Parcourir les aide-mémoire · SQL Server

Technologie: SQL ServerCatégorie: PerformanceNiveau: IntermédiaireMis à jour

Trouver les requêtes de longue durée

Identifiez les requêtes SQL Server de longue durée en cours et les requêtes lentes historiques, distinguez le temps écoulé du temps CPU et déterminez si le retard provient des attentes, des blocages, des lectures ou d’une régression de plan.

Sur cette page
  1. Problème
  2. Réponse rapide
  3. Code / commandes
  4. 1. Trouver les requêtes utilisateur actives les plus longues
  5. 2. Afficher l’instruction en cours d’exécution dans le lot
  6. 3. Distinguer les requêtes gourmandes en CPU de celles dominées par les attentes
  7. 4. Trouver les requêtes bloquées de longue durée
  8. 5. Trouver les requêtes de longue durée avec beaucoup de lectures logiques
  9. 6. Trouver les requêtes en cache historiquement lentes
  10. 7. Trouver les requêtes ayant le temps écoulé maximal le plus élevé
  11. 8. Regrouper les requêtes lentes par query hash
  12. 9. Utiliser Query Store pour une analyse historique durable des requêtes lentes
  13. 10. Comparer les plans d’une requête lente
  14. Interpréter le résultat
  15. Points à vérifier
  16. Temps écoulé élevé + CPU faible
  17. Temps écoulé élevé + CPU élevé
  18. Temps écoulé élevé + lectures logiques élevées
  19. blocking_session_id > 0
  20. Écart important entre durée minimale et maximale
  21. Query Store affiche la même requête avec plusieurs plans
  22. Notes de production
  23. Longue durée ne veut pas dire problème
  24. Les données actives et historiques proviennent de sources différentes
  25. L’historique du cache de plans est éphémère
  26. Les moyennes peuvent masquer les valeurs aberrantes
  27. Le temps écoulé inclut les attentes
  28. Les autorisations varient selon la version
  29. Erreurs fréquentes
  30. 1. Désigner la requête la plus longue comme « le problème »
  31. 2. Confondre temps écoulé et temps CPU
  32. 3. Ne regarder que les moyennes
  33. 4. Ignorer le nombre d’exécutions
  34. 5. Tuer une session de longue durée sans contexte
  35. 6. Supposer qu’une requête lente a toujours besoin d’un index
  36. 7. Utiliser les statistiques du cache de plans comme historique durable
  37. Étape suivante
  38. Pour aller plus loin

Problème

Les utilisateurs signalent qu’une requête, un rapport, un appel d’API ou un traitement par lots prend beaucoup plus de temps que prévu.

La première question n’est pas simplement :

Quelle requête s’exécute depuis le plus longtemps ?

Les questions utiles sont les suivantes :

  • La requête est-elle toujours en cours d’exécution ?
  • Consomme-t-elle activement du CPU ou est-elle surtout en attente ?
  • Est-elle bloquée ?
  • Effectue-t-elle un nombre excessif de lectures ?
  • Le temps d’exécution est-il anormal pour cette requête ?
  • Le plan a-t-il changé ?
  • S’agit-il d’une exécution lente isolée ou d’un comportement récurrent ?

Réponse rapide

Pour les requêtes qui s’exécutent en ce moment même, utilisez sys.dm_exec_requests.

SELECT TOP (50)
    r.session_id,
    DB_NAME(r.database_id) AS database_name,
    r.status,
    r.command,
    r.start_time,
    CAST(r.total_elapsed_time / 1000.0 AS decimal(18,1)) AS elapsed_seconds,
    r.cpu_time AS cpu_ms,
    r.logical_reads,
    r.reads,
    r.writes,
    r.wait_type,
    r.wait_time AS wait_ms,
    r.wait_resource,
    r.blocking_session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    txt.text AS batch_text
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_sessions AS s
    ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE
    r.session_id <> @@SPID
    AND s.is_user_process = 1
ORDER BY
    r.total_elapsed_time DESC;

Demandez-vous ensuite pourquoi la requête est lente :

  • CPU élevé → piste CPU / plan de requête
  • lectures élevées → piste du chemin d’accès aux données
  • blocking_session_id > 0 → piste des blocages
  • wait_type significatif → piste de l’analyse des attentes
  • plus rien d’actif → piste Query Store / historique

