튜닝 대상 SQL 찾기

SQL Server 튜닝 대상 SQL 찾기

• 현재 실행되거나 캐시에 있는 SQL을 분석하려면:

1. sys.dm_exec_query_stats
2. sys.dm_exec_sql_text
3. sys.dm_exec_query_plan

CPU를 많이 사용하는 SQL Top 20

SELECT TOP 20
       qs.execution_count,
       qs.total_worker_time / 1000 AS total_cpu_ms,
       qs.total_elapsed_time / 1000 AS total_elapsed_ms,
       qs.total_logical_reads,
       qs.total_physical_reads,
       qs.total_logical_writes,
       qs.last_execution_time,
       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 sql_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.total_worker_time DESC;

Logical Reads 기준으로 Top SQL 찾기

• I/O 튜닝 목적이라면:

SELECT TOP 20
       qs.execution_count,
       qs.total_logical_reads,
       qs.total_logical_reads / NULLIF(qs.execution_count, 0)
           AS avg_logical_reads,
       qs.total_physical_reads,
       qs.total_worker_time / 1000
           AS total_cpu_ms,
       qs.total_elapsed_time / 1000
           AS total_elapsed_ms,
       qs.last_execution_time,
       st.text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.total_logical_reads DESC;

• 이렇게 하면:
  • 실행 횟수
  • 총 Logical Reads
  • 평균 Logical Reads
  • Physical Reads
  • CPU
  • Elapsed

를 한꺼번에 확인할 수 있습니다.