plan cache에서 가장 비용이 큰 쿼리 찾기 (CPU, 읽기, 소요 시간)

명령어

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>);

하는 일

plan cache의 문장을 query_hash로 그룹화하고(리터럴만 다른 ad-hoc 쿼리는 한 행으로 합쳐짐) 총/평균 CPU, 소요 시간, 논리 읽기, memory grant, 실행 계획 수를 반환한다.

사용 시점

CPU가 포화될 때, "서버가 느리다"는 불만이 있을 때, Query Store가 없는 데이터베이스, 또는 모든 데이터베이스에 걸친 서버 전체 관점이 필요할 때.

주의

캐시에 남아 있는 실행 계획만 보인다: 재시작, DBCC FREEPROCCACHE, 메모리 압박, RECOMPILE로 계획이 제거된 쿼리는 누락된다. 지속적이고 시간 구간별 분석에는 Query Store를 사용한다. plan_count > 1은 파라미터 민감도 또는 다른 SET 옵션을 시사한다. plan cache가 매우 크면 쿼리에 몇 초가 걸릴 수 있다. 안전함.