![\"twitter\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/twitter.png\" "\\"Share")
<\/a><\/a>![\"linkedin\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/linkedin.png\" "\\"Share")
<\/a>![\"mail\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/mail.png\" "\\"Share")
<\/a>Un post assez condens\u00e9 pour rappeler les \u00e9l\u00e9ments cl\u00e9s \u00e0 garder \u00e0 l’esprit lorsque l’on parle de parall\u00e9lisme 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 <\/a>en anglais de Paul White sur simple-talk, ou en fran\u00e7ais sur MSDN<\/a>.<\/p>\n Parmi ces conditions on trouve (1)<\/em>:<\/p>\n Depuis SQL Server 2012, un attribut NonParallelPlanReason<\/em> est ajout\u00e9 dans le plan XML, qui indique pourquoi le plan n’a pas pu \u00eatre parall\u00e9lis\u00e9. Voir le post <\/a>de Joe Sack \u00e0 ce sujet.<\/p>\n Lorsque le plan optimal est trouv\u00e9 pour une requ\u00eate, et si les conditions sont favorables (le plan n’est pas trivial, pas de serial zone, le masque CPU pr\u00e9sente au moins 2 CPU, MAXDOP est diff\u00e9rent de 1, etc…), son co\u00fbt est compar\u00e9 \u00e0 la valeur du param\u00e8tre ‘Cost Threshold for Parallelism’. Ce param\u00e8tre repr\u00e9sente un seuil de temps estim\u00e9 en secondes pour ex\u00e9cuter un plan s\u00e9rialis\u00e9 (sur un seul processeur), par d\u00e9faut 5 secondes. Si le co\u00fbt du plan retenu est sup\u00e9rieur \u00e0 cette valeur, alors l’optimiseur va \u00e9valuer un plan parall\u00e8le. Ci-dessous, une requ\u00eate sur AdventureWorks2012, un plan avec un co\u00fbt de 124.08, et un DOP de 4 \u00e0 l’ex\u00e9cution:<\/p>\n En fait la formulation plan parall\u00e8le<\/em> n’est pas tout \u00e0 fait exacte, dans la mesure o\u00f9 seulement une poign\u00e9e d’op\u00e9rateurs physiques sont r\u00e9ellement ‘parallel-aware<\/em>‘, et m\u00eame parmi ceux-ci, tr\u00e8s peu sont structur\u00e9s pour g\u00e9rer le parall\u00e9lisme et impl\u00e9mentent des producteurs et des consommateurs de lignes: principalement les op\u00e9rateurs Exchange (Gather, Distribute et Repartition). Les autres (Stream Aggregate, Merge Join et Sort dans l’exemple) seront simplement dupliqu\u00e9s \u00e0 hauteur du DOP par l’op\u00e9rateur parent (Repartition et Gather dans l’exemple):<\/p>\n Le Parallel Page Supplier est un op\u00e9rateur 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\u00e9partit pas uniform\u00e9ment le nombre de lignes \u00e0 lire par worker. Les workers 1 et 4 vont traiter 90000+ lignes alors que les workers 2 et 3 vont en\u00a0traiter davantage. Ceci est du aux conditions de charge des schedulers au moment de l’ex\u00e9cution. L’id\u00e9e est que les canaux les moins encombr\u00e9s demandent plus de lignes au PPS, mais comme c’est empirique, en sortie de l’op\u00e9rateur certains workers auront termin\u00e9 leur t\u00e2che avant les autres, et vont devoir attendre (sur CXPACKET).<\/p>\n A noter que le worker not\u00e9 Thread 0<\/em> est le consommateur en bout de cha\u00eene. Son travail est de rassembler les lignes produites par les 4 workers. Il aura toujours 0 lignes affect\u00e9es.<\/p>\n En r\u00e8gle g\u00e9n\u00e9rale, le parall\u00e9lisme est une bonne chose. Il permet de maximiser l’utilisation des ressources CPU pour une requ\u00eate donn\u00e9e. Mais il peut \u00eatre aussi le sympt\u00f4me d’un probl\u00e8me de performance SQL sur l’instance, et d’une mauvaise configuration de MAXDOP (par d\u00e9faut). En raison du manque d’index ou de couverture des indexes (ORM & Cie), ou d’un probl\u00e8me d’\u00e9criture ou de stats, le co\u00fbt de la plupart des requ\u00eates peut \u00eatre tr\u00e8s \u00e9lev\u00e9 et d\u00e9passer CTFP, et dans ce cas les requ\u00eates concern\u00e9es vont parall\u00e9liser. On rappelle que le degr\u00e9 de parall\u00e9lisme est appliqu\u00e9 \u00e0 chaque entr\u00e9e et sortie de chaque op\u00e9rateur parallel-aware. Donc avec un plan profond comme le gouffre de Padirac sur une machine 64 procs, \u00e7a peut faire pas mal de workers monopolis\u00e9s.<\/p>\n Si vous avez assist\u00e9 \u00e0 notre d\u00e9mo<\/a> aux journ\u00e9es SQL en d\u00e9cembre dernier, vous savez comment d\u00e9tecter ce genre de situation: les waits. On peut d\u00e9terminer les attentes principales gr\u00e2ce \u00e0 la requ\u00eate de Glenn Berry <\/a>\u00e0 laquelle j’ai ajout\u00e9 les 3 derni\u00e8res exclusions pour SQL 2012 (SP_SERVER_DIAGNOSTICS_SLEEP est notamment visible sur les clusters):<\/p>\n Sur la plupart des syst\u00e8mes que l’on audite en conseil, il est plut\u00f4t 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\u00e9cut\u00e9s en parall\u00e8le, et dans ce cas il est normal de voir du CXPACKET comme on l’a vu plus haut. Souvent il est accompagn\u00e9 d’un autre wait, par exemple PAGEIOLATCH_%. Dans ce cas, le probl\u00e8me de fond se trouve sur le disque. On lit trop de donn\u00e9es, ou le disque ne r\u00e9pond pas assez vite. Et donc l’attente qui se trouve derri\u00e8re la lecture (CXPACKET dans un plan parall\u00e8le) attend \u00e0 son tour plus longtemps.<\/p>\n Donc la r\u00e9ponse se trouvera quelque part dans sys.dm_exec_query_stats, en triant sur total_worker_time, et en affichant le plan associ\u00e9. J’utilise tr\u00e8s souvent cette requ\u00eate sur les audits:<\/p>\n Evidemment si l’instance n’est d\u00e9marr\u00e9e que depuis 24 heures \u00e7a ne va pas \u00eatre forc\u00e9ment tr\u00e8s int\u00e9ressant. Mais \u00e7a permet de trier les requ\u00eates par utilisation CPU, de voir combien de fois le plan a \u00e9t\u00e9 r\u00e9utilis\u00e9, quel temps de r\u00e9ponse par ex\u00e9cution, etc… Et surtout son co\u00fbt et s’il a \u00e9t\u00e9 parall\u00e9lis\u00e9.<\/p>\n Le cercle vertueux de l’analyse de performance d\u00e9bute comme \u00e7a: on regarde les attentes, on regarde les requ\u00eates les plus consonmmatrices, et s’attaque \u00e0 la premi\u00e8re, on r\u00e9sout le probl\u00e8me, on regarde les attentes \u00e0 nouveau , etc…<\/p>\n Le degr\u00e9 de parall\u00e9lisme repr\u00e9sente dans un plan parall\u00e9lis\u00e9 le nombre de workers impliqu\u00e9s de chaque c\u00f4t\u00e9 de chaque op\u00e9rateur parallel-aware. Donc \u00e7a ne repr\u00e9sente pas du tout le nombre total de workers dans un plan (sauf dans le cas d’un op\u00e9rateur parall\u00e8le unique) (2).<\/p>\n D’ailleurs la valeur de MAXDOP ne sera pas forc\u00e9ment appliqu\u00e9e, comme son nom l’indique, c’est un MAX. Autre \u00e9l\u00e9ment tr\u00e8s important, un plan parall\u00e8le est compil\u00e9 et mis dans le plan cache, mais la valeur du DOP ne sera \u00e9tablie qu’au runtime en fonction de divers param\u00e8tres: le param\u00e8tre serveur ‘max degree of parallelism’<\/em>, la pr\u00e9sence d’un hint, d’un plan guide ou d’un groupe de ressources RG modifiant la valeur de MAXDOP pour la requ\u00eate, le nombre de schedulers visibles et le nombre de workers disponibles dans le pool.<\/p>\n Par d\u00e9faut la valeur du param\u00e8tre ‘max degree of parallelism’ est fix\u00e9e \u00e0 0, ce qui signifie que MAXDOP sera \u00e9gal au nombre de schedulers visibles dans le masque CPU. Le masque CPU (CPU Affinity mask) prenant lui-m\u00eame par d\u00e9faut tous les CPU visibles, on peut donc dire pour raccourcir que souvent MAXDOP = nombre de procs.<\/p>\n Sur les machines avec de nombreux CPU, on va trouver de tr\u00e8s nombreux workers dans un plan parall\u00e8le, et plus il va y en avoir, plus il va y avoir d’attentes sur CXPACKET. Et c’est l\u00e0 que les mauvaises langues se d\u00e9lient sur le net: la solution miracle serait, lit-on partout, de mettre MAXDOP=1 au niveau de l’instance, ce qui revient \u00e0 ne plus compiler que des plans s\u00e9rialis\u00e9s… \ud83d\ude41<\/p>\n Ce qu’il faut bien comprendre, c’est qu’un plan est parall\u00e9lis\u00e9 parce que son co\u00fbt est plus \u00e9lev\u00e9 que CTFP. Donc la racine du probl\u00e8me, c’est le co\u00fbt, pas le parral\u00e9lisme. En r\u00e9\u00e9crivant la requ\u00eate, en pla\u00e7ant un index judicieux, on peut faire baisser le co\u00fbt de mani\u00e8re dramatique.<\/p>\n Si on reprend notre requ\u00eate sur AdventureWorks2012, en pla\u00e7ant un index sur bigTransactionHistory (Quantity) include (ProductID), on peut faire baisser le co\u00fbt de 84%:<\/p>\n Ca passe au millim\u00e8tre mais surtout j’ai diminu\u00e9 la charge sur l’instance en IO et en CPU, sans toucher \u00e0 aucun param\u00e8tre MAXDOP \/ CTFP. C’est donc un premier axe d’am\u00e9lioration: s’occuper de la cause racine du probl\u00e8me en priorit\u00e9.<\/p>\n Cel\u00e0 dit, plus il y a de donn\u00e9es, et plus il est difficile de faire baisser les co\u00fbts malgr\u00e9 tout. Dans l’absolu, l’\u00e9diteur pr\u00e9conise de limiter la valeur de ‘max degree of parallellism’ \u00e0 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\u00e9, on ne tiendra compte que des cores physiques. (3)<\/p>\n Le Bitmap Filter (4) est un op\u00e9rateur utilis\u00e9 \u00e0 la fois dans les plans s\u00e9rialis\u00e9s (dans un Hash Match) et les plans parall\u00e8les. Mais en fait il n’est r\u00e9ellement visible que dans les plans parall\u00e8les:<\/p>\n Le but du Bitmap filter est de filtrer le plus t\u00f4t possible les lignes arrivant des op\u00e9rateurs en dessous dans l’ex\u00e9cution. Il est beaucoup utilis\u00e9 conjointement avec des jointures Hash Match en parall\u00e8le comme c’est le cas ici. Lors de la phase de probe, le Bitmap permet de filtrer les lignes qui ne pourront pas \u00eatre jointes de toute mani\u00e8re avec la table de Build.<\/p>\n Parfois le Bitmap peut \u00eatre appliqu\u00e9 encore plus en amont dans le plan, par exemple sur un Table Scan ou Index Scan\/Seek, dans ce cas l’op\u00e9rateur sur lequel il est appliqu\u00e9 montre un attribut INROW, comme ici sur un Table Scan:<\/p>\n Il est possible d’observer cependant des \u00e9carts d’estimation car l’optimiseur ne sait pas \u00e0 l’avance combien de lignes seront \u00e9cart\u00e9es. Par exemple sur ce Table Scan de 5 millions de lignes, le Bitmap filtre 2488 lignes:<\/p>\n Il ne faut pas se laisser tromper par les apparences. Il n’y a ici ni probl\u00e8mes de statistiques, ni de timeout dans la g\u00e9n\u00e9ration du plan, ni aucun autre facteur qui pourrait expliquer cette diff\u00e9rence. La diff\u00e9rence vient du Bitmap filter, et elle est parfaitement normale.<\/p>\n A+. David B.<\/p>\n \u00a0R\u00e9f\u00e9rences:<\/em> <\/p>\n Un post assez condens\u00e9 pour rappeler les \u00e9l\u00e9ments cl\u00e9s \u00e0 garder \u00e0 l’esprit lorsque l’on parle de parall\u00e9lisme sur SQL Server. Il suppose que vous soyez familiers du concept. Si ce n’est pas le cas, la visite commence par cet… Continuer la lecture Quelques d\u00e9finitions d’abord:<\/h2>\n
\n
\n
\n
Dans quelles conditions un plan est-il parall\u00e9lis\u00e9 ?<\/h2>\n
![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2013\/05\/parallel_cost1.png\" "\\"parallel_cost\\"")
<\/a><\/p>\n![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2013\/05\/parallel_plan_1.png\" "\\"parallel_plan_1\\"")
<\/a>En s\u00e9lectionnant l’op\u00e9rateur dans le plan XML, et en pressant F4, on obtient des infos sur les conditions de r\u00e9partition des lignes par thread, par exemple l’Index Scan sur bigTransactionHistory:<\/p>\n![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2013\/05\/maxdop_details.png\" "\\"maxdop_details\\"")
<\/a><\/p>\nLa d\u00e9tection globale<\/h2>\n
WITH Waits AS\r\n(SELECT wait_type, wait_time_ms \/ 1000. AS wait_time_s,\r\n100. * wait_time_ms \/ SUM(wait_time_ms) OVER() AS pct,\r\nROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn\r\nFROM sys.dm_os_wait_stats\r\nWHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK',\r\n'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE',\r\n'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT',\r\n'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT',\r\n'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',\r\n'ONDEMAND_TASK_QUEUE', 'BROKER_EVENTHANDLER', 'SLEEP_BPOOL_FLUSH','SP_SERVER_DIAGNOSTICS_SLEEP',\r\n'HADR_FILESTREAM_IOMGR_IOCOMPLETION','DIRTY_PAGE_POLL'))\r\nSELECT W1.wait_type, \r\nCAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,\r\nCAST(W1.pct AS DECIMAL(12, 2)) AS pct,\r\nCAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct\r\nFROM Waits AS W1\r\nINNER JOIN Waits AS W2\r\nON W2.rn < = W1.rn\r\nGROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct\r\nHAVING SUM(W2.pct) - W1.pct < 99 OPTION (RECOMPILE);<\/pre>\n![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2013\/05\/CXPACKET_waits.png\" "\\"CXPACKET_waits\\"")
<\/a><\/p>\nselect top 20 S.text 'SQLtext', STAT.execution_count 'Plan reuse (total executions)', STAT.plan_generation_num 'Plans generations', \r\nSTAT.creation_time 'Last compile time',STAT.last_execution_time 'Last execution time',\r\nSTAT.total_worker_time\/1000 'Total CPU time (ms)', STAT.total_worker_time\/1000\/STAT.execution_count 'CPU time\/exec (ms)',\r\nSTAT.total_elapsed_time\/1000 'Total Elapsed (ms)', STAT.total_elapsed_time\/1000\/STAT.execution_count 'Total Elapsed\/exec (ms)',\r\nSTAT.total_logical_reads 'Total Logical Reads', STAT.total_logical_reads\/STAT.execution_count 'Logical Reads\/exec', \r\nSTAT.total_logical_writes 'Total Logical Writes', STAT.total_logical_writes\/STAT.execution_count 'Logical Writes\/exec', \r\nP.query_plan 'Last query Plan'\r\nfrom sys.dm_exec_query_stats STAT with (NOLOCK)\r\ncross apply sys.dm_exec_sql_text(STAT.sql_handle) S\r\ncross apply sys.dm_exec_query_plan(STAT.plan_handle) P\r\norder by STAT.total_worker_time\/1000 desc;\r\nGO<\/pre>\n
Degr\u00e9 de parall\u00e9lisme:<\/h2>\n
Avant de toucher \u00e0 MAXDOP…<\/h2>\n
![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2013\/05\/cheapplan.png\" "\\"cheapplan\\"")
<\/a><\/p>\nAbaisser la valeur de MAXDOP:<\/h2>\n
Bitmap filtering:<\/h2>\n
![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2013\/05\/Bitmapexample1.png\" "\\"Bitmapexample1\\"")
<\/a><\/p>\n![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2013\/05\/inrow_bitmap_filter.png\" "\\"inrow_bitmap_filter\\"")
<\/a><\/p>\n![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2013\/05\/bitmapestimatesdifferences.png\" "\\"bitmapestimatesdifferences\\"")
<\/a><\/p>\n
\n(1) Parallel Query Execution whitepaper- Craig Freedman (MSFT)<\/a>
\n(2) How it Works: SQL Server Per Query Degree Of Parallelism Worker Count(s) Bob Dorr (MSFT – PSS) <\/a>
\n(3) https:\/\/support.microsoft.com\/kb\/2806535\/en-us<\/a>
\n(4) Bitmap Magic (or\u2026 how SQL Server uses bitmap filters) Paul White (MVP) <\/a><\/p>\n<\/a><\/a><\/a>","protected":false},"excerpt":{"rendered":"