튜닝 대상 SQL 찾기

SQL Server 튜닝 대상 SQL 찾기

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

sys.dm_exec_query_stats
sys.dm_exec_sql_text
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

네. **“지금 현재 SQL Server에서 실제로 튜닝해야 할 SQL”**을 찾는 목적이라면, 앞에서 본 `sys.dm_exec_query_stats`만 보는 것보다 **현재 실행 중인 SQL + 최근 누적 부하가 큰 SQL + 대기(WAIT)가 큰 SQL**을 같이 봐야 합니다.

Oracle DBA 관점에서는 다음 3개로 나누면 이해가 쉽습니다.

① 지금 실행 중이며 오래 걸리는 SQL
② 최근 누적 CPU / I/O가 가장 큰 SQL
③ 현재 대기(WAIT)가 발생하고 있는 SQL

현재 실행 중인 SQL 중 오래 걸리는 SQL

Oracle의 `V$SESSION`, `V$SQL`을 보는 느낌으로 사용할 수 있습니다.

SELECT
    r.session_id,
    r.status,
    r.command,

    DB_NAME(r.database_id) AS database_name,

    s.login_name,
    s.host_name,
    s.program_name,

    r.start_time,

    DATEDIFF(SECOND, r.start_time, GETDATE()) AS elapsed_sec,

    r.cpu_time,

    r.logical_reads,
    r.reads,
    r.writes,

    r.wait_type,
    r.wait_time,
    r.last_wait_type,
    r.wait_resource,

    r.blocking_session_id,

    SUBSTRING(
        st.text,
        (r.statement_start_offset / 2) + 1,
        (
            (
                CASE r.statement_end_offset
                    WHEN -1 THEN DATALENGTH(st.text)
                    ELSE r.statement_end_offset
                END
                - r.statement_start_offset
            ) / 2
        ) + 1
    ) AS sql_text

FROM sys.dm_exec_requests r

JOIN sys.dm_exec_sessions s
    ON r.session_id = s.session_id

CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st

WHERE r.session_id <> @@SPID

ORDER BY
    elapsed_sec DESC;

이게 **현재 시점 튜닝 대상**을 찾는 데 가장 먼저 사용할 쿼리입니다.

예를 들어:

SPID   elapsed  CPU    reads    wait_type       blocker
-----  -------  -----  -------  --------------  -------
125    1850 sec 15000  8500000  PAGEIOLATCH_SH  NULL
132     620 sec 54000  1200000  CXPACKET        NULL
145     350 sec   200  5000000  LCK_M_X          100

라면 각각 성격이 다릅니다.

1. 1. 125번

reads = 8,500,000
PAGEIOLATCH_SH

이면 **I/O 부담이 큰 SQL**일 가능성이 높습니다.

1. 1. 132번

CPU = 54,000ms
CXPACKET

이면 병렬 처리와 실행계획을 같이 봐야 합니다.

1. 1. 145번

LCK_M_X
blocking_session_id = 100

이면 SQL 자체 튜닝 이전에 **Blocking 원인**부터 봐야 합니다.

현재 실행 중인 SQL을 "튜닝 우선순위"로 보기

저는 실무에서는 아래처럼 **Elapsed + CPU + Reads + Blocking + Wait**를 한 번에 봅니다.

SELECT
    r.session_id AS spid,

    DB_NAME(r.database_id) AS database_name,

    s.login_name,
    s.host_name,
    s.program_name,

    r.start_time,

    DATEDIFF(SECOND, r.start_time, GETDATE()) AS elapsed_sec,

    r.cpu_time AS cpu_ms,

    r.logical_reads,
    r.reads AS physical_reads,
    r.writes,

    r.wait_type,
    r.wait_time,

    r.blocking_session_id,

    CASE
        WHEN r.blocking_session_id <> 0
            THEN 'BLOCKING'
        WHEN r.wait_type IS NOT NULL
            THEN 'WAIT'
        WHEN r.logical_reads >= 1000000
            THEN 'HIGH LOGICAL READ'
        WHEN r.cpu_time >= 10000
            THEN 'HIGH CPU'
        WHEN DATEDIFF(SECOND, r.start_time, GETDATE()) >= 60
            THEN 'LONG RUNNING'
        ELSE 'NORMAL'
    END AS tuning_reason,

    SUBSTRING(
        st.text,
        (r.statement_start_offset / 2) + 1,
        (
            (
                CASE r.statement_end_offset
                    WHEN -1 THEN DATALENGTH(st.text)
                    ELSE r.statement_end_offset
                END
                - r.statement_start_offset
            ) / 2
        ) + 1
    ) AS sql_text

