Parcourir les aide-mémoire · SQL Server

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

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
  1. Problème
  2. Réponse rapide
  3. Code / commandes
  4. 1. Trouver les requêtes actives qui consomment le plus de CPU
  5. 2. Afficher l’instruction en cours d’exécution dans le lot
  6. 3. Trouver les requêtes en cache par CPU total
  7. 4. Trouver les requêtes avec le CPU moyen par exécution le plus élevé
  8. 5. Trouver les consommateurs de CPU à haute fréquence
  9. 6. Regrouper les requêtes logiquement équivalentes par query hash
  10. 7. Afficher le texte de requête et le plan d’exécution des requêtes en cache gourmandes en CPU
  11. 8. Comparer le temps CPU et la durée écoulée des requêtes actives
  12. 9. Vérifier si la pression CPU dépasse une seule requête
  13. 10. Utiliser Query Store pour l’historique du CPU élevé
  14. Interpréter le résultat
  15. Points à vérifier
  16. CPU total élevé + nombre d’exécutions élevé
  17. CPU moyen élevé + faible nombre d’exécutions
  18. CPU élevé + lectures logiques élevées
  19. Durée écoulée élevée + CPU faible
  20. Nombreuses requêtes ad hoc logiquement équivalentes
  21. CPU élevé + files runnable sur les schedulers
  22. Notes de production
  23. sys.dm_exec_query_stats repose sur le cache
  24. Les statistiques de requête sont mises à jour après la fin de l’exécution
  25. Les unités de CPU diffèrent selon les DMV
  26. Les requêtes parallèles compliquent l’interprétation
  27. Les moyennes en cache peuvent masquer des écarts
  28. Les autorisations varient selon la version
  29. Un CPU élevé peut être légitime
  30. Erreurs fréquentes
  31. 1. Trier uniquement par CPU moyen
  32. 2. Trier uniquement par CPU total
  33. 3. Confondre durée écoulée et temps CPU
  34. 4. Utiliser sys.dm_exec_query_stats pour les requêtes en cours d’exécution
  35. 5. Ignorer le cycle de vie du cache des plans
  36. 6. Supposer que des lectures logiques élevées signifient automatiquement un index manquant
  37. 7. Modifier MAXDOP parce que le CPU est élevé
  38. 8. Ignorer la fréquence des appels de l’application
  39. Étape suivante
  40. 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.total_worker_time 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_time
  • max_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.

Étape suivante

Utilisez le profil CPU pour choisir la prochaine investigation.

  • CPU actif élevé sur une requête → examinez son instruction en cours et son plan d’exécution.
  • CPU total élevé + nombre d’exécutions élevé → examinez la fréquence des appels et optimisez le chemin critique.
  • CPU moyen élevé + faible nombre d’exécutions → examinez le plan de la requête concernée et son mode d’accès aux données.
  • CPU élevé + lectures élevées → analysez les scans, les jointures, les estimations et l’indexation.
  • Durée écoulée élevée + CPU faible → passez à l’analyse des attentes et des blocages.
  • Nombreuses variantes ad hoc → regroupez par query_hash et examinez le paramétrage et la génération des requêtes.
  • La DMV en direct ne montre plus le problème → utilisez Query Store.
  • Les files runnable des schedulers restent élevées → analysez la pression CPU et la concurrence à l’échelle du système.

← Tous les aide-mémoire