SQL Server faisant nativement des requêtes parallélisées dès que le cout dépasse un certain seuil, il est conseillé de limiter le degré de parallélisme. mais comment savoir quel est le bon réglage ? Voici une méthode simple pour y parvenir.
FONCTIONNEMENT DU PARALLÉLISME D’EXÉCUTION DANS MS SQL SERVER
Lorsque SQL Server reçoit requête, le moteur transforme la requête SQL en une formule mathématique de l’algèbre relationnelle et effectue une simplification mathématique. C’est la phase d’algébrisation. Puis il calcule un plan d’exécution estimé le plus rapide pour la requête considérée. C’est le rôle de l’optimiseur qui se base d’abord sur les contraintes pour une première phase d’élimination d’opération (optimisation sémantique) puis procède à une évaluation des différentes méthodes d’accès et d’opérateur, c’est l’optimisation statistique. À ce stade la requête est évalué avec un coût (il n’y a pas d’unité à ce coût) qui sert juste pour la comparaison avec d’autres « coûts ».
Si le coût dépasse le seuil de déclenchement du parallélisme fixé dans la configuration du serveur (paramètre « cost threshold for parallelism » de sys.configuration) alors la requête est ré évaluée en tentant d’effectuer le maximum d’opérations de manière parallèle. Un second coût est alors obtenu. Si le coût de la requête parallélisé est inférieur à la requête mono threadée alors cette dernière est choisie et l’exécution s’effectue avec le plan parallélisé.
Seuil de coût de déclenchement du parallélisme (sys.configurations)
MAIS COMBIEN DE THREADS SONT UTILISÉS ?
Le nombre de threads utilisé par SQL Server dépend de 3 facteurs :
- Le nombre de cÅ“urs utilisés par SQL Server (réglages « affinity… mask » dans sys.configuration) et à défaut de ce réglage, le nombre de cÅ“urs de la machine (par exemple pour une machine bi-CPU avec 8 cÅ“urs sur chaque CPU et hyperthreadé, ce sera 32…)
- L’éventuelle limite fixée dans la requête si la clause OPTION est utilisé avec le paramètre MAXDOP.
- Et sans le MAXDOP de la clause OPTION; la limité fixée globalement par le paramètre « max degree of parallelism » visible dans la vue sys.configurations
En l’absence d’un quelconque réglage, SQL Server prends tous les threads disponible pour exécuter une requête parallélisée et ceci peut être préjudicieux :
- aux performances !
- Ã la concurrence
…
Parallélisme et pertes de performances :
Le parallélisme induit forcément des temps d’attente appelés « rendez-vous de tâches ». En gros, lorsqu’une opération est parallélisée certains threads finissent avant d’autres mais doivent continuer d’exister sans pour autant « travailler » jusqu’à ce que le dernier thread ait terminé son exécution. Alors la phase de collecte des informations peut enfin être réalisée, passant du multi thread au mono thread pour produire un résultat final d’étape. Ces types d’attente sont connus dans SQL Server sous le nom de CXPACKET (mais aussi CXROWSET_SYNC). On peut en mesurer le cumul dans la vue :
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE 'CX%';
Ce cumul a démarré depuis le lancement du serveur ou depuis la dernière mise à zéro de ces statistiques, effectuée via la commande :
Parallélisme et concurrence :
Il est évident que si tous les cÅ“urs sont utilisés par une même requête, alors aucune autre requête ne peut de lancer simultanément, conduisant à une contention de la concurrence d’accès. Il convient donc de ne jamais laisser SQL Server prendre tous les cÅ“urs pour une même requête, sauf cas particulier (DataWarehousing par exemple).
Combien ???
Sans réglage, le seuil de coût est fixé à 5 et tous les cœurs disponibles sont utilisés.
On peut se rendre compte du nombre de cÅ“urs utilisés par la dernière exécution de chaque requête parallélisées, à l’aide de la requête suivante :
SELECT DB_NAME(p.dbid) AS DATABASE_CONTEXT,
x.VALUE('(@StatementText)[1]', 'NVARCHAR(max)') AS SQL_QUERY,
p.query_plan AS EXECUTION_PLAN,
x.VALUE('(@StatementOptmLevel)[1]', 'VARCHAR(25)') AS OPTIMIZATION_LEVEL,
x.VALUE('(@StatementSubTreeCost)[1]', 'float') AS QUERY_COST,
c.usecounts AS THRREAD_COUNT
FROM sys.dm_exec_cached_plans AS c WITH(NOLOCK)
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS p
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS T(x)
WHERE x.query('.').exist('//RelOp[@PhysicalOp="Parallelism"]') = 1;
ATTENTON : l’éditeur de developpez.com modifiant automatiquement le texte des requêtes, il vous faudra les rectifier à la main en récrivant les méthodes VALUE en minuscule (value) dans la requête !
Le paramètre MAXDOP de la clause OPTION, force l’adoption d’un nombre de cÅ“ur comme précisé par la valeur du paramètre, si le plan doit être parallélisé (s’il dépasse le seuil de coût fixé par le « cost threshold for parallelism » de sys.configuration).
Sinon, à défaut de précision de MAXDOP dans la requête, c’est la limite fixée par « max degree of parallelism » qui est utilisée.
LES BONS RÉGLAGES
Pour une base de données OLTP (et donc, pas pour la BI avec ses bases OLAP) il convient de ne jamais trop forcer le parallélisme.
Tout d’abord le seuil de déclenchement du parallélisme (« cost threshold for parallelism« ) ayant été fixé à 5 il y a de nombreuses années (en 1999 avec la version 7 ?), les ordinateurs ayant largement progressé dans leurs performances depuis lors, il est certains que ce seuil de coût doit être relevé. Le placer entre 10 et 25 est un bon début.
Ceci se fait avec la procédure sp_configure. Exemple :
.
Avec un serveur ne possédant qu’un cÅ“ur ou 2, la limitation du degré de parallélisme doit être de 1, autrement dit pas de parallélisme. À 4 cÅ“urs, vous pouvez tenter 2. Au delà , il convient de ne jamais dépasser la moitié des CPU moins 1 ou 2, et de toute façon jamais plus que vos CPU physique ne compte de cÅ“urs.
Par exemple avec un serveur ayant 2 CPU physiques à 12 cœurs, limiter à 10 parait correct.
Autre exemple, avec un serveur à 4 CPU physiques à 8 cœurs hyperthreadés (64 coeurs), ne pas dépasser 10 est aussi un bon réglage !
Dans ces deux cax, le réglage pourra être effectué comme suit :
Par défaut le réglage est à 0, ce qui signifie prendre tous les cœurs disponibles.
AFFINER LE RÉGLAGE
Un moyen simple d’affiner le réglage est de tester différentes options du MAXDOP avec quelques requêtes étalons.
Il suffit de trouver par exemple les 10 requêtes les plus couteuses ayant des plans parallélisés et de les exécuter avec les différentes valeurs du MAXDOP. La requête suivante permet de trouver ces requêtes.
FROM sys.dm_exec_query_stats AS s WITH(NOLOCK)
CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) p
CROSS APPLY sys.dm_exec_sql_text(s.plan_handle) AS q
WHERE p.query_plan.VALUE('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
max(//p:RelOp/@Parallel)', 'float') > 0
ORDER BY total_worker_time/execution_count DESC
OPTION (MAXDOP 1);
ATTENTON : l’éditeur de developpez.com modifiant automatiquement le texte des requêtes, il vous faudra les rectifier à la main en récrivant les méthodes VALUE en minuscule (value) dans la requête !
ATTENTION : ne pas étalonner ces mesures sur des requêtes de mise à jour (INSERT, UPDATE, DELETE…).
Une telle requête peut, par exemple, vous donner les résultats suivants :
Recherche des requêtes avec plan d’exécution en parallèle
Il vous suffit alors de prendre une par une ces requêtes et de les exécuter en les mesurant au niveau des temps de réponse, puis prendre le réglage, non pas du meilleurs temps, mais de celui précédent.
Exemple.. La requête suivante :
GO
USE MA_BASE;
GO
SELECT COUNT(OTPID) AS NBRESULT,
SUM(OTPMTVTED2) AS OTPMTVTED2,
SUM(OTPMTD1) AS OTPMTD1,
SUM((OTPMTVTED2 - OTPMTD1)) AS OTPMTD5
FROM ORDPLA, ORDRE, ORDRES, ORDPLAS
WHERE OTPID = OPSOTPID
AND OSROTSID = OTSID
AND (OTSDOSNUM = '' OR OTSDOSNUM IS NULL)
AND (OTPTYPE = 2 OR OTPTYPE = 1)
AND OTPOTSID = OTSID
AND OTSETTNIV >= 4
AND OTSTYPE 7
AND OTPTYPE = 2
AND OTPSOCID IN (4)
OPTION (MAXDOP 1);
Si par exemple votre serveur compte 4 cÅ“urs, alors lancez cette requête avec les valeurs 1, 2, 3 et 4 au niveau du MAXDOP et recueillez à chaque fois les métrique de durée dans l’onglet Messages du panneau des résultats de SSMS.
Voici par exemple ce que nous avons obtenu pour cette requête :
MAXDOP 1 : Temps UC = 1984 ms, temps écoulé = 1985 ms.
MAXDOP 2 : Temps UC = 3109 ms, temps écoulé = 1725 ms.
MAXDOP 3 : Temps UC = 3470 ms, temps écoulé = 1163 ms.
MAXDOP 4 : Temps UC = 3328 ms, temps écoulé = 1195 ms.
Le dernier temps écoulé avec MAXDOP 4 étant moins bon que le précédent avec MAXDOP 3 et très voisin en temps CPU, nous prenons comme référence la valeur 3 et avec notre règle du moins 1 cela fera 2. Cela tombe bien, car c’est juste la moitié du nombre de cÅ“urs de notre serveur…
Dans ce cas, votre réglage de configuration devra être fait comme suit :
* * *
Expert S.G.B.D relationnelles et langage S.Q.L
Moste Valuable Professionnal Microsoft SQL Server
Société SQLspot : modélisation, conseil, formation,
optimisation, audit, tuning, administration SGBDR
Enseignant: CNAM PACA, ISEN Toulon, CESI Aix en Prov.
Développez et administrez pour la performance avec SQL Server 2014
L’entreprise SQL Spot
Le site web sur le SQL et les SGBDR
