Gérer dynamiquement la mémoire au démarrage de SQL Server

Dans ce post, nous allons voir comment gérer dynamiquement la mémoire allouée à l’instance SQL en fonction de la taille mémoire du serveur.

Mais pourquoi vouloir gérer dynamiquement cette valeur au démarrage de SQL ?

Pour des serveurs physiques cela a peu d’intérêt, car la mémoire du serveur a très peu tendance à bouger.
Sur des machines virtuelles, cela peut être intéressant. Comme par exemple sur Azure ou l’on peut changer la configuration d’un serveur à tout moment pour optimiser les coûts.

Avant de commencer, un petit rappel : SQL Server Best Practices – Fixer la mémoire de l’instance.

Notre serveur de test aura une capacité de 8 GO de ram.

Pour pouvoir calculer la  taille optimale de ram nécessaire , il faut récupérer la mémoire totale du serveur. Pour cela nous utiliserons la table sys.dm_os_sys_memory.
Voici ce que nous retourne cette table :

Comme nous le voyons dans ce résultat, il faudra se baser sur la colonne total_physical_memory_kb pour définir la valeur à allouer à SQL Server.

Il faut maintenant définir une règle pour calculer la bonne valeur à mettre en place pour le min et max mémoire. Suivant l’article cité plus haut, nous pouvons calculer suivant un pourcentage ou une valeur fixe.

Pourcentage
select
 (total_physical_memory_kb/1024) - ((total_physical_memory_kb/1024)*0.1)
as max_memory_mb from sys.dm_os_sys_memory
Valeur Fixe
select
CASE
WHEN (total_physical_memory_kb/1024) <= 4096 THEN (total_physical_memory_kb/1024) - 1024
WHEN (total_physical_memory_kb/1024) <= 8192 THEN (total_physical_memory_kb/1024) - 2048
ELSE (total_physical_memory_kb/1024) - 4096
END
as max_memory_mb from sys.dm_os_sys_memory

Et la procédure complète pour modifier les paramètres du min et max dynamiquement :

Pourcentage
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
Declare @MaxMemory INT
set @MaxMemory = (select
 (total_physical_memory_kb/1024) - ((total_physical_memory_kb/1024)*0.1)
as max_memory_mb from sys.dm_os_sys_memory)
EXEC sys.sp_configure N'min server memory (MB)', @MaxMemory
EXEC sys.sp_configure N'max server memory (MB)', @MaxMemory
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
GO
Valeur Fixe
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
Declare @MaxMemory INT
set @MaxMemory = (select
CASE
WHEN (total_physical_memory_kb/1024) <= 4096 THEN (total_physical_memory_kb/1024) - 1024
WHEN (total_physical_memory_kb/1024) <= 8192 THEN (total_physical_memory_kb/1024) - 2048
ELSE (total_physical_memory_kb/1024) - 4096
END
as max_memory_mb from sys.dm_os_sys_memory)
EXEC sys.sp_configure N'min server memory (MB)', @MaxMemory
EXEC sys.sp_configure N'max server memory (MB)', @MaxMemory
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
GO

Ce qui nous intéresse maintenant c’est de pouvoir lancer ce code au démarrage de SQL Server. Pour cela rien de plus simple ! Il suffit de mettre ce code dans un job qui sera lancé à chaque démarrage du SQL Agent. Si votre SQL Server est bien configuré, le SQL Agent est en démarrage automatique et sera lancé au démarrage de SQL Server lors d’un reboot.

Création du JOB par SSMS : 

Ouvrir SSMS → SQL Server Agent → Jobs → New Job.

Donner un nom au job et une description.

Aller dans Steps → New Step.
Donner un nom , mettre le type en « T-SQL » et copier coller le code. (Pourcentage ou Valeur Fixe)

Aller dans Schedules → New Schedule.
Donner un nom, changer le Schedule type par « Start Automatically when SQL Server Agent Starts » et cocher la case « Enabled ».

Ensuite cliquer sur OK pour terminer.

Création du JOB en T-SQL: 

USE [msdb]
GO

/****** Object:  Job [Admin - Set up Max Memory]  *******/

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Admin - Set up Max Memory', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'Set up the max memory following best practices and server memory.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Configure Max Memory]    Script Date: 29/05/2017 12:37:10 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Configure Max Memory', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'EXEC sys.sp_configure N''show advanced options'', N''1''  RECONFIGURE WITH OVERRIDE
GO
Declare @MaxMemory INT 
set @MaxMemory = (select
					CASE   
						WHEN (total_physical_memory_kb/1024) <= 4096 THEN (total_physical_memory_kb/1024) - 1024
						WHEN (total_physical_memory_kb/1024) <= 8192 THEN (total_physical_memory_kb/1024) - 2048
						ELSE (total_physical_memory_kb/1024) - 4096   
					END  
					as max_memory from sys.dm_os_sys_memory)
EXEC sys.sp_configure N''min server memory (MB)'', @MaxMemory
EXEC sys.sp_configure N''max server memory (MB)'', @MaxMemory
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N''show advanced options'', N''0''  RECONFIGURE WITH OVERRIDE
GO

', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'At startup of SQL Agent', 
		@enabled=1, 
		@freq_type=64, 
		@freq_interval=0, 
		@freq_subday_type=0, 
		@freq_subday_interval=0, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20170509, 
		@active_end_date=99991231, 
		@active_start_time=0, 
		@active_end_time=235959, 
		@schedule_uid=N'6b873d31-3cfd-4691-94c2-808f777178a5'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Pour vérifier si votre job fonctionne correctement, il suffit de redémarrer votre service SQL Server, qui redémarrera par la même occasion SQL Server Agent.

Une fois le service redémarré, vérifiez l’historique du job. Vous devriez avoir ce genre de log :

Nous pouvons voir que le job a bien été exécuté au redémarrage, et que les valeurs du Min et Max Memory ont bien étés changées.

Comme nous avons mis en place le job avec le code qui se base sur des valeurs fixes, le maximum de mémoire allouée est de 6 GO (sur les 8 GO du serveur).

Personnellement j’utilise ce job sur toutes les VM Azure avec SQL Server qui sont susceptibles de changer de taille.
Cela évite de se retrouver avec la mémoire saturée si le serveur est downgradé, ou une mémoire pas assez utilisée lors d’un upgrade.