DBAtlas · Atlas d'exploitation des bases de données

Trouver les requêtes les plus coûteuses dans le plan cache (CPU, lectures, durée)

SQL ServerPerformances

Commande

SELECT TOP (20)
       qs.query_hash,
       SUM(qs.execution_count)                                       AS execs,
       SUM(qs.total_worker_time) / 1000                              AS total_cpu_ms,
       SUM(qs.total_worker_time) / SUM(qs.execution_count) / 1000    AS avg_cpu_ms,
       SUM(qs.total_elapsed_time) / SUM(qs.execution_count) / 1000   AS avg_ms,
       SUM(qs.total_logical_reads) / SUM(qs.execution_count)         AS avg_reads,
       SUM(qs.total_grant_kb) / SUM(qs.execution_count)              AS avg_grant_kb,
       COUNT(DISTINCT qs.query_plan_hash)                            AS plan_count,
       MAX(qs.last_execution_time)                                   AS last_exec,
       MIN(SUBSTRING(st.text, qs.statement_start_offset / 2 + 1,
           ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
             ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) + 1)) AS statement_text,
       MIN(DB_NAME(pa.dbid))                                         AS db
FROM   sys.dm_exec_query_stats qs
  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
  OUTER APPLY (SELECT CONVERT(int, value) AS dbid FROM sys.dm_exec_plan_attributes(qs.plan_handle)
               WHERE attribute = N'dbid') pa
GROUP BY qs.query_hash
ORDER BY total_cpu_ms DESC;

-- To rank by reads: ORDER BY SUM(qs.total_logical_reads) DESC
-- Plan: SELECT query_plan FROM sys.dm_exec_query_plan(<plan_handle>);

Ce que fait la commande

Regroupe les instructions du plan cache par query_hash (les requêtes ad hoc qui ne diffèrent que par les littéraux se réduisent à une ligne) et renvoie le CPU, la durée, les lectures logiques, le memory grant totaux et moyens ainsi que le nombre de plans.

Quand l'utiliser

Quand le CPU est saturé, sur une plainte "le serveur est lent", pour les bases sans Query Store, ou quand il faut une vue globale du serveur sur toutes les bases.

Attention

Ne voit que les plans encore en cache : les requêtes dont les plans ont été évincés par un redémarrage, DBCC FREEPROCCACHE, la pression mémoire ou RECOMPILE sont absentes. Utilisez Query Store pour une analyse persistante par fenêtre temporelle. plan_count > 1 suggère une sensibilité aux paramètres ou des options SET différentes. Sur un très grand plan cache, la requête peut prendre quelques secondes ; sûre.

Ouvrir dans DBAtlas →

Voir aussi