Code / commandes

1. Trouver les requêtes utilisateur actives les plus longues

SELECT TOP (50)
    r.session_id,
    DB_NAME(r.database_id) AS database_name,
    r.status,
    r.command,
    r.start_time,
    CAST(r.total_elapsed_time / 1000.0 AS decimal(18,1)) AS elapsed_seconds,
    r.cpu_time AS cpu_ms,
    r.logical_reads,
    r.reads,
    r.writes,
    r.wait_type,
    r.wait_time AS wait_ms,
    r.wait_resource,
    r.blocking_session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    txt.text AS batch_text
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_sessions AS s
    ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE
    r.session_id <> @@SPID
    AND s.is_user_process = 1
ORDER BY
    r.total_elapsed_time DESC;

C’est la requête de tri par défaut pour l’analyse en direct.

Ne considérez pas automatiquement la première ligne comme la cause racine.

Une requête peut durer longtemps parce qu’elle est :

  • bloquée
  • en attente d’E/S
  • en attente de CPU
  • en train de traiter volontairement une charge de travail importante
  • bloquée derrière une autre dépendance

2. Afficher l’instruction en cours d’exécution dans le lot

Une procédure stockée de longue durée peut contenir de nombreuses instructions.

Utilisez les offsets de la requête pour isoler l’instruction en cours.

SELECT
    r.session_id,
    r.start_time,
    CAST(r.total_elapsed_time / 1000.0 AS decimal(18,1)) AS elapsed_seconds,
    r.cpu_time AS cpu_ms,
    r.logical_reads,
    r.wait_type,
    r.blocking_session_id,
    CASE
        WHEN r.statement_start_offset IS NULL THEN txt.text
        ELSE SUBSTRING(
            txt.text,
            (r.statement_start_offset / 2) + 1,
            (
                (
                    CASE r.statement_end_offset
                        WHEN -1 THEN DATALENGTH(txt.text)
                        ELSE r.statement_end_offset
                    END
                    - r.statement_start_offset
                ) / 2
            ) + 1
        )
    END AS statement_text,
    txt.text AS batch_text
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE
    r.session_id <> @@SPID
ORDER BY
    r.total_elapsed_time DESC;

C’est généralement plus exploitable que d’examiner uniquement le texte complet de la procédure.


3. Distinguer les requêtes gourmandes en CPU de celles dominées par les attentes

SELECT
    r.session_id,
    r.total_elapsed_time AS elapsed_ms,
    r.cpu_time AS cpu_ms,
    r.wait_time AS wait_ms,
    r.wait_type,
    r.blocking_session_id,
    CAST(
        100.0 * r.cpu_time
        / NULLIF(r.total_elapsed_time, 0)
        AS decimal(6,2)
    ) AS cpu_to_elapsed_pct,
    txt.text AS batch_text
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE
    r.session_id <> @@SPID
ORDER BY
    r.total_elapsed_time DESC;

Utilisez ce ratio uniquement comme aide au tri.

Ne définissez pas de seuil strict.

Les requêtes parallèles peuvent consommer du CPU sur plusieurs workers, et le temps écoulé inclut les attentes.


4. Trouver les requêtes bloquées de longue durée

SELECT
    r.session_id AS blocked_session_id,
    r.blocking_session_id,
    DB_NAME(r.database_id) AS database_name,
    r.status,
    r.wait_type,
    r.wait_time AS wait_ms,
    r.wait_resource,
    CAST(r.total_elapsed_time / 1000.0 AS decimal(18,1)) AS elapsed_seconds,
    txt.text AS blocked_batch_text
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE
    r.blocking_session_id > 0
ORDER BY
    r.total_elapsed_time DESC;

Si une requête de longue durée est bloquée, traitez d’abord la piste du bloqueur.

La requête lente n’est peut-être que la victime.


5. Trouver les requêtes de longue durée avec beaucoup de lectures logiques

SELECT TOP (50)
    r.session_id,
    CAST(r.total_elapsed_time / 1000.0 AS decimal(18,1)) AS elapsed_seconds,
    r.logical_reads,
    r.reads AS physical_reads,
    r.cpu_time AS cpu_ms,
    r.wait_type,
    txt.text AS batch_text
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE
    r.session_id <> @@SPID
