28
Chapitre 3. Optimisation du matériel
de processus SQL Server grâce à la vue de gestion dynamique sys.dm_os_wait_stats.
La colonne wait_type indique pour quelle raison l’attente a eu lieu. Lors d’une exécution en parallèle, les threads ont besoin de s’échanger des données. Le type CXPACKET indique une attente de synchronisation d’échange de paquets. Ainsi, si vous
constatez des ralentissements liés à des attentes de type CXPACKET, il ne vous reste
plus qu’à jouer avec « max degree of parallelism ».
Si vous constatez, sur des processeurs hyper-threadés, une augmentation de l’utilisation du CPU mais une baisse de performances, liées à des messages visibles
dans le journal d’erreur de SQL Server (errorlog) indiquant qu’un thread n’a pas
réussi à acquérir un spinlock (une structure légère de synchronisation de threads),
vous vous retrouvez certainement dans la situation décrite par Slava Oks ici :
http://blogs.msdn.com/slavao/archive/2005/11/12/492119.aspx. La solution est
de désactiver purement et simplement l’hyper-threading dans le BIOS de votre
machine.
Comme remarque générale, considérant les problèmes potentiels que génère
l’hyper-threading, faites des tests de charge hyper-threading désactivé puis activé, et si
vous ne voyez pas de gain de performance notable avec l’hyper-threading, désactivezle pour de bon.
NUMA
Comme nous allons le voir, sur une architecture NUMA, des bus séparés sont alloués aux
processeurs pour accéder à la mémoire. La mémoire accédée sur le bus du processeur est
nommée mémoire locale, et la mémoire accessible sur les autres bus est logiquement
nommée mémoire distante. Vous vous doutez que l’accès à la mémoire distante est plus long
et coûteux que l’accès à la mémoire locale. Il faut donc éviter de paralléliser une requête sur
des processeurs qui accèdent à des bus différents. Vous le faites en limitant le « max degree
of parallelism » au nombre de processeurs sur un nœud.
Les bonnes pratiques sont donc les suivantes :
• sur des systèmes très sollicités en requêtes courtes, désactivez totalement la
parallélisation (EXEC sp_configure 'max degree of parallelism', 1) : cela
évite que quelques requêtes de reporting mobilisent plusieurs processeurs qui
auraient été utiles pour d’autres lots en attente ;
• sur des systèmes comportant plus de huit processeurs, limitez « max degree of
parallelism » à 8. Une parallélisation sur plus de 8 processeurs devient excessivement coûteuse en synchronisation ;
• sur une architecture NUMA, « max degree of parallelism » ne doit pas être
supérieur au nombre de processeurs sur un nœud NUMA.
Il existe une option de requête, MAXDOP, qui produit le même effet de façon localisée. Par exemple :
Chapitre 3. Optimisation du matériel
de processus SQL Server grâce à la vue de gestion dynamique sys.dm_os_wait_stats.
La colonne wait_type indique pour quelle raison l’attente a eu lieu. Lors d’une exécution en parallèle, les threads ont besoin de s’échanger des données. Le type CXPACKET indique une attente de synchronisation d’échange de paquets. Ainsi, si vous
constatez des ralentissements liés à des attentes de type CXPACKET, il ne vous reste
plus qu’à jouer avec « max degree of parallelism ».
Si vous constatez, sur des processeurs hyper-threadés, une augmentation de l’utilisation du CPU mais une baisse de performances, liées à des messages visibles
dans le journal d’erreur de SQL Server (errorlog) indiquant qu’un thread n’a pas
réussi à acquérir un spinlock (une structure légère de synchronisation de threads),
vous vous retrouvez certainement dans la situation décrite par Slava Oks ici :
http://blogs.msdn.com/slavao/archive/2005/11/12/492119.aspx. La solution est
de désactiver purement et simplement l’hyper-threading dans le BIOS de votre
machine.
Comme remarque générale, considérant les problèmes potentiels que génère
l’hyper-threading, faites des tests de charge hyper-threading désactivé puis activé, et si
vous ne voyez pas de gain de performance notable avec l’hyper-threading, désactivezle pour de bon.
NUMA
Comme nous allons le voir, sur une architecture NUMA, des bus séparés sont alloués aux
processeurs pour accéder à la mémoire. La mémoire accédée sur le bus du processeur est
nommée mémoire locale, et la mémoire accessible sur les autres bus est logiquement
nommée mémoire distante. Vous vous doutez que l’accès à la mémoire distante est plus long
et coûteux que l’accès à la mémoire locale. Il faut donc éviter de paralléliser une requête sur
des processeurs qui accèdent à des bus différents. Vous le faites en limitant le « max degree
of parallelism » au nombre de processeurs sur un nœud.
Les bonnes pratiques sont donc les suivantes :
• sur des systèmes très sollicités en requêtes courtes, désactivez totalement la
parallélisation (EXEC sp_configure 'max degree of parallelism', 1) : cela
évite que quelques requêtes de reporting mobilisent plusieurs processeurs qui
auraient été utiles pour d’autres lots en attente ;
• sur des systèmes comportant plus de huit processeurs, limitez « max degree of
parallelism » à 8. Une parallélisation sur plus de 8 processeurs devient excessivement coûteuse en synchronisation ;
• sur une architecture NUMA, « max degree of parallelism » ne doit pas être
supérieur au nombre de processeurs sur un nœud NUMA.
Il existe une option de requête, MAXDOP, qui produit le même effet de façon localisée. Par exemple :