FROM sys.dm_exec_requests r

JOIN sys.dm_exec_sessions s
    ON r.session_id = s.session_id

CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st

WHERE r.session_id <> @@SPID

ORDER BY
    CASE
        WHEN r.blocking_session_id <> 0 THEN 1
        WHEN r.wait_type IS NOT NULL THEN 2
        WHEN r.logical_reads >= 1000000 THEN 3
        WHEN r.cpu_time >= 10000 THEN 4
        ELSE 5
    END,
    elapsed_sec DESC;

이렇게 하면 현재 실행중인 SQL 중에서 **뭘 먼저 볼지** 판단하기가 편합니다.

현재 실행 중인 SQL만 말고 "최근에 가장 많이 문제를 일으킨 SQL"

이게 실제 운영 DBA에서는 더 중요합니다.

`sys.dm_exec_query_stats`를 이용합니다.

1. Logical Reads Top

SELECT TOP (30)

    DB_NAME(st.dbid) AS database_name,

    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_physical_reads / NULLIF(qs.execution_count, 0)
        AS avg_physical_reads,

    qs.total_worker_time / 1000
        AS total_cpu_ms,

    qs.total_worker_time / NULLIF(qs.execution_count, 0) / 1000
        AS avg_cpu_ms,

    qs.total_elapsed_time / 1000
        AS total_elapsed_ms,

    qs.total_elapsed_time
        / NULLIF(qs.execution_count, 0) / 1000
        AS avg_elapsed_ms,

    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_logical_reads DESC;

이걸 보면:

SQL A
execution_count     2
avg_logical_reads   850000

같은 SQL을 발견할 수 있습니다.

이런 SQL은 **한 건 실행해도 DB를 심하게 압박**할 수 있습니다.

CPU 많이 사용하는 SQL

SELECT TOP (30)

    DB_NAME(st.dbid) AS database_name,

    qs.execution_count,

    qs.total_worker_time / 1000
        AS total_cpu_ms,

    qs.total_worker_time
        / NULLIF(qs.execution_count, 0) / 1000
        AS avg_cpu_ms,

    qs.total_logical_reads,
    qs.total_physical_reads,

    qs.total_elapsed_time / 1000
        AS total_elapsed_ms,

    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;

Oracle의:

V$SQL
ORDER BY CPU_TIME DESC

같은 용도로 생각하시면 됩니다.

실행 시간이 긴 SQL

SELECT TOP (30)

    DB_NAME(st.dbid) AS database_name,

    qs.execution_count,

    qs.total_elapsed_time / 1000
        AS total_elapsed_ms,

    qs.total_elapsed_time
        / NULLIF(qs.execution_count, 0) / 1000
        AS avg_elapsed_ms,

    qs.total_worker_time / 1000
        AS total_cpu_ms,

    qs.total_logical_reads,

    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_elapsed_time DESC;

6. 그런데 가장 중요한 것은 "평균"입니다

예를 들어:

SQL A
실행횟수 = 1,000,000
평균 Logical Reads = 100

총:

100,000,000

입니다.

반면:

SQL B
실행횟수 = 2
평균 Logical Reads = 10,000,000

총:

20,000,000

입니다.

따라서:

총부하가 큰 SQL

을 찾을 때와

한 번 실행할 때 엄청 느린 SQL

을 찾을 때 기준이 다릅니다.

그래서 저는 다음 4개를 모두 봅니다.

Total CPU
Average CPU

Total Logical Reads
Average Logical Reads

. Blocking SQL도 반드시 확인