ORDER BY
    r.logical_reads DESC;

Cela permet d’identifier les requêtes lentes parce qu’elles accèdent à un grand nombre de pages.

Examinez ensuite le plan d’exécution.


6. Trouver les requêtes en cache historiquement lentes

sys.dm_exec_query_stats est utile pour les requêtes récemment terminées dont les plans sont encore en cache.

SELECT TOP (50)
    qs.execution_count,
    qs.creation_time,
    qs.last_execution_time,

    CAST(
        qs.total_elapsed_time * 1.0
        / NULLIF(qs.execution_count, 0)
        / 1000.0
        AS decimal(18,2)
    ) AS avg_elapsed_ms,

    CAST(qs.max_elapsed_time / 1000.0 AS decimal(18,2)) AS max_elapsed_ms,
    CAST(qs.min_elapsed_time / 1000.0 AS decimal(18,2)) AS min_elapsed_ms,

    CAST(
        qs.total_worker_time * 1.0
        / NULLIF(qs.execution_count, 0)
        / 1000.0
        AS decimal(18,2)
    ) AS avg_cpu_ms,

    qs.total_logical_reads,
    txt.text AS batch_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS txt
WHERE
    qs.execution_count > 0
ORDER BY
    avg_elapsed_ms DESC;

Utilisez cette requête lorsque la requête active a disparu mais que l’historique du cache de plans est encore disponible.


7. Trouver les requêtes ayant le temps écoulé maximal le plus élevé

Les moyennes peuvent masquer des exécutions lentes intermittentes.

SELECT TOP (50)
    qs.execution_count,
    CAST(qs.max_elapsed_time / 1000.0 AS decimal(18,2)) AS max_elapsed_ms,
    CAST(qs.min_elapsed_time / 1000.0 AS decimal(18,2)) AS min_elapsed_ms,
    CAST(
        qs.total_elapsed_time * 1.0
        / NULLIF(qs.execution_count, 0)
        / 1000.0
        AS decimal(18,2)
    ) AS avg_elapsed_ms,
    qs.last_execution_time,
    txt.text AS batch_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS txt
ORDER BY
    qs.max_elapsed_time DESC;

Un écart important entre la durée minimale et la durée maximale est un signal qui mérite d’être examiné.

Causes possibles :

  • sensibilité aux paramètres
  • blocages lors de certaines exécutions
  • changements de plan
  • évolution du volume de données
  • contention des ressources

8. Regrouper les requêtes lentes par query hash

Les différences de littéraux dans les requêtes ad hoc peuvent fragmenter les statistiques du cache de plans.

SELECT TOP (50)
    qs.query_hash,
    SUM(qs.execution_count) AS executions,

    CAST(
        SUM(qs.total_elapsed_time) * 1.0
        / NULLIF(SUM(qs.execution_count), 0)
        / 1000.0
        AS decimal(18,2)
    ) AS weighted_avg_elapsed_ms,

    CAST(
        MAX(qs.max_elapsed_time) / 1000.0
        AS decimal(18,2)
    ) AS max_elapsed_ms,

    MAX(qs.last_execution_time) AS last_execution_time
FROM sys.dm_exec_query_stats AS qs
GROUP BY
    qs.query_hash
ORDER BY
    weighted_avg_elapsed_ms DESC;

Utilisez cette requête pour agréger les formes de requêtes logiquement équivalentes.


9. Utiliser Query Store pour une analyse historique durable des requêtes lentes

Si Query Store est activé :

SELECT TOP (50)
    q.query_id,
    p.plan_id,
    qt.query_sql_text,
    SUM(rs.count_executions) AS executions,

    CAST(
        SUM(rs.avg_duration * rs.count_executions)
        / NULLIF(SUM(rs.count_executions), 0)
        / 1000.0
        AS decimal(18,2)
    ) AS weighted_avg_duration_ms,

    CAST(
        MAX(rs.max_duration) / 1000.0
        AS decimal(18,2)
    ) AS max_duration_ms,

    CAST(
        SUM(rs.avg_cpu_time * rs.count_executions)
        / NULLIF(SUM(rs.count_executions), 0)
        / 1000.0
        AS decimal(18,2)
    ) AS weighted_avg_cpu_ms
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON q.query_text_id = qt.query_text_id
INNER JOIN sys.query_store_plan AS p
    ON p.query_id = q.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON rs.plan_id = p.plan_id
