Parcourir les aide-mémoire · SQL Server
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
- Problème
- Réponse rapide
- Code / commandes
- 1. Trouver les requêtes utilisateur actives les plus longues
- 2. Afficher l’instruction en cours d’exécution dans le lot
- 3. Distinguer les requêtes gourmandes en CPU de celles dominées par les attentes
- 4. Trouver les requêtes bloquées de longue durée
- 5. Trouver les requêtes de longue durée avec beaucoup de lectures logiques
- 6. Trouver les requêtes en cache historiquement lentes
- 7. Trouver les requêtes ayant le temps écoulé maximal le plus élevé
- 8. Regrouper les requêtes lentes par query hash
- 9. Utiliser Query Store pour une analyse historique durable des requêtes lentes
- 10. Comparer les plans d’une requête lente
- Interpréter le résultat
- Points à vérifier
- Temps écoulé élevé + CPU faible
- Temps écoulé élevé + CPU élevé
- Temps écoulé élevé + lectures logiques élevées
- blocking_session_id > 0
- Écart important entre durée minimale et maximale
- Query Store affiche la même requête avec plusieurs plans
- Notes de production
- Longue durée ne veut pas dire problème
- Les données actives et historiques proviennent de sources différentes
- L’historique du cache de plans est éphémère
- Les moyennes peuvent masquer les valeurs aberrantes
- Le temps écoulé inclut les attentes
- Les autorisations varient selon la version
- Erreurs fréquentes
- 1. Désigner la requête la plus longue comme « le problème »
- 2. Confondre temps écoulé et temps CPU
- 3. Ne regarder que les moyennes
- 4. Ignorer le nombre d’exécutions
- 5. Tuer une session de longue durée sans contexte
- 6. Supposer qu’une requête lente a toujours besoin d’un index
- 7. Utiliser les statistiques du cache de plans comme historique durable
- Étape suivante
- 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 blocageswait_typesignificatif → 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 activessys.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.
Ce contenu vous a-t-il été utile ?
Que pourrions-nous améliorer ?
Merci — cela m’aide à améliorer Javi Builds.
Restez au courant
Recevez uniquement les nouvelles de Javi Builds qui vous intéressent.
Consultez votre boîte de réception
Je vous ai envoyé un lien de confirmation. Votre abonnement sera actif après confirmation.