Blog

Tuning d’instance SQL Server

---

Sur une instance SQL Server 2016, il y a près de 80 options. Soyons pragmatique, il y a au final une dizaine d’options à prioriser. Pour afficher la configuration avancée de l’instance :

Les options qui nous intéressent sont les suivantes :

backup compression default

Permet d’activer la compression des sauvegardes, ce qui réduit le temps de backup par la même occasion. Cette option est uniquement activable sur une édition Enterprise pour une version SQL Server < 2016 SP1. Après ça change du tout au tout et on est bien content !

blocked process threshold (s)

Seuil de déclenchement pour le relevé de blocage dans une trace XEvent/Profiler, l’impact de cette option est que la trace d’enregistrement des blocages peut être plus ou moins volumineuse. Dans l’exemple ci-dessous, le seuil est positionné à 10 sec, autrement dit les blocages seront consignés dans une trace XEvent/Profiler si la durée est d’au minium 10 sec.

Pour la mise en place de trace XEvent de processus bloqués et bloquant, voir : https://www.concatskills.com/2017/01/27/xevent-processus-bloques-bloquant/

cost threshold for parallelism

Coût CPU/IO minimum d’une requête pour déclencher le parallélisme, autrement dit, pour répartir l’exécution d’une tâche sur plusieurs threads par CPU. Ce paramètre a toujours été positionné à 5 par défaut mais sa valeur n’a jamais été réévaluée avec les versions de SQL Server, à tort. A l’heure actuelle, une valeur entre 30 et 50 est plus réaliste. Attention la modification de ce paramètre entraîne une recompilation des plans d’exécution.

max degree of parallelism

Nombre de CPU maximum pour le parallélisme. Un positionnement à 1 signifie que le parallélisme est désactivé ce qui peut être un pré requis pour une instance qui héberge une base de données Sharepoint. Toujours est-il que cette valeur ne doit jamais être positionnée à 0 pour éviter d’accroître le temps de synchronisation des threads ou encore de générer des deadlocks intra parallelism. Attention la modification de ce paramètre entraîne une recompilation des plans d’exécution.

Un deadlock intra parallelism, ça ressemble à ça (et non ce n’est pas un vaisseau Star Wars) :

Pour la mise en place de trace XEvent de deadlocks, voir : https://www.concatskills.com/2017/02/10/xevent-deadlock/

max server memory (MB)

Allouer la mémoire selon la formule suivante : RAM totale – 2 Go (pour l’OS). Si un serveur dispose de a 16Go de RAM, on allouera 14Go à SQL Server. Le redémarrage de l’instance sera nécessaire si la mémoire est revue à la baisse et que la quantité utilisée par l’instance SQL Server est déjà supérieure à la nouvelle allocation.

min server memory (MB)

Cette option n’a d’intérêt que si le serveur héberge plusieurs instances SQL Server pour allouer un minium de mémoire à chacune d’elle.

optimize for ad hoc workloads

Cette option active l’optimisation du cache de plan pour les requêtes Ad Hoc. Cela permet de créer des sous-plans pour une même requête utilisant des valeurs littérales différentes et de réduire la taille des plans.

Pour mesurer l’impact de cette option, voir : https://www.concatskills.com/2016/10/28/optimize-for-adhoc-workloads/

remote admin connections

Activer le mode de connexion de secours à distance est indispensable pour une instance qui n’est plus accessible en locale en raison d’un serveur gelé par exemple.

Pour l’utilisation du mode de secours DAC, voir : https://www.concatskills.com/2017/01/12/mode-de-secours-dac/

xp_cmdshell

Cette option permet l’exécution de commande DOS depuis l’instance SQL Server mais elle créé une faille de sécurité en autorisant l’accès aux ressources d’un serveur, ex : disque. Il est plus sain d’avoir recours à un job utilisant un proxy pour une tâche SSIS ou Powershell.

Pour avoir plus de détail sur les options avancées : https://msdn.microsoft.com/fr-fr/library/ms189631.aspx