GROUP BY
    q.query_id,
    p.plan_id,
    qt.query_sql_text
ORDER BY
    weighted_avg_duration_ms DESC;

Utilisez Query Store pour :

  • les requêtes lentes intermittentes
  • les régressions de plan
  • les analyses avant/après
  • l’historique après le renouvellement du cache de plans

10. Comparer les plans d’une requête lente

DECLARE @query_id bigint = 123;

SELECT
    p.plan_id,
    p.is_forced_plan,
    SUM(rs.count_executions) AS executions,

    CAST(
        SUM(rs.avg_duration * rs.count_executions)
        / NULLIF(SUM(rs.count_executions), 0)
        / 1000.0
        AS decimal(18,2)
    ) AS weighted_avg_duration_ms,

    CAST(
        SUM(rs.avg_cpu_time * rs.count_executions)
        / NULLIF(SUM(rs.count_executions), 0)
        / 1000.0
        AS decimal(18,2)
    ) AS weighted_avg_cpu_ms
FROM sys.query_store_plan AS p
INNER JOIN sys.query_store_runtime_stats AS rs
    ON rs.plan_id = p.plan_id
WHERE
    p.query_id = @query_id
GROUP BY
    p.plan_id,
    p.is_forced_plan
ORDER BY
    weighted_avg_duration_ms DESC;

Si un plan est systématiquement plus lent qu’un autre, recherchez une régression de plan plutôt que de considérer le texte de la requête seul comme le problème.

Interpréter le résultat

Champ Pourquoi c’est important
total_elapsed_time Temps réel écoulé pour la requête active. Inclut les attentes.
cpu_time CPU consommé par la requête active.
wait_time Durée de l’attente en cours pour la requête.
wait_type Catégorie de l’attente en cours. Souvent l’indice le plus rapide pour comprendre un temps écoulé élevé.
blocking_session_id Session qui bloque actuellement la requête, le cas échéant.
logical_reads Pages lues via le buffer pool. Des valeurs élevées peuvent indiquer un accès trop large.
avg_elapsed_ms Temps écoulé moyen des exécutions terminées présentes dans le cache.
max_elapsed_ms Exécution terminée la plus lente observée dans le cycle de vie actuel des statistiques.
min_elapsed_ms Exécution terminée la plus rapide observée dans le cycle de vie actuel des statistiques.
query_hash Utile pour regrouper les requêtes logiquement équivalentes.
plan_id Identifiant de plan Query Store pour comparer les régressions.

Points à vérifier

Temps écoulé élevé + CPU faible

Il s’agit souvent d’un problème d’attente, et non d’un problème de CPU.

Vérifiez :

  • les blocages
  • les attentes d’E/S
  • les allocations de mémoire (memory grants)
  • la consommation côté réseau/client
  • la disponibilité des workers

Temps écoulé élevé + CPU élevé

La requête est peut-être réellement coûteuse.

Examinez :

  • le plan d’exécution
  • les analyses (scans)
  • les jointures
  • les tris
  • les agrégations
  • les estimations de cardinalité
  • le comportement des paramètres

Temps écoulé élevé + lectures logiques élevées

La requête traite peut-être plus de pages que nécessaire.

Recherchez :

  • des analyses étendues
  • des prédicats peu efficaces
  • des jointures peu sélectives
  • des index manquants ou inefficaces
  • des jeux de résultats volumineux attendus

blocking_session_id > 0

Suivez le bloqueur.

N’optimisez pas d’abord la requête bloquée, sauf si elle pose aussi problème en elle-même.

Écart important entre durée minimale et maximale

Cela peut indiquer un comportement instable.

Causes possibles :

  • sensibilité aux paramètres
  • changements de plan
  • blocages irréguliers
  • évolution de la charge de travail
  • asymétrie des données (data skew)

Query Store affiche la même requête avec plusieurs plans

Comparez les plans selon le nombre d’exécutions, la durée moyenne, le CPU et les lectures.

