Pour mon 1er billet, je vais vous faire part d’une discussion intéressante que j’ai eu avec un de mes responsables de travail concernant la parallélisation des requêtes sur SQL Server. En effet les premiers temps où je suis arrivé dans l’entreprise, on m’a présenté l’infrastructure informatique et un des serveurs sur lequel j’allais exercer mes fonctions de DBA.
Ce serveur a des caractéristiques plutôt intéressantes. (2 Processeurs Intel Xeon 2 quadricoeur 2,33GHz avec 8 Go de RAM et un peu plus de 1 Téra Octets d’espace disque pour héberger les données avec une version SQL Server 2005 Entreprise Edition). Ce serveur héberge 16 bases de données qui fonctionnent toutes en environnement OLTP et où l’activité transactionnelle est plutôt soutenue.
Cependant mon responsable a été surpris quand je lui ai annoncé qu’il était bien souvent inutile de laisser la parallélisation activée dans ce genre d’environnement et qu’à l’inverse on risquait de perdre en performance. Il a fallu bien évidemment le lui prouver.
Alors commençons par poser le décor : La parallélisation c’est quoi au juste ? Et comment peut t’on savoir si celle-ci détériore les performances plutôt que le contraire ?
Les serveurs actuels sont généralement multiprocesseurs et multicoeurs. Cette architecture permet à SQL Server de pouvoir paralléliser l’exécution d’une requête lorsqu’il le désire en fonction de son coût et de l’occupation du système. Les requêtes qui sont le plus souvent candidates à cette opération sont les requêtes longues et celles qui sont composées de plusieurs autres petites requêtes. On les retrouve volontiers dans des environnements plutôt de type OLAP.
A contrario, dans un environnement de type OLTP la plupart des requêtes sont courtes et nombreuses. Par conséquent le coût engendré pour une éventuelle parallélisation peut devenir important car cette opération nécessite une synchronisation de tous les processeurs candidats. On peut donc assister à des ralentissements et une détérioration des performances dans certains cas.
SQL Server se réfère à certains paramètres de configuration du serveur pour décider d’une parallélisation de requête. Ces paramètres sont disponibles via la procédure sp_configure et sont des options avancées. On en dénombre 3 :
- affinity mask : ce paramètre permet d’attribuer à SQL Server certains ou tous les processeurs disponibles sur le serveur.
- max degree of parallelism : ce paramètre contrôle le nombre de processeurs candidats à la parallélisation en fonction de ceux disponibles via l’option affinity mask
- cost threshold for parallelism : ce paramètre détermine le coût minimum de référence pour pouvoir exécuter une parallélisation de requête
Â
Comment peut on mesurer ces ralentissements et cette perte de performance ?
Les ralentissements se traduisent par des types d’attentes particuliers. On peut les visualiser via la DMV sys.dm_os_wait_stats qui recense les informations concernant les attentes subies par les threads en cours d’exécution sur le système. Nous nous intéresserons plus particulièrement au type d’attente CXPACKET.
Le coût de synchronisation des processeurs se traduit également par des temps d’utilisation et des commutations de threads entre processeurs plus important. On peut mesurer ces temps et ces commutations via les compteurs de performance Processeur : %Temps processeur et Système : Changement de contexte /s.Â
Nous avons donc tout ce qu’il faut pour réaliser nos tests. Ceux-ci ont été réalisé sur la base suivante :
- Pour que les tests soient représentatifs nous avons pris une journée de travail où le serveur était très sollicité et une où celui-ci fonctionnait en régime « stationnaire ».
- La 1ère partie des tests a été réalisé avec désactivant la parallélisation (option « max degree of parallelism » à 1, qui permet de n’utiliser qu’un seul processeur dans tous les cas) et la 2ème partie en réactivant la parallélisation (option « max degree of parallelism » à 0, qui permet d’utiliser tous les processeurs candidats)
Voici les résultats observés : (ceux-ci sont à peu près similaires pour les 2 tests. Pour cette raison je n’exposerais qu’un seul jeu d’essai)
- Utilisation des processeurs :
Â
 En vert : % temps processeur
 En bleu : Les changements de contexte / s
