[SQL Server] CPU/DMV : parallélisme et CXPACKET

Ce billet évoque le parallélisme, et tout particulièrement le type d’attente CXPACKET (Class Exchange Packet).

CXPACKET : késako ?

Il s’agit d’un type d’attente impliqué dans les exécutions parallèles. Il indique qu’un processus est en attente d’un traitement parallèle pour (commencer à) travailler, ce qui fait qu’une attente trop longue peu mener vers un goulot d’étranglement.

Une telle situation intervient généralement lorsque l’optimiseur de requêtes doit traiter une requête dont le plan d’exécution est estimé coûteux (manque d’indexes, requête non SARGeable, mauvaises jointures,…).

Détection d’un CXPACKET

La meilleure façon de détecter ce type d’attente est d’utiliser la DMV sys.dm_os_wait_stats :

SELECT wait_type
 ,wait_time_ms
 ,CONVERT(DECIMAL(7,4),100.0 * wait_time_ms/SUM(wait_time_ms) OVER()) AS wait_percent
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') -- filtrage de quelques types d'attente
ORDER BY wait_percent DESC
GO

Si la valeur de la colonne wait_percent de CXPACKET vaut plus de 5%, des goulots d’étranglement au niveau du parallélisme sont présents.

Il est, d’ailleurs, possible d’utiliser, en complément, le Perfmon afin d’avoir une idée des temps CPU (Processor\%ProcessorTime) et des changements de contexte (System\ContextSwitches/sec). Voir ici.

Comment résoudre les problèmes d’attentes CXPACKET ?

Il existe diverses façons de s’y prendre, avec notamment :

  • La redéfinition du nombre de processeurs à utiliser via MAXDOP (maximum degree of parallelism), soit à une valeur adéquate, soit en le désactivant tout simplement (passage à 1) comme suit :
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'max degree of parallelism', 1
GO
RECONFIGURE WITH OVERRIDE
GO
Notez que si l’environnement est à la fois dédié aux traitements OLTP et à ceux OLAP, et que les temps CXPACKET sont excessivement élevés, nécessitant une désactivant du parallélisme, il est possible de procéder à une redéfinition avec l’indicateur (ou option) de requête MAXDOP pour des requêtes spécifiques, plutôt qu’au niveau de toute l’instance. Par exemple : SELECT col1, col2 FROM tab WHERE col3=’blabla’ OPTION (MAXDOP=1);
Plus de détails ici pour en savoir plus sur le MAXDOP, et pour déterminer les requêtes les plus coûteuses en CPU avec, par exemple, les DMV.
  • La redéfinition du seuil relatif au coût du parallélisme (cost threshold for parallelism) qui, par défaut, est à 5. Il s’agit d’un indicateur permettant à l’optimiseur de requêtes de déterminer au bout de combien de secondes d’exécution d’une requête sérialisée il doit utiliser le parallélisme. La requête ci-dessous permet de monter le seuil à 15 secondes :
EXEC sp_configure 'cost threshold for parallelism', N'10'
GO
RECONFIGURE WITH OVERRIDE
GO

Last but not the least : il est de bon ton de procéder à un contrôle des statistiques et des indexes (un plan de maintenance intégrant des jobs d’optimisation lancés régulièrement est recommandé).

Pour finir…

En soi, la manifestation du type d’attente CXPACKET n’est pas un problème. En effet, si, par exemple, une requête traite différents fichiers de données (ou filegroups) et que l’un desdits fichiers (ou filegroups) est situé sur une partition peu performante, elle peut avoir un temps d’exécution lent, y compris avec le MAXDOP et/ou le seuil de parallélisme redéfinis. Surtout que la désactivation du MAXDOP peut avoir un impact négatif sur les traitements OLAP, ces derniers étant généralement coûteux et logiquement sujets au parallélisme.

Dans tous les cas, avant de désactiver définitivement le parallélisme, un audit général est recommandé (OS, matériel, instance SQL Server, requêtes,…).

Publicités

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s