Un plan plus lent qui apparaît après un déploiement ou après des modifications de statistiques ou d’index constitue un indice fort de régression.

Notes de production

Longue durée ne veut pas dire problème

Certaines opérations sont volontairement longues :

  • ETL
  • maintenance des index
  • sauvegardes
  • chargements de données
  • rapports volumineux
  • vérifications d’intégrité

La question est de savoir si la durée est attendue au regard de la charge de travail et du SLA.

Les données actives et historiques proviennent de sources différentes

Utilisez :

  • sys.dm_exec_requests → requêtes actives
  • sys.dm_exec_query_stats → statistiques des requêtes terminées présentes dans le cache
  • Query Store → historique persistant des métriques d’exécution et des plans

Ne les combinez pas comme si elles représentaient la même fenêtre d’observation.

L’historique du cache de plans est éphémère

Les lignes de sys.dm_exec_query_stats disparaissent lorsque le plan sort du cache.

Utilisez Query Store pour une analyse historique durable.

Les moyennes peuvent masquer les valeurs aberrantes

Une requête d’une durée moyenne de 100 ms peut tout de même prendre ponctuellement 30 secondes.

Examinez :

  • la durée maximale
  • la durée minimale
  • l’historique des plans
  • les données par fenêtre de temps

lorsque la latence intermittente compte.

Le temps écoulé inclut les attentes

Ne comparez pas le temps écoulé et le CPU comme s’il s’agissait de la même métrique.

Une requête peut passer l’essentiel de son temps à attendre.

Les autorisations varient selon la version

Pour un accès étendu aux DMV de performance :

  • SQL Server 2019 et versions antérieures exigent généralement VIEW SERVER STATE
  • SQL Server 2022 et versions ultérieures exigent généralement VIEW SERVER PERFORMANCE STATE

Azure SQL Database applique des règles de portée et d’autorisation différentes.

Erreurs fréquentes

1. Désigner la requête la plus longue comme « le problème »

La requête qui s’exécute depuis le plus longtemps attend peut-être simplement derrière une autre session.

Suivez d’abord les attentes et les blocages.

2. Confondre temps écoulé et temps CPU

Le temps écoulé inclut les attentes.

Une requête de 60 secondes peut n’avoir consommé que très peu de CPU.

3. Ne regarder que les moyennes

Les moyennes peuvent masquer des régressions ponctuelles ou propres à certains paramètres.

Examinez les valeurs min/max et l’historique de Query Store.

4. Ignorer le nombre d’exécutions

Une requête exécutée une fois par jour peut être lente mais avoir peu d’impact.

Une requête exécutée des milliers de fois peut générer une charge de travail cumulée plus importante.

5. Tuer une session de longue durée sans contexte

Avant d’y mettre fin, comprenez :

  • l’opération métier
  • l’état de la transaction
  • le coût du rollback
  • l’impact sur les blocages

6. Supposer qu’une requête lente a toujours besoin d’un index

La cause racine peut être :

  • un blocage
  • un mauvais plan
  • des statistiques obsolètes
  • une pression sur le CPU
  • une pression sur la mémoire
  • la lenteur du client
  • la latence du stockage

7. Utiliser les statistiques du cache de plans comme historique durable

Elles ne sont pas permanentes.

Utilisez Query Store ou une supervision externe pour disposer de preuves historiques.

Étape suivante

Utilisez le profil d’exécution pour choisir la piste d’investigation suivante.

  • Bloquée → trouvez le bloqueur principal et examinez les transactions ouvertes.
  • CPU élevé → examinez le plan d’exécution actuel ou précédent et la charge de travail gourmande en CPU.
  • Lectures élevées → examinez les analyses, les prédicats, les jointures et l’indexation.
  • Type d’attente significatif → suivez la catégorie d’attente.
  • Écart important entre durée minimale et maximale → étudiez la sensibilité aux paramètres et la stabilité des plans.
  • Plan modifié au moment de la dégradation des performances → comparez les plans dans Query Store.
  • Problème déjà disparu → utilisez Query Store et les données de supervision persistées.
  • Longue durée attendue → validez le SLA et l’impact sur les ressources avant d’optimiser.

← Tous les aide-mémoire