SQL Server 2016 : Diagnostic

Comprendre l'attente CXPACKET

TESTEZ LINKEDIN LEARNING GRATUITEMENT ET SANS ENGAGEMENT

Tester maintenant Afficher tous les abonnements
L'attente de type CXPACKET vous permet de diagnostiquer d'éventuels problèmes de parallélisme. Voyez comment tirer parti de ces informations.
07:34

Transcription

Deuxième type d'attente, très classique, une attente qui s'appelle « CXPACKET ». Peut-être en avez-vous déjà entendu parler, on en parle beaucoup, et, là tout va bien, zéro pour moi, mais je peux vous en générer assez facilement. Donc, CXPACKET, on voit qu'on a « CX », ça veut dire « échange », et « PACKET », des paquets. Mais échange de paquets pourquoi ? Prenons un exemple de requête ici que j'ai faite assez rapidement, où je fais un « UNION » entre deux requêtes. Je vais regarder le plan d'exécution. Là, j'ai fait « Ctrl+L » pour avoir le plan d'exécution estimé ici et je m'aperçois que ce plan d'exécution a quelques éléments intéressants, notamment, c'est marqué ici, « parallélisme, parallélisme, parallélisme ». Vous voyez qu'à ces endroits, on me dit qu'on fait du parallélisme en répartition de flux, on fait de la répartition de flux, et ensuite on fait un regroupement de flux. On répartit des flux pour faire au milieu des correspondances de hachage, donc des « hash » ici. Et pourquoi on répartit des flux, pourquoi on regroupe des flux, et pourquoi on fait tout ça d'ailleurs ? Parce que cette requête a été évaluée par l'optimiseur comme étant relativement coûteuse, et l'optimiseur s'est dit « Et si j'exécutais cette requête sur plusieurs processeurs en même temps, je vais pouvoir utiliser plusieurs processeurs de façon à diviser le travail et à exécuter cette requête plus vite ». Il s'est trouvé qu'on va prendre ici des informations dans les tables, ensuite, on a des correspondances de hachage, et pour ces hash, créer des hash en mémoire, je pourrais utiliser plusieurs processeurs, mais pour ça, il faut que je répartisse déjà des données de façon à les distribuer sur les différents processeurs en quelque sorte pour faire mon hachage de façon découpée. Et ensuite, je fais mes hachages, et ensuite je vais regrouper mes flux ici pour resynchroniser mes données de façon à obtenir le résultat. Bref, c'est ce qu'on appelle un « plan parallélisé ». Et on voit ici, ici, ici, ce petit ajout d'icône sur chaque opérateur qui va pouvoir être exécuté en parallèle par SQL Server. Mais à ces endroits, répartition et regroupement, peut-être que je vais avoir des attentes, parce que si j'ai plusieurs processeurs qui travaillent, peut-être qu'il y en a certains qui vont avoir terminé avant d'autres, et par exemple, le regroupement de flux, j'aurai peut-être un processeur qui a terminé, il va devoir attendre que les autres qui se terminent pour pouvoir se terminer lui-même. Peut-être que si je parallélise beaucoup, je vais avoir un phénomène d'attente trop important ici et ici par rapport à ce que je voudrais et le fait d'avoir parallélisé la requête n'est pas toujours une bonne idée. Peut-être que le parallélisme pose plus de problèmes, s'il y a trop d'attente, qu'il ne résout de problèmes. Donc c'est vraiment un équilibre à trouver sur les requêtes qui ont été parallélisées. Je vais lancer cette requête pour voir si ça me génère des attentes, vous vous souvenez, ici je suis pour l'instant à zéro. Donc, j'exécute ma requête, vous voyez que ça a pris trois secondes approximativement. Et maintenant, je réexécute, et effectivement j'ai eu du CXPACKET, vous voyez, j'ai eu 10 millisecondes de temps d'attente. J'ai eu 3590 attentes CXPACKET. Vous voyez, qu'il y a eu de l'attente quand même, et pour cette requête, il y a eu 10 secondes d'attente, ça paraît beaucoup quand même, mais le max c'était 273 millisecondes, donc on pourrait dire 10 secondes, selon le nombre de processeurs. J'en ai quatre qui ont dû être pris en charge ici, donc il y a eu peut-être 2,5 secondes, 3 secondes d'attente, ce qui correspond d'ailleurs à peu près au temps d'exécution. Vous voyez que le parallélisme a quand même généré pas mal d'attente. Ça arrive régulièrement. C'est à vous d'estimer par rapport au temps total, une fois de plus ici, et par rapport au nombre, quelle est la moyenne, et de voir si la moyenne d'attente due au parallélisme est vraiment importante. C'est normal, c'est toujours normal d'avoir des attentes de parallélisme, simplement il faut juger de l'intérêt de modifier les paramètres du parallélisme. À ce sujet, quelques règles classiques de gestion du parallélisme dans SQL Server. Je vais revenir ici au niveau de mon serveur dans les propriétés du serveur, et puis, je vais aller directement dans les propriétés avancées, où je trouve ici des informations sur le parallélisme. Il y a deux options qu'on peut changer si on veut améliorer un peu les choses. D'abord, ici le degré maximum de parallélisme. Il est ici. Il est par défaut à zéro. Le degré maximum, c'est combien de processeurs, de cœurs si vous préférez, vont être utilisés dans une requête parallélisée. Ici, zéro signifie potentiellement tous les processeurs, et dynamiquement, le moteur d'exécution va juger, lors du parallélisme, du nombre de processeurs qu'il vaut mieux utiliser. Ici, j'ai quatre cœurs, donc je peux utiliser un maximum de quatre cœurs pour paralléliser une seule requête. Mais imaginez, vous avez un serveur de production où vous avez 32 cœurs. Le fait de laisser à zéro peut faire en sorte qu'à certains moments, vous avez une requête qui s'exécute sur 32 cœurs, et le parallélisme à ce moment-là va poser beaucoup plus de problèmes que d'amélioration de temps de la requête, parce que si on a séparé en 32 threads une seule requête, elle va passer beaucoup de temps à attendre. Ici, Microsoft recommande d'ailleurs de ne pas dépasser huit. Et puis vous allez juger par exemple, si vous avez un serveur où il y a beaucoup de petites requêtes qui passent, et donc vous voulez laisser des processeurs libres pour exécuter ces petites requêtes. Alors peut-être que si vous avez par exemple 32 processeurs, vous mettriez ici 4, pour dire, voilà si on parallélise pour des requêtes plus lourdes, on ne va pas utiliser plus de quatre processeurs. Ça me paraît raisonnable. Voilà, un exemple. Ensuite, le seuil de coût pour le parallélisme. Cette valeur indique une valeur pour estimer qu'une requête, un plan d'exécution qui va être estimé coûtant 5, peut être parallélisé. Ça veut dire ceci : lorsque le moteur d'optimisation crée un plan d'exécution, il est pesé et on lui donne un coût. Or, il se trouve que 5 de nos jours, ce n'est pas très coûteux comme plan d'exécution. Simplement, ce cinq correspond à des secondes qui étaient des secondes de référence sur une machine de Microsoft, il y a de cela assez longtemps. Donc, maintenant, un plan qui coûte 5, ça ne veut pas dire cinq secondes réelles, ça veut dire assez rapide finalement. Donc, le seuil de coût indique que les requêtes qui sont en dessous de 5, ne vont pas être parallélisées. De nos jours, vous pouvez sans problème monter à 12 ou 15, si vous voulez, de façon à empêcher un parallélisme pour des requêtes qui restent relativement rapides. Vous allez dire, « Non, je vais favoriser le parallélisme pour des requêtes beaucoup plus coûteuses, et ça sera beaucoup plus intéressant de les paralléliser ». Donc, par exemple ici, j'ai des options qui me correspondent beaucoup mieux, si je suis sur une machine qui a 32 processeurs par exemple et qui est une machine opérationnelle qui doit exécuter beaucoup de requêtes rapidement, de dire, j'augmente le seuil de coût, je laisse un degré maximum de parallélisme à 4 de façon à utiliser le parallélisme quand on en a vraiment besoin et sur un nombre de processeurs raisonnable.

SQL Server 2016 : Diagnostic

Prenez en main les outils de diagnostic intégrés à SQL Server. Soyez en mesure de comprendre et d'analyser les problèmes de performance rencontrés le plus fréquemment.

1h58 (20 vidéos)
Aucun commentaire n´est disponible actuellement
 
Logiciel :
Spécial abonnés
Date de parution :14 déc. 2016

Votre formation est disponible en ligne avec option de téléchargement. Bonne nouvelle : vous ne devez pas choisir entre les deux. Dès que vous achetez une formation, vous disposez des deux options de consultation !

Le téléchargement vous permet de consulter la formation hors ligne et offre une interface plus conviviale. Si vous travaillez sur différents ordinateurs ou que vous ne voulez pas regarder la formation en une seule fois, connectez-vous sur cette page pour consulter en ligne les vidéos de la formation. Nous vous souhaitons un excellent apprentissage avec cette formation vidéo.

N'hésitez pas à nous contacter si vous avez des questions !