현재 SQL이 느린 이유가 SQL 자체가 아니라 **다른 세션에게 막혀 있기 때문**일 수 있습니다.

SELECT
    r.session_id AS blocked_spid,
    r.blocking_session_id AS blocking_spid,

    DB_NAME(r.database_id) AS database_name,

    s.login_name,
    s.host_name,
    s.program_name,

    r.wait_type,
    r.wait_time,
    r.wait_resource,

    DATEDIFF(SECOND, r.start_time, GETDATE())
        AS elapsed_sec,

    r.logical_reads,
    r.cpu_time,

    SUBSTRING(
        st.text,
        (r.statement_start_offset / 2) + 1,
        (
            (
                CASE r.statement_end_offset
                    WHEN -1 THEN DATALENGTH(st.text)
                    ELSE r.statement_end_offset
                END
                - r.statement_start_offset
            ) / 2
        ) + 1
    ) AS sql_text

FROM sys.dm_exec_requests r

JOIN sys.dm_exec_sessions s
    ON r.session_id = s.session_id

CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st

WHERE r.blocking_session_id <> 0

ORDER BY
    r.wait_time DESC;

이 결과에서:

blocked_spid = 150
blocking_spid = 100

이면 **150번 SQL만 튜닝하면 안 됩니다.**

100번 세션이 왜 락을 오래 잡고 있는지를 먼저 확인해야 합니다.

실제 튜닝할 SQL을 골랐다면 다음 단계

예를 들어 위 쿼리에서:

SPID 125
Logical Reads 8,500,000
CPU 15,000ms
Elapsed 1,850sec

가 나왔다고 하겠습니다.

그러면 바로:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

그리고 SSMS에서:

Ctrl + M

켜고 해당 SQL을 실행해서:

Actual Execution Plan

을 봅니다.

즉 실제 작업 흐름은:

현재 문제 SQL 찾기
        ↓
SPID / SQL Text
        ↓
Logical Reads / CPU / Wait
        ↓
Actual Execution Plan
        ↓
Estimated Rows vs Actual Rows
        ↓
Scan / Seek
        ↓
Key Lookup
        ↓
Join
        ↓
Sort / Hash
        ↓
Index / Statistics

입니다.

Oracle DBA라면 이 정도만 기억하셔도 됩니다

SQL Server에서 **현재 튜닝 대상을 찾는 첫 번째 SQL**은 사실 아래 쿼리 하나부터 시작해도 됩니다.

SELECT
    r.session_id,
    DB_NAME(r.database_id) AS db_name,

    DATEDIFF(SECOND, r.start_time, GETDATE()) AS elapsed_sec,

    r.cpu_time,
    r.logical_reads,
    r.reads,
    r.writes,

    r.wait_type,
    r.wait_time,

    r.blocking_session_id,

    SUBSTRING(
        st.text,
        (r.statement_start_offset / 2) + 1,
        (
            (
                CASE r.statement_end_offset
                    WHEN -1 THEN DATALENGTH(st.text)
                    ELSE r.statement_end_offset
                END
                - r.statement_start_offset
            ) / 2
        ) + 1
    ) AS sql_text

FROM sys.dm_exec_requests r

CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st

WHERE r.session_id <> @@SPID

ORDER BY
    elapsed_sec DESC;

여기서 **`elapsed_sec`, `cpu_time`, `logical_reads`, `wait_type`, `blocking_session_id`**를 먼저 보시면 됩니다.

그리고 중요한 점 하나 있습니다. `sys.dm_exec_query_stats`와 `sys.dm_exec_requests`의 통계는 **서버 재시작, 플랜 캐시 삭제, SQL 재컴파일 등에 따라 초기화될 수 있습니다.** 장기적인 튜닝 대상을 잡으려면 **Query Store**를 사용하는 것이 더 적합합니다.

원하시는 환경이 **SQL Server 2016/2019/2022/2025 중 어느 버전인지**에 따라, 다음 단계에서 **현재 실행 SQL + Query Store + Wait + Execution Plan을 묶어서 "튜닝 대상 Top 20"으로 뽑는 DBA용 통합 SQL**로 구성할 수 있습니다.