APSQL

Interroger sys.dm_os_wait_stats

Lorsqu'un problème de performance se produit il n'est pas facile de savoir par où commencer pour identifier la source du problème. Bien sur des outils comme les compteurs de performances ou sql server profiler (surtout couplé à l'optimiseur de base de données) sont performants. Mais leur champ d'action est si vaste qu'il est nécessaire d'identifier le secteur à analyser en détail

SQL Server propose un ensemble de vues dynamique connues sous le nom DMV. Ces vues sont dites dynamique car les données visibles au travers de ces vues sont basés sur la collecte d'information depuis le dernier démarrage du moteur de base de données. Il est possible de réinitialiser les compteurs de cette vue à l'aide de l'instruction DBCC suivante:

DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
			

La vue dm_os_wait_stats constient les informations nécessaire pour déterminer si l'origine du problème de performance est du à un problème disque, processeur ou mémoire. En effet chaque fois que SQL Server est obligé de d'attendre une ressource, cette attente est enregistré dans la vue dm_os_wait_stats.

Les attentes sont présentées dans cette vue à raison de 1 ligne pour chaque type d'attente recensées au niveaude SQL Serveur. Ce nombre d'attente possible augmente au fur à mesure des versions de SQL Server, permettant ainsi d'obtenir une meilleure précision

Cette vue est composée des 5 colonnes suivantes:

  • wait_type: nom du type de l'attente. Les diffétents types d'attente définis au niveau de SQL Server 2008 sont disponibles sur http://msdn.microsoft.com/fr-fr/library/ms179984.aspx.
  • waiting_tasks_count: nombre d'attente enregistré pour le type d'attente correspondant.
  • waiting_time_ms: temps total d'attente pour ce type d'attente. Cette durée est exprimée en millisecondes.
  • max_wait_time_ms: il s'agit cette fois ci du temps maximal d'attente (toujours en millisecondes) pour cette ressource.
  • signal_wait_time_ms: temps écoulé entre le moment ou le thread a était avertir de la disponibilité de la resource et l'exécution de ce thread.

SQL Server 2008 proposant plus de 480 type d'attentes différents, une requête de type SELECT * n'a strictement aucun sens car difficilement exploitable. Par contre la connaissance des attentes les plus fréquente est très interressante. Pour obtenir ce type de résultat il est nécessaire de calculer pour chaque type d'attente ce que le temps d'attente pour cette ressource represente en terme de pourcentage par rapport au temps total d'attente et de classer par ordre de décroissance. Bien qu'il soit tout à fait possible d'écrire cette requête, il est possible de faire référence à la requête mise au point dans le livre SQL Server MVP Deep Dives. Cette requête est donc la suivante:

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'))
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;