Â
Â
Â
On observe une augmentation rapide de l’utilisation des processeurs et des changements de contexte lorsqu’on réactive la parallélisation (2ème partie). Dans le cas de nos tests on observe un gain d’environ 30% ce qui n’est pas négligeable !!
Â
- Les attentes :
Â
                                       CXPACKET – Temps d’attente (ms) | Temps d’attente globale (ms) | Ratio
Parallélisation désactivée                                 2                      |                  12908789           | 0,00%
Parallélisation activée                                     4524593             |                  15510535           | 29,17%
On observe que les temps d’attentes CXPACKET représente 30% du temps global d’attente. On voit ici l’intérêt de désactiver la parallélisation.
Bien entendu ces tests ont convaincu mon responsable 
Mais pour être plus sérieux, dans la plupart des cas il est conseillé de désactiver la parallélisation dans les environnements de type OLTP pour toutes les raisons évoquées dans l’article. Il existe également des best practices Microsoft qui traitent sur ce sujet. Je finirais par le fait que beaucoup d’informaticiens (non DBA) ou de services informatiques misent sur la puissance de leur architecture matérielle en sur dimensionnant leurs serveurs pour des questions de tranquillité ou de budget (Eh oui un DBA en plus c’est une ressource en plus !!) sans imaginer que cela puisse être un handicap dans certains cas. J’espère que ce 1er billet vous aura convaincu !!!
++
David BARBARIN (Mikedavem)
Elève ingénieur CNAM Lyon
Bonjour David,
Je viens de suivre un cours de Paul Randal, un des gars qui a participé au développement de SQL Server.
https://app.pluralsight.com/course-player?clipId=ff1d22d0-4808-41d2-960c-d105a960364d
Dans cette vidéo, il démontre l’événement d’attente.
Dans ton explication, tu as démontré que quand on utilise du parallélisme, il y a plus d’événements d’attente CXPACKET que quand la requête est exécuté par un thread. Ça va de soi et cela n’a pas besoin d’être démontré.
Ce qu’il fallait démontrer, c’est est-ce que le parallélisme est utile ou non? Oui, il est utile et on n’a pas besoin de le désactiver.
Il est utile car les temps de réponse sont souvent réduits lorsque les requêtes sont exécutées en parallèle, surtout s’il faut parcourir une grande table partitionnée par exemple. Si c’est de l’OLTP, avec un accès à quelques pages, l’optimiseur est assez intelligent pour ne pas activer le parallélisme.
Pour résoudre le problème des attentes excessives du thread de synchronisation CXPACKET, il n’est pas conseillé d’arrêter le parallélisme mais d’indiquer à l’optimiseur à partir de quel coût il faut penser à évaluer des plans avec parallélisme.
Hello m_amziani,
C’est un vieux poste déterré celui là :). Je n’ai même pas vu le commentaire.
Tout d’abord, je précise bien que de désactiver le parallélisme ne constitue en aucun cas une bonne pratique générale et je préfère laisser faire SQL Server quand c’est possible (heureusement 99% du temps). Ensuite, tout est question de contexte et changer le cost threshold for parallelism revient pratiquement à inhiber le parallèlise sur le un environnement purement OLTP avec transactions courtes si on y réfléchit. Augmenter le threshold for parallelism reste à coup sûr une bonne chose à faire car la valeur par défaut n’est plus adaptée aux workloads d’aujourd’hui.
Ce qu’il fallait démontrer, c’est est-ce que le parallélisme est utile ou non? Oui, il est utile et on n’a pas besoin de le désactiver … Si c’est de l’OLTP, avec un accès à quelques pages, l’optimiseur est assez intelligent pour ne pas activer le parallélisme.
J’ai encore des cas où avoir désactivé complètement le parallélisme.. (au niveau de la DB, possible maintenant avec les DB-Scoped configuration) a été plutôt salvateur même après une augmentation du cost threshold for parallelism. La parallélisation a un coût et notamment au niveau de l’estimation d’acquisition mémoire (GRANT MEMORY). Pendant la période COVID-19 ou notre workload a considérablement augmenté, on s’est dit que ca serait peut être une bonne idée de laisser faire SQL Server en réactivant le parallélisme et on s’est vite retrouvé à avoir un empilement d’exécution de requête en attente avec des types d’attentes SEMAPHORE en pagaille sur notre instance (pas cool quand tu es > 10000 batches/sec). Après réflexion, notre environnement a finalement très peu de requêtes potentiellement parallèlisables sur la partie purement OLTP .. en général une augmentation du coût (et donc du parallélisme) démontre plutôt soit l’absence d’un index pertinent soit une requête qu’on peut réécrire et rendre plus efficace … Pour les requêtes à fort coût .. principalement du Reporting, on a décidé d’utiliser nos réplicas secondaires ou nous avons activé le parallélisme. Dans un autre scénario nous aurions bien entendu activé le parallélisme et trouver une bonne valeur du cost threshold for parallelism et surtout revu le dimensionnement de la mémoire… pas le choix
Je suis d’accord avec toi sur pas mal des points que tu cites :
1) 2) 3) 5) et 6) . Je serais tenté de dire que pour un environnement OLTP je préferais paramétrer l’option de serveur max degree of parallelism à 1 et de jouer avec l’option MAXDOP .. en principe le ratio de requêtes ayant besoin d’une parallélisation reste faible par rapport au volume global de requêtes dans ce type d’environnement en général.
Cependant pour le point 4) quand tu dis que le XML requêté par les fonctions Xpath / Xquery empêche une parallélisation est ce que tu peux préciser pourquoi .. En fait j’ai un exemple ici qui génére un plan de requête avec parallélisation avec du XML et Xquey (testé sous SQL Server 2008 avec un portable dual core) :
GO <br />
<br />
CREATE TABLE T_TEST ( <br />
ID INT IDENTITY PRIMARY KEY, <br />
ColXML XML ) <br />
GO <br />
<br />
INSERT INTO T_TEST ( ColXML ) <br />
SELECT '<Ex> <br />
<E Value="' + LTRIM( STR( RAND() * 1000 ) ) + '"/> <br />
<E Value="' + LTRIM( STR( RAND() * 1000 ) ) + '"/> <br />
</Ex>' <br />
GO 100000 <br />
<br />
SELECT <br />
ID, <br />
ColXML.value('(//E/@Value)[1]', 'float') AS Value <br />
FROM T_TEST <br />
WHERE (ColXML.exist('(//E[@Value gt 456])')= 1) <br />
GO
A+
En fait on peut faire du parallélisme en OLTP, si tout est cohérent en matière de parallélisation. Par exemple :
1) que les données de la base soit réparties sur différents fichiers situés sur des axes physiques séparés
2) que l’on ait au moins n fichiers pour la tempdb (n étant le nombre de CPU physique) et toujours sur des axes physiques séparés.
3) que les journaux de transactions soient stockés sur un agrégat RAID 10 sur 4, 6 ou 8 disques
4) que les requêtes qui portent sur des volumes important de données ne prévoient pas de point de contention qui déparallélise comme des fonctions tables ou du XML requêté par XQuery/XPath
5) que l’on limite le parallélisme à une valeur raisonnable (par exemple 4 CPU sur un 8 ou 12 coeurs).
6) que l’on étudie la combinaison du paramétrage mode fibre et masque d’affinité en fonction du nombre et de la volumétrie moyenne des requêtes.
A +
Sympa.
Pourrais-tu, aussitôt familiarisé à l’outil de Nono40 pour la mise au gabarit des articles, et aussitôt ton domaine activé, transformer ce petit billet en un véritable article et m’en fournir le lien ?
D’avance merci
Fadace