Parcourir les aide-mémoire · SQL Server
Trouver les requêtes gourmandes en CPU
Identifiez les requêtes SQL Server qui consomment le plus de CPU en ce moment et dans l’historique, distinguez le CPU total du CPU par exécution et choisissez la bonne étape d’optimisation suivante.
Sur cette page
- Problème
- Réponse rapide
- Code / commandes
- 1. Trouver les requêtes actives qui consomment le plus de CPU
- 2. Afficher l’instruction en cours d’exécution dans le lot
- 3. Trouver les requêtes en cache par CPU total
- 4. Trouver les requêtes avec le CPU moyen par exécution le plus élevé
- 5. Trouver les consommateurs de CPU à haute fréquence
- 6. Regrouper les requêtes logiquement équivalentes par query hash
- 7. Afficher le texte de requête et le plan d’exécution des requêtes en cache gourmandes en CPU
- 8. Comparer le temps CPU et la durée écoulée des requêtes actives
- 9. Vérifier si la pression CPU dépasse une seule requête
- 10. Utiliser Query Store pour l’historique du CPU élevé
- Interpréter le résultat
- Points à vérifier
- CPU total élevé + nombre d’exécutions élevé
- CPU moyen élevé + faible nombre d’exécutions
- CPU élevé + lectures logiques élevées
- Durée écoulée élevée + CPU faible
- Nombreuses requêtes ad hoc logiquement équivalentes
- CPU élevé + files runnable sur les schedulers
- Notes de production
- sys.dm_exec_query_stats repose sur le cache
- Les statistiques de requête sont mises à jour après la fin de l’exécution
- Les unités de CPU diffèrent selon les DMV
- Les requêtes parallèles compliquent l’interprétation
- Les moyennes en cache peuvent masquer des écarts
- Les autorisations varient selon la version
- Un CPU élevé peut être légitime
- Erreurs fréquentes
- 1. Trier uniquement par CPU moyen
- 2. Trier uniquement par CPU total
- 3. Confondre durée écoulée et temps CPU
- 4. Utiliser sys.dm_exec_query_stats pour les requêtes en cours d’exécution
- 5. Ignorer le cycle de vie du cache des plans
- 6. Supposer que des lectures logiques élevées signifient automatiquement un index manquant
- 7. Modifier MAXDOP parce que le CPU est élevé
- 8. Ignorer la fréquence des appels de l’application
- Étape suivante
- Pour aller plus loin
Problème
Le CPU de SQL Server est élevé, la latence de l’application augmente, ou vous devez identifier les requêtes responsables de la plus grande part du temps processeur.
L’objectif est de répondre aux questions suivantes :
- Quelles requêtes consomment du CPU en ce moment ?
- Quelles requêtes en cache ont consommé le plus de CPU au total ?
- Quelles requêtes sont coûteuses par exécution ?
- Une seule requête est-elle coûteuse, ou une requête très fréquente crée-t-elle une pression CPU cumulée ?
- Une durée écoulée élevée est-elle vraiment liée au CPU, ou la requête passe-t-elle surtout son temps en attente ?
Réponse rapide
Pour l’activité CPU actuelle, commencez par sys.dm_exec_requests.
SELECT TOP (20)
r.session_id,
DB_NAME(r.database_id) AS database_name,
r.status,
r.command,
r.cpu_time AS cpu_ms,
CAST(r.total_elapsed_time / 1000.0 AS decimal(18,1)) AS elapsed_seconds,
r.logical_reads,
r.wait_type,
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.cpu_time DESC;
Pour la charge de travail récemment terminée présente dans le cache des plans, utilisez sys.dm_exec_query_stats.
SELECT TOP (20)
qs.execution_count,
CAST(qs.total_worker_time / 1000.0 AS decimal(18,2)) AS total_cpu_ms,
CAST(
qs.total_worker_time * 1.0
/ NULLIF(qs.execution_count, 0)
/ 1000.0
AS decimal(18,2)
) AS avg_cpu_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.total_worker_time DESC;
Utilisez les deux vues ensemble :
- les données des requêtes actives vous indiquent ce qui se passe maintenant
- les statistiques de requêtes vous indiquent quelle charge de travail récemment terminée a consommé du CPU
Code / commandes
1. Trouver les requêtes actives qui consomment le plus de CPU
SELECT TOP (20)
r.session_id,
DB_NAME(r.database_id) AS database_name,
r.status,
r.command,
r.cpu_time AS cpu_ms,
r.total_elapsed_time AS elapsed_ms,
r.logical_reads,
r.reads,
r.writes,
r.wait_type,
r.blocking_session_id,
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.cpu_time DESC;
Utilisez cette requête pendant un incident en cours.
cpu_time correspond aux millisecondes de CPU utilisées par la requête en cours.
Une valeur élevée ne signifie pas automatiquement que la requête est la cause racine, mais c’est une piste sérieuse lorsque le CPU du serveur est également élevé.
2. Afficher l’instruction en cours d’exécution dans le lot
Un lot peut contenir de nombreuses instructions. Utilisez les offsets d’instruction pour isoler celle qui s’exécute actuellement.
SELECT TOP (20)
r.session_id,
r.cpu_time AS cpu_ms,
r.total_elapsed_time AS elapsed_ms,
r.logical_reads,
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.cpu_time DESC;
C’est généralement plus exploitable que d’examiner une procédure stockée entière ou un lot de plusieurs instructions.
3. Trouver les requêtes en cache par CPU total
Utilisez cette requête pour identifier la charge de travail qui a consommé le plus de CPU cumulé depuis la compilation du plan en cache.
SELECT TOP (50)
qs.execution_count,
qs.creation_time,
qs.last_execution_time,
CAST(qs.total_worker_time / 1000.0 AS decimal(18,2)) AS total_cpu_ms,
CAST(
qs.total_worker_time * 1.0
/ NULLIF(qs.execution_count, 0)
/ 1000.0
AS decimal(18,2)
) AS avg_cpu_ms,
CAST(qs.last_worker_time / 1000.0 AS decimal(18,2)) AS last_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
ORDER BY
qs.total_worker_time DESC;
C’est généralement la bonne requête lorsque la question est :
Quelle charge de travail en cache a consommé le plus de CPU au total ?
4. Trouver les requêtes avec le CPU moyen par exécution le plus élevé
SELECT TOP (50)
qs.execution_count,
CAST(
qs.total_worker_time * 1.0
/ NULLIF(qs.execution_count, 0)
/ 1000.0
AS decimal(18,2)
) AS avg_cpu_ms,
CAST(qs.total_worker_time / 1000.0 AS decimal(18,2)) AS total_cpu_ms,
CAST(qs.max_worker_time / 1000.0 AS decimal(18,2)) AS max_cpu_ms,
CAST(qs.min_worker_time / 1000.0 AS decimal(18,2)) AS min_cpu_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
WHERE
qs.execution_count > 0
ORDER BY
avg_cpu_ms DESC;
Utilisez cette requête pour trouver les exécutions individuellement coûteuses.
Examinez toujours execution_count à côté de la moyenne.
Une exécution unique de 20 secondes de CPU peut compter moins qu’une requête de 20 ms exécutée un million de fois.
5. Trouver les consommateurs de CPU à haute fréquence
SELECT TOP (50)
qs.execution_count,
CAST(qs.total_worker_time / 1000.0 AS decimal(18,2)) AS total_cpu_ms,
CAST(
qs.total_worker_time * 1.0
/ NULLIF(qs.execution_count, 0)
/ 1000.0
AS decimal(18,2)
) AS avg_cpu_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
WHERE
qs.execution_count >= 100
ORDER BY
qs.total_worker_time DESC;
La valeur 100 n’est qu’un exemple de filtre pour réduire le résultat.
Ce n’est pas un seuil universel.
Ajustez-la à la charge de travail.
6. Regrouper les requêtes logiquement équivalentes par query hash
Les littéraux des requêtes ad hoc peuvent créer plusieurs entrées de cache pour des instructions logiquement équivalentes.
Regrouper par query_hash peut révéler plus clairement le CPU cumulé.
SELECT TOP (50)
qs.query_hash,
SUM(qs.execution_count) AS executions,
CAST(
SUM(qs.total_worker_time) / 1000.0
AS decimal(18,2)
) AS total_cpu_ms,
CAST(
SUM(qs.total_worker_time) * 1.0
/ NULLIF(SUM(qs.execution_count), 0)
/ 1000.0
AS decimal(18,2)
) AS avg_cpu_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
total_cpu_ms DESC;
Utilisez cette approche lorsque des différences de littéraux donnent l’impression d’un cache fragmenté.
Examinez ensuite en détail le texte de requête correspondant au hash concerné.
7. Afficher le texte de requête et le plan d’exécution des requêtes en cache gourmandes en CPU
SELECT TOP (20)
qs.execution_count,
CAST(qs.total_worker_time / 1000.0 AS decimal(18,2)) AS total_cpu_ms,
CAST(
qs.total_worker_time * 1.0
/ NULLIF(qs.execution_count, 0)
/ 1000.0
AS decimal(18,2)
) AS avg_cpu_ms,
txt.text AS batch_text,
qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS txt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY
qs.total_worker_time DESC;
Utilisez le plan pour examiner :
- les scans volumineux
- les jointures coûteuses
- les tris
- les opérations de hachage
- les mauvaises estimations
- les lookups répétés
- le parallélisme
- les débordements (spills) ou les indicateurs de pression mémoire
N’optimisez pas un plan en vous fondant sur un seul opérateur.
8. Comparer le temps CPU et la durée écoulée des requêtes actives
SELECT
r.session_id,
r.cpu_time AS cpu_ms,
r.total_elapsed_time AS elapsed_ms,
CAST(
100.0 * r.cpu_time
/ NULLIF(r.total_elapsed_time, 0)
AS decimal(6,2)
) AS cpu_to_elapsed_pct,
r.status,
r.wait_type,
r.blocking_session_id,
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.cpu_time DESC;
Ce ratio n’est qu’un signal de tri approximatif.
La durée écoulée inclut les attentes, et le travail parallèle peut compliquer l’interprétation.
Utilisez-le pour vous demander :
- La requête effectue-t-elle surtout du travail CPU ?
- Ou est-elle surtout en attente ?
N’utilisez pas ce pourcentage comme un seuil de classification strict.
9. Vérifier si la pression CPU dépasse une seule requête
Les requêtes gourmandes en CPU comptent davantage lorsque le planificateur (scheduler) est sous pression.
SELECT
scheduler_id,
cpu_id,
status,
is_online,
current_tasks_count,
runnable_tasks_count,
active_workers_count,
current_workers_count,
load_factor
FROM sys.dm_os_schedulers
WHERE
status = N'VISIBLE ONLINE'
ORDER BY
scheduler_id;
runnable_tasks_count représente les workers prêts à s’exécuter mais qui attendent le CPU du scheduler.
Des files runnable non nulles de manière répétée pendant un épisode de CPU élevé peuvent étayer un diagnostic de pression CPU.
N’utilisez pas un échantillon instantané unique comme seuil universel.
10. Utiliser Query Store pour l’historique du CPU élevé
Si la requête coûteuse est déjà terminée ou si les données du cache des plans sont insuffisantes, Query Store est généralement la meilleure source historique.
SELECT TOP (20)
q.query_id,
p.plan_id,
qt.query_sql_text,
SUM(rs.count_executions) AS executions,
CAST(
SUM(rs.avg_cpu_time * rs.count_executions)
/ 1000.0
AS decimal(18,2)
) AS total_cpu_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
total_cpu_ms DESC;
C’est la meilleure approche pour :
- les régressions historiques
- la comparaison de plans
- l’analyse avant/après de la charge de travail
- les requêtes qui ne sont plus présentes dans le cache des plans
Interpréter le résultat
| Champ | Pourquoi c’est important |
|---|---|
cpu_time |
Millisecondes de CPU consommées par une requête active. |
total_worker_time |
Temps CPU total cumulé par les exécutions terminées représentées par la ligne de statistiques de requête en cache. |
last_worker_time |
CPU consommé par la dernière exécution terminée représentée par la ligne de statistiques. |
min_worker_time |
Temps CPU le plus faible observé pour une exécution au cours du cycle de vie actuel du cache. |
max_worker_time |
Temps CPU le plus élevé observé pour une exécution au cours du cycle de vie actuel du cache. |
execution_count |
Nombre d’exécutions terminées représentées par la ligne de statistiques en cache. |
avg_cpu_ms |
CPU par exécution, calculé. Utile uniquement avec le nombre d’exécutions et le contexte de la charge de travail. |
query_hash |
Hash qui peut aider à regrouper les requêtes logiquement équivalentes. |
total_elapsed_time |
Durée écoulée réelle (wall-clock), attentes comprises. |
runnable_tasks_count |
Workers du scheduler prêts à s’exécuter mais en attente de CPU. |
Points à vérifier
CPU total élevé + nombre d’exécutions élevé
Cela signifie souvent qu’une requête « petite mais fréquente » consomme une part importante du CPU.
Étapes suivantes possibles :
- réduire la fréquence d’exécution
- traiter le travail par lots
- mettre les résultats en cache
- améliorer le chemin d’accès aux données
- réduire les appels répétés de l’application
CPU moyen élevé + faible nombre d’exécutions
Cela indique généralement une opération coûteuse en elle-même.
Examinez :
- le plan d’exécution
- les scans
- les jointures
- les tris
- les agrégations
- les estimations de cardinalité
- le comportement des paramètres
CPU élevé + lectures logiques élevées
Cela indique souvent un accès aux données inefficace.
Causes possibles :
- scans volumineux
- prédicats peu sélectifs
- mauvaise stratégie de jointure
- index manquants ou inefficaces
- mauvaises estimations
Ne supposez pas que la solution est un index tant que le plan n’étaye pas cette conclusion.
Durée écoulée élevée + CPU faible
La requête n’est peut-être pas limitée par le CPU.
Examinez :
- les attentes
- les blocages
- les E/S
- les allocations de mémoire (memory grants)
- la consommation côté client/réseau
Nombreuses requêtes ad hoc logiquement équivalentes
Regrouper par query_hash peut révéler que la charge de travail est plus importante que ne le laisse penser une seule ligne du cache.
Cela peut indiquer des problèmes de paramétrage ou de forme des requêtes générées par l’application.
CPU élevé + files runnable sur les schedulers
Cela renforce l’hypothèse d’une réelle pression CPU.
Déterminez ensuite si la pression provient :
- d’une seule requête
- de nombreuses requêtes concurrentes
- du parallélisme
- de la compilation
- de la croissance du trafic applicatif
Notes de production
sys.dm_exec_query_stats repose sur le cache
Cette DMV contient les statistiques des plans de requête en cache.
Lorsque le plan quitte le cache, la ligne disparaît.
La DMV est donc utile pour analyser la charge de travail récente, mais ce n’est pas une télémétrie historique durable.
Les statistiques de requête sont mises à jour après la fin de l’exécution
sys.dm_exec_query_stats reflète les exécutions de requêtes terminées.
Pour une requête encore en cours d’exécution, utilisez sys.dm_exec_requests.
Les unités de CPU diffèrent selon les DMV
sys.dm_exec_requests.cpu_time est exprimé en millisecondes.
sys.dm_exec_query_stats est exprimé en microsecondes, bien que la précision sous-jacente ne soit que de l’ordre de la milliseconde.
Convertissez les unités avant de comparer les valeurs.
Les requêtes parallèles compliquent l’interprétation
Le temps CPU peut dépasser la durée écoulée réelle lorsque le travail est réparti en parallèle sur plusieurs workers.
Ne considérez pas cpu_time > elapsed_time comme une erreur.
Les moyennes en cache peuvent masquer des écarts
Une requête peut être peu coûteuse pour la plupart des valeurs de paramètres et très coûteuse pour quelques-unes.
Utilisez :
min_worker_timemax_worker_time- Query Store
- l’historique des plans
- une analyse de la sensibilité aux paramètres
lorsque la distribution compte.
Les autorisations varient selon la version
Pour un accès étendu à ces DMV de performance :
- SQL Server 2019 et les versions antérieures exigent généralement
VIEW SERVER STATE - SQL Server 2022 et les versions ultérieures exigent
VIEW SERVER PERFORMANCE STATE
Azure SQL Database applique des règles de visibilité et d’autorisation différentes.
Un CPU élevé peut être légitime
Une requête de reporting ou un traitement par lots peut consommer intentionnellement beaucoup de CPU.
La question n’est pas seulement :
Le CPU est-il élevé ?
Demandez-vous aussi :
L’utilisation du CPU est-elle attendue, efficace et conforme au SLA de la charge de travail ?
Erreurs fréquentes
1. Trier uniquement par CPU moyen
Une requête coûteuse exécutée une seule fois peut dominer les moyennes tout en contribuant peu à la charge totale.
Comparez toujours le CPU moyen et le CPU total.
2. Trier uniquement par CPU total
Une requête à haute fréquence peut dominer le CPU total tout en restant efficace individuellement.
Parfois, la meilleure solution consiste à réduire le volume d’appels, pas à réécrire le SQL.
3. Confondre durée écoulée et temps CPU
La durée écoulée inclut les attentes.
Analysez séparément cpu_time, worker_time et les attentes.
4. Utiliser sys.dm_exec_query_stats pour les requêtes en cours d’exécution
Ses statistiques sont mises à jour à la fin de l’exécution.
Utilisez sys.dm_exec_requests pour le travail en cours.
5. Ignorer le cycle de vie du cache des plans
Un redémarrage récent, une recompilation ou l’éviction d’un plan raccourcit la fenêtre d’observation.
Ne comparez pas les totaux du cache sans contexte.
6. Supposer que des lectures logiques élevées signifient automatiquement un index manquant
Les lectures sont un indice du travail effectué, pas un diagnostic.
Vérifiez le plan d’exécution réel et la charge de travail.
7. Modifier MAXDOP parce que le CPU est élevé
Le parallélisme aide peut-être la requête à se terminer plus vite.
Examinez les plans, la pression sur les schedulers, la concurrence et la charge de travail avant de modifier des paramètres au niveau de l’instance.
8. Ignorer la fréquence des appels de l’application
Une requête peut être tout à fait raisonnable isolément, mais devenir coûteuse lorsqu’elle est appelée des milliers de fois par seconde.
Le diagnostic CPU doit tenir compte du volume de la charge de travail.
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.