SQL Server Best Practices – Exclusions Antivirus

Lors de l’installation/configuration d’un SQL Server sur une machine, il faut toujours penser à vérifier si un antivirus est présent.
En effet, si un scan antivirus est lancé sur des fichiers de base de données (MDF, LDF), ou sur les services SQL,  les performances du serveur seront catastrophiques.
Pour éviter ce genre de probléme, il faut gérer une liste d’exclusion afin de ne pas impacter les performances du SQL Server.

Voici une liste exhaustive des exclusions par catégorie :

Fichiers et dossiers :

  • Les fichiers de base de données (.mdf, .ldf, .ndf)
  • Les fichiers de sauvegarde (.bak, .trn)
  • Les fichiers de catalogue texte intégral (Full-Text)
  • Les fichiers de trace SQL (.trc)
  • Les fichiers d’audit SQL (.sqlaudit)
  • Les fichiers de requête (.sql)
  • Les répertoires Analysis Services (Data, Log, Temp, Backups)
  • Les fichiers de sauvegarde Analysis Services
  • Les fichiers FileStream
  • Le répertoire SSRS contenant les fichiers temporaires et  les logs

Processus :

  • SQLServr .exe
  • ReportingServicesService .exe
  • MSMDSrv .exe

Dans le cas ou vous utilisez la fonctionnalité cluster,  il faut également penser à exclure :

  • C:\Windows\Cluster
  • Le disque de Quorum

Pour plus de détails je vous invite à vous rendre sur le post officiel de Microsoft : ici.

SQL Server Best Practices – Options d’alimentation du serveur

Cette best practice est seulement valable pour les serveurs qui ne sont pas issues de la virtualisation (Hyper-V ou Vmware), car ce paramètre est géré directement au niveau de la console de virtualisation.

Définir le mode de gestion de l’alimentation du serveur en « High Performance » est souvent oublié lors de la configuration des serveurs.
Pourtant, ne pas laisser le mode par défaut est primordial pour faire fonctionner le serveur à 100% de ses capacités.

En effet, laisser le mode Balanced peut réduire drastiquement les performances.

Comme on peut le voir sur cette image, le mode Balanced permet d’allouer au processeur un pourcentage minimum et maximum de fréquence.
C’est donc le système qui va définir quelle fréquence il devra utiliser, en fonction de la charge du serveur.
Le problème qui va se poser est que le système a du mal à définir la bonne fréquence à donner aux processeurs en fonction de la charge du SQL Server.
Par exemple, pour une requête spécifique, SQL pourrait avoir besoin de 100% de CPU, alors que le système n’en donnera que 5% car il n’aura pas détecté cette requête comme étant consommatrice.

La bonne pratique est de mettre le mode en High Performance.

Ce mode permet de laisser la fréquence à 100% en minimum et maximum.
Le SQL Server pourra donc utiliser toutes les capacités du serveur.

Lors de mes interventions, beaucoup de problèmes de performances ont pu être réglés seulement en changeant ce paramètre. Attention donc à ne pas négliger cette best practice !

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.

SQL Server Best Practices – Fixer la mémoire de l’instance

Une des premières étapes de la configuration d’une instance SQL Server est d’appliquer des « Best Practices ». Définir une limite maximale pour la mémoire allouée à l’instance est très important. Cela permet de ne pas se retrouver avec un serveur saturé.

Mais comment définir la meilleure valeur à mettre en place ?

Voici deux règles simples :

  1. (Mémoire du serveur) – ((Mémoire du serveur) * 0,10))
  2. (Mémoire du serveur) – Une valeur fixe en fonction de la mémoire du serveur

Ces règles sont applicables dans le cas ou il n’y a qu’une seule instance SQL sur le serveur.
Pour ma part je préfère celle avec les valeurs fixes, qui sont définies suivant la mémoire totale.

Voici ce que j’utilise :

  • 1 GO pour 4 GO
  • 2 GO pour 8 GO
  • 4 GO pour 16 GO et plus

A ajuster si d’autres services sont installés (SSIS/SSAS/SSRS etc…) ou si la mémoire de votre serveur ne rentre pas dans ces critères.

Pour un serveur de 8 GO nous aurons donc : 8(Mémoire du serveur) – 2(Valeur fixe) = 6 GO allouée à l’instance.

En plus de fixer la mémoire maximum dédiée à l’instance, on peut également mettre une valeur minimum. Si vous mettez une valeur minimum et maximum au même niveau, par exemple 6 GO, l’instance restera à cette valeur fixe tant qu’elle sera active.
C’est intéressant pour avoir une valeur de référence et détecter un problème de mémoire sur le serveur.

Pour fixer la mémoire vous pouvez passer en interface graphique avec SSMS ou directement en requête T-SQL.

Avec SSMS :

Aller dans les propriétés de l’instance, puis Memory et Server memory options.
Puis changer la valeur minimum et maximum de la mémoire dédiée à l’instance.

En T-SQL :

EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'min server memory (MB)', N'6144'
GO
EXEC sys.sp_configure N'max server memory (MB)', N'6144'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0'  RECONFIGURE WITH OVERRIDE
GO

Remplacer 6144 par la valeur que vous aurez définie.

Cet article est le premier d’une longue série sur les Best Practices  de SQL Server.
D’autres seront postés dans les semaines à venir.

SSMS 17 disponible !

Cette news date de quelques mois, mais Management Studio en version 17 est disponible au téléchargement.
Il permet d’administrer toutes les versions de SQL Server (même les plus anciennes), ainsi que SQL Server sous Linux.

Pour le télécharger c’est par ici : SSMS17

Et pour la liste de toutes les nouveautés : Changelog