Lors du diagnostic de problèmes de performance sur une instance SQL Server, la première étape est toujours de comprendre ce qui se passe en ce moment : quelles requêtes s’exécutent, quelles sessions en bloquent d’autres, et où le temps est passé à attendre. Ces trois scripts T-SQL vous donnent une image immédiate de la charge de travail actuelle grâce aux Dynamic Management Views (DMVs).

1. Requêtes bloquées et bloquantes

Si cette requête ne retourne aucune ligne, il n’y a pas de requêtes bloquées en ce moment. Exécutez-la plusieurs fois pour attraper des chaînes de blocage de courte durée. Note : cela ne révèle que le blocage actuel — la contention de verrous à court terme cumulative nécessite une analyse des wait stats (voir section 2).

SELECT 'BLOCKING STATUS' AS Controllo,
    BlockedSPID     = LEFT(blocked.session_id, 5),
    BlockedQuery    = CONVERT(VARCHAR(50), blockedsql.text),
    BlockingSPID    = CONVERT(VARCHAR(50), blocking.session_id),
    BlockingQuery   = CONVERT(VARCHAR(50), blockingsql.text)
FROM sys.dm_exec_requests blocked
JOIN sys.dm_exec_requests blocking
    ON blocked.blocking_session_id = blocking.session_id
CROSS APPLY (SELECT * FROM sys.dm_exec_sql_text(blocked.sql_handle))  blockedsql
CROSS APPLY (SELECT * FROM sys.dm_exec_sql_text(blocking.sql_handle)) blockingsql;

2. Analyse des événements d’attente

SQL Server collecte en continu des statistiques d’attente dans sys.dm_os_wait_stats depuis le dernier redémarrage de l’instance. Réinitialiser cette vue et collecter des données sur une courte fenêtre vous donne une image focalisée de ce sur quoi l’instance passe son temps à attendre — I/O, CPU, verrous, latches, et plus encore.

DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR); -- Réinitialiser la DMV
GO

SELECT
    [owt].[session_id],
    [owt].[exec_context_id],
    [owt].[wait_duration_ms],
    [owt].[wait_type],
    [owt].[blocking_session_id],
    [owt].[resource_description],
    [es].[program_name],
    [est].[text],
    [est].[dbid],
    [eqp].[query_plan],
    [es].[cpu_time],
    [es].[memory_usage]
FROM sys.dm_os_waiting_tasks [owt]
INNER JOIN sys.dm_exec_sessions  [es]  ON [owt].[session_id] = [es].[session_id]
INNER JOIN sys.dm_exec_requests  [er]  ON [es].[session_id]  = [er].[session_id]
OUTER APPLY sys.dm_exec_sql_text   ([er].[sql_handle])   [est]
OUTER APPLY sys.dm_exec_query_plan ([er].[plan_handle])  [eqp]
WHERE [es].[is_user_process] = 1;

3. Requêtes actuellement actives

Cette requête retourne toutes les requêtes actuellement en cours d’exécution avec leur texte SQL, leur plan d’exécution, l’utilisation CPU et les attentes actuelles.

SELECT
    r.session_id,
    r.status,
    r.wait_type,
    r.wait_time,
    r.cpu_time,
    r.total_elapsed_time,
    SUBSTRING(st.text, (r.statement_start_offset/2)+1,
        ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
          ELSE r.statement_end_offset END - r.statement_start_offset)/2)+1) AS statement_text,
    qp.query_plan
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) qp
WHERE r.session_id > 50  -- Exclure les processus système
  AND r.session_id != @@SPID
ORDER BY r.total_elapsed_time DESC;