Un post assez condensé pour rappeler les éléments clés à garder à l’esprit lorsque l’on parle de parallélisme sur SQL Server. Il suppose que vous soyez familiers du concept. Si ce n’est pas le cas, la visite commence par cet excellent article en anglais de Paul White sur simple-talk, ou en français sur MSDN.
Quelques définitions d’abord:
- Trivial Plan: un plan d’exécution est considéré comme trivial lorsqu’il n’y a pas d’alternative possible. Par exemple SELECT COL1, COL2 FROM TABLE.
- Serial Zone: C’est un ensemble de conditions qui font qu’un plan parallèle n’est pas applicable.
Parmi ces conditions on trouve (1):
- Modification d’une variable table.
- Utilisation d’une fonction TSQL ou CLR scalaire. La fonction CLR doit utiliser InitMethod() définie avec DataAccess (https://msdn.microsoft.com/en-us/library/ms131109.aspx) .
- Quelques fonctions builtin OBJECT_NAME() / OBJECT_ID(), ENCRYPTBYCERT(), ERROR_NUMBER(), @@TRANCOUNT…
- Curseurs dynamiques
- Table scan d’objets système
- Backward scan
- Utilisation d’un Global Scalar Aggregate
- Requêtes récursives
- TOP
- Utilisation d’une TVF
- Et potentiellement d’autres…
Depuis SQL Server 2012, un attribut NonParallelPlanReason est ajouté dans le plan XML, qui indique pourquoi le plan n’a pas pu être parallélisé. Voir le post de Joe Sack à ce sujet.
Dans quelles conditions un plan est-il parallélisé ?
Lorsque le plan optimal est trouvé pour une requête, et si les conditions sont favorables (le plan n’est pas trivial, pas de serial zone, le masque CPU présente au moins 2 CPU, MAXDOP est différent de 1, etc…), son coût est comparé à la valeur du paramètre ‘Cost Threshold for Parallelism’. Ce paramètre représente un seuil de temps estimé en secondes pour exécuter un plan sérialisé (sur un seul processeur), par défaut 5 secondes. Si le coût du plan retenu est supérieur à cette valeur, alors l’optimiseur va évaluer un plan parallèle. Ci-dessous, une requête sur AdventureWorks2012, un plan avec un coût de 124.08, et un DOP de 4 à l’exécution:
En fait la formulation plan parallèle n’est pas tout à fait exacte, dans la mesure où seulement une poignée d’opérateurs physiques sont réellement ‘parallel-aware‘, et même parmi ceux-ci, très peu sont structurés pour gérer le parallélisme et implémentent des producteurs et des consommateurs de lignes: principalement les opérateurs Exchange (Gather, Distribute et Repartition). Les autres (Stream Aggregate, Merge Join et Sort dans l’exemple) seront simplement dupliqués à hauteur du DOP par l’opérateur parent (Repartition et Gather dans l’exemple):
En sélectionnant l’opérateur dans le plan XML, et en pressant F4, on obtient des infos sur les conditions de répartition des lignes par thread, par exemple l’Index Scan sur bigTransactionHistory:
Le Parallel Page Supplier est un opérateur de distribution de lignes qui se place en amont de l’index scan (et qui ne se voit pas dans un plan XML). Dans le cas ci-dessus il ne répartit pas uniformément le nombre de lignes à lire par worker. Les workers 1 et 4 vont traiter 90000+ lignes alors que les workers 2 et 3 vont en traiter davantage. Ceci est du aux conditions de charge des schedulers au moment de l’exécution. L’idée est que les canaux les moins encombrés demandent plus de lignes au PPS, mais comme c’est empirique, en sortie de l’opérateur certains workers auront terminé leur tâche avant les autres, et vont devoir attendre (sur CXPACKET).
A noter que le worker noté Thread 0 est le consommateur en bout de chaîne. Son travail est de rassembler les lignes produites par les 4 workers. Il aura toujours 0 lignes affectées.
La détection globale
En règle générale, le parallélisme est une bonne chose. Il permet de maximiser l’utilisation des ressources CPU pour une requête donnée. Mais il peut être aussi le symptôme d’un problème de performance SQL sur l’instance, et d’une mauvaise configuration de MAXDOP (par défaut). En raison du manque d’index ou de couverture des indexes (ORM & Cie), ou d’un problème d’écriture ou de stats, le coût de la plupart des requêtes peut être très élevé et dépasser CTFP, et dans ce cas les requêtes concernées vont paralléliser. On rappelle que le degré de parallélisme est appliqué à chaque entrée et sortie de chaque opérateur parallel-aware. Donc avec un plan profond comme le gouffre de Padirac sur une machine 64 procs, ça peut faire pas mal de workers monopolisés.
Si vous avez assisté à notre démo aux journées SQL en décembre dernier, vous savez comment détecter ce genre de situation: les waits. On peut déterminer les attentes principales grâce à la requête de Glenn Berry à laquelle j’ai ajouté les 3 dernières exclusions pour SQL 2012 (SP_SERVER_DIAGNOSTICS_SLEEP est notamment visible sur les clusters):
WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK',
'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE',
'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT',
'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
'ONDEMAND_TASK_QUEUE', 'BROKER_EVENTHANDLER', 'SLEEP_BPOOL_FLUSH','SP_SERVER_DIAGNOSTICS_SLEEP',
'HADR_FILESTREAM_IOMGR_IOCOMPLETION','DIRTY_PAGE_POLL'))
SELECT W1.wait_type,
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn < = W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 99 OPTION (RECOMPILE);
Sur la plupart des systèmes que l’on audite en conseil, il est plutôt rare de ne pas trouver CXPACKET en premier dans le top des waits. Ca ne veut pas dire grand chose en soi, simplement que des plans sont exécutés en parallèle, et dans ce cas il est normal de voir du CXPACKET comme on l’a vu plus haut. Souvent il est accompagné d’un autre wait, par exemple PAGEIOLATCH_%. Dans ce cas, le problème de fond se trouve sur le disque. On lit trop de données, ou le disque ne répond pas assez vite. Et donc l’attente qui se trouve derrière la lecture (CXPACKET dans un plan parallèle) attend à son tour plus longtemps.
Donc la réponse se trouvera quelque part dans sys.dm_exec_query_stats, en triant sur total_worker_time, et en affichant le plan associé. J’utilise très souvent cette requête sur les audits:
select top 20 S.text 'SQLtext', STAT.execution_count 'Plan reuse (total executions)', STAT.plan_generation_num 'Plans generations', STAT.creation_time 'Last compile time',STAT.last_execution_time 'Last execution time', STAT.total_worker_time/1000 'Total CPU time (ms)', STAT.total_worker_time/1000/STAT.execution_count 'CPU time/exec (ms)', STAT.total_elapsed_time/1000 'Total Elapsed (ms)', STAT.total_elapsed_time/1000/STAT.execution_count 'Total Elapsed/exec (ms)', STAT.total_logical_reads 'Total Logical Reads', STAT.total_logical_reads/STAT.execution_count 'Logical Reads/exec', STAT.total_logical_writes 'Total Logical Writes', STAT.total_logical_writes/STAT.execution_count 'Logical Writes/exec', P.query_plan 'Last query Plan' from sys.dm_exec_query_stats STAT with (NOLOCK) cross apply sys.dm_exec_sql_text(STAT.sql_handle) S cross apply sys.dm_exec_query_plan(STAT.plan_handle) P order by STAT.total_worker_time/1000 desc; GO
Evidemment si l’instance n’est démarrée que depuis 24 heures ça ne va pas être forcément très intéressant. Mais ça permet de trier les requêtes par utilisation CPU, de voir combien de fois le plan a été réutilisé, quel temps de réponse par exécution, etc… Et surtout son coût et s’il a été parallélisé.
Le cercle vertueux de l’analyse de performance débute comme ça: on regarde les attentes, on regarde les requêtes les plus consonmmatrices, et s’attaque à la première, on résout le problème, on regarde les attentes à nouveau , etc…
Degré de parallélisme:
Le degré de parallélisme représente dans un plan parallélisé le nombre de workers impliqués de chaque côté de chaque opérateur parallel-aware. Donc ça ne représente pas du tout le nombre total de workers dans un plan (sauf dans le cas d’un opérateur parallèle unique) (2).
D’ailleurs la valeur de MAXDOP ne sera pas forcément appliquée, comme son nom l’indique, c’est un MAX. Autre élément très important, un plan parallèle est compilé et mis dans le plan cache, mais la valeur du DOP ne sera établie qu’au runtime en fonction de divers paramètres: le paramètre serveur ‘max degree of parallelism’, la présence d’un hint, d’un plan guide ou d’un groupe de ressources RG modifiant la valeur de MAXDOP pour la requête, le nombre de schedulers visibles et le nombre de workers disponibles dans le pool.
Par défaut la valeur du paramètre ‘max degree of parallelism’ est fixée à 0, ce qui signifie que MAXDOP sera égal au nombre de schedulers visibles dans le masque CPU. Le masque CPU (CPU Affinity mask) prenant lui-même par défaut tous les CPU visibles, on peut donc dire pour raccourcir que souvent MAXDOP = nombre de procs.
Sur les machines avec de nombreux CPU, on va trouver de très nombreux workers dans un plan parallèle, et plus il va y en avoir, plus il va y avoir d’attentes sur CXPACKET. Et c’est là que les mauvaises langues se délient sur le net: la solution miracle serait, lit-on partout, de mettre MAXDOP=1 au niveau de l’instance, ce qui revient à ne plus compiler que des plans sérialisés… 🙁
Avant de toucher à MAXDOP…
Ce qu’il faut bien comprendre, c’est qu’un plan est parallélisé parce que son coût est plus élevé que CTFP. Donc la racine du problème, c’est le coût, pas le parralélisme. En réécrivant la requête, en plaçant un index judicieux, on peut faire baisser le coût de manière dramatique.
Si on reprend notre requête sur AdventureWorks2012, en plaçant un index sur bigTransactionHistory (Quantity) include (ProductID), on peut faire baisser le coût de 84%:
Ca passe au millimètre mais surtout j’ai diminué la charge sur l’instance en IO et en CPU, sans toucher à aucun paramètre MAXDOP / CTFP. C’est donc un premier axe d’amélioration: s’occuper de la cause racine du problème en priorité.
Abaisser la valeur de MAXDOP:
Celà dit, plus il y a de données, et plus il est difficile de faire baisser les coûts malgré tout. Dans l’absolu, l’éditeur préconise de limiter la valeur de ‘max degree of parallellism’ à 8 dans le cas d’une machine non-NUMA de plus de 8 CPU, et au nombre de NUMA nodes dans le cas d’une machine NUMA. Si l’hyperthreading est activé, on ne tiendra compte que des cores physiques. (3)
Bitmap filtering:
Le Bitmap Filter (4) est un opérateur utilisé à la fois dans les plans sérialisés (dans un Hash Match) et les plans parallèles. Mais en fait il n’est réellement visible que dans les plans parallèles:
Le but du Bitmap filter est de filtrer le plus tôt possible les lignes arrivant des opérateurs en dessous dans l’exécution. Il est beaucoup utilisé conjointement avec des jointures Hash Match en parallèle comme c’est le cas ici. Lors de la phase de probe, le Bitmap permet de filtrer les lignes qui ne pourront pas être jointes de toute manière avec la table de Build.
Parfois le Bitmap peut être appliqué encore plus en amont dans le plan, par exemple sur un Table Scan ou Index Scan/Seek, dans ce cas l’opérateur sur lequel il est appliqué montre un attribut INROW, comme ici sur un Table Scan:
Il est possible d’observer cependant des écarts d’estimation car l’optimiseur ne sait pas à l’avance combien de lignes seront écartées. Par exemple sur ce Table Scan de 5 millions de lignes, le Bitmap filtre 2488 lignes:
Il ne faut pas se laisser tromper par les apparences. Il n’y a ici ni problèmes de statistiques, ni de timeout dans la génération du plan, ni aucun autre facteur qui pourrait expliquer cette différence. La différence vient du Bitmap filter, et elle est parfaitement normale.
A+. David B.
Références:
(1) Parallel Query Execution whitepaper- Craig Freedman (MSFT)
(2) How it Works: SQL Server Per Query Degree Of Parallelism Worker Count(s) Bob Dorr (MSFT – PSS)
(3) https://support.microsoft.com/kb/2806535/en-us
(4) Bitmap Magic (or… how SQL Server uses bitmap filters) Paul White (MVP)
Continuez votre lecture sur le blog :
- DMV et problème de tri (David Baffaleuf) [SQL Server]
- Oracle et SQL Server: L’optimizer (Benjamin VESAN) [OracleSQL Server]
- Les attentes dans SQL Server (Benjamin VESAN) [SQL Server]
- Nouveautés pg_stat_statements avec PostgreSQL 15 (David Baffaleuf) [PostgreSQL]
- “Pruning” de partitions sous PostgreSQL ou comment bien élaguer ! (Capdata team) [PostgreSQL]