운영 환경 성능 진단: DMV·Query Store·대기 통계

입문편에서는 실행 계획을 읽고 단일 쿼리의 비용을 줄이는 방법을 다뤘습니다. 하지만 실제 운영 서버에서 "왜 지금 느린가?"라는 질문에 답하려면 시야를 쿼리 하나가 아닌 서버 전체 워크로드로 넓혀야 합니다. CPU가 한계에 달했는지, 디스크 I/O가 포화 상태인지, 아니면 잠금 경합이 특정 테이블을 옥죄고 있는지는 단일 쿼리 분석만으로는 알 수 없습니다.

이 레슨에서는 SQL Server가 실시간으로 수집하는 동적 관리 뷰(DMV), 과거 이력을 보존하는 Query Store, 그리고 경량 트레이싱 도구인 확장 이벤트(Extended Events)를 활용해 운영 환경에서 병목을 체계적으로 진단하는 방법을 다룹니다.

학습 목표

• **대기 통계(Wait Statistics)**를 해석해 서버 병목의 유형(I/O, 잠금, CPU, 메모리 등)을 분류할 수 있다.
• sys.dm_exec_query_stats와 플랜 캐시를 사용해 서버 전체에서 비용이 큰 쿼리를 식별할 수 있다.
• Query Store로 쿼리 실행 계획의 변경(plan regression)을 감지하고 이전 계획을 강제 적용할 수 있다.
• 매개변수 스니핑(Parameter Sniffing) 문제를 진단하고 적합한 해결 옵션을 선택할 수 있다.
• **확장 이벤트(Extended Events)**로 프로덕션 환경에 부담 없이 워크로드를 추적할 수 있다.

대기 통계(Wait Statistics)로 병목 유형 분류하기

SQL Server는 모든 스레드가 무언가를 기다릴 때마다 그 유형과 시간을 sys.dm_os_wait_stats에 누적합니다. 이 뷰는 "서버가 지금 무엇 때문에 기다리고 있는가?"를 단 한 번의 쿼리로 요약해 주는 가장 강력한 진단 출발점입니다.

-- 누적 대기 통계에서 상위 20개 유형 추출 (노이즈 제거 포함)
SELECT TOP 20
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms,
    wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms,
    CAST(100.0 * wait_time_ms
         / SUM(wait_time_ms) OVER () AS DECIMAL(5,2)) AS pct
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
    -- SQL Server 내부 유휴 대기 — 실제 병목과 무관하므로 제외
    'SLEEP_TASK', 'BROKER_TO_FLUSH', 'BROKER_TASK_STOP',
    'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',
    'FT_IFTS_SCHEDULER_IDLE_WAIT', 'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
    'HADR_WORK_QUEUE', 'LAZYWRITER_SLEEP', 'LOGMGR_QUEUE',
    'ONDEMAND_TASK_QUEUE', 'REQUEST_FOR_DEADLOCK_MONITOR',
    'RESOURCE_QUEUE', 'SERVER_IDLE_CHECK', 'SLEEP_DBSTARTUP',
    'SLEEP_DBRECOVER', 'SLEEP_DBNULL', 'SLEEP_DBOPEN',
    'SLEEP_MASTERDBREADY', 'SLEEP_MASTERMDREADY',
    'SLEEP_MASTERUPGRADED', 'SLEEP_MSDBSTARTUP',
    'SLEEP_SYSTEMTASK', 'SLEEP_TEMPDBSTARTUP',
    'SNI_HTTP_ACCEPT', 'SP_SERVER_DIAGNOSTICS_SLEEP',
    'SQLTRACE_BUFFER_FLUSH', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
    'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', 'WAITFOR',
    'XE_DISPATCHER_WAIT', 'XE_TIMER_EVENT'
)
ORDER BY wait_time_ms DESC;

대기 유형은 크게 다음과 같이 분류할 수 있습니다.

대기 유형	분류	의미
PAGEIOLATCH_SH/EX	I/O	데이터 페이지를 디스크에서 읽는 중
WRITELOG	I/O (로그)	트랜잭션 로그 플러시 대기
LCK_M_*	잠금	행/페이지/테이블 잠금 경합
CXPACKET, CXCONSUMER	병렬 처리	병렬 쿼리 스레드 간 동기화
SOS_SCHEDULER_YIELD	CPU	CPU 과부하
RESOURCE_SEMAPHORE	메모리	쿼리 메모리 부족(sort/hash 등)
ASYNC_NETWORK_IO	네트워크	클라이언트가 결과셋을 빠르게 소비 못 함
PAGELATCH_EX	래치	메모리 내 핫 페이지 경합(tempdb 등)

💡 TIP signal_wait_time_ms는 스레드가 이미 깨어났지만 CPU 스케줄러를 기다린 시간입니다. 전체 대기 중 신호 대기 비율이 높다면 CPU가 병목입니다.

서버 재시작 이후 누적된 값이므로, 기준선(baseline)을 잡고 일정 시간 후 델타를 비교하는 방식이 더 정밀합니다.

-- 10분 간격으로 델타 측정하는 패턴
-- 1단계: 스냅샷 저장
SELECT wait_type, wait_time_ms, waiting_tasks_count
INTO #wait_baseline
FROM sys.dm_os_wait_stats;

-- ... 10분 경과 후 ...

-- 2단계: 델타 계산
SELECT
    w.wait_type,
    w.wait_time_ms - b.wait_time_ms AS delta_wait_ms,
    w.waiting_tasks_count - b.waiting_tasks_count AS delta_tasks
FROM sys.dm_os_wait_stats w
JOIN #wait_baseline b ON w.wait_type = b.wait_type
WHERE w.wait_time_ms - b.wait_time_ms > 0
ORDER BY delta_wait_ms DESC;

DROP TABLE #wait_baseline;

플랜 캐시와 sys.dm_exec_query_stats로 비용 큰 쿼리 찾기

대기 통계로 병목 유형을 파악했다면, 다음 단계는 그 병목을 유발하는 쿼리를 특정하는 것입니다. sys.dm_exec_query_stats는 현재 플랜 캐시에 올라있는 모든 실행 계획의 누적 통계(CPU, 논리 읽기, 실행 횟수, 경과 시간)를 제공합니다.

-- CPU 소비 기준 상위 20 쿼리
SELECT TOP 20
    qs.total_worker_time / 1000          AS total_cpu_ms,
    qs.execution_count,
    qs.total_worker_time / qs.execution_count / 1000
                                         AS avg_cpu_ms,
    qs.total_logical_reads,
    qs.total_logical_reads / qs.execution_count
                                         AS avg_logical_reads,
    qs.total_elapsed_time / qs.execution_count / 1000
                                         AS avg_elapsed_ms,
    qs.creation_time                     AS plan_cached_at,
    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,
    qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)  AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY qs.total_worker_time DESC;

논리 읽기 기준으로 정렬하면 I/O 집약 쿼리를, total_elapsed_time / execution_count로 정렬하면 사용자 체감 지연이 큰 쿼리를 찾을 수 있습니다.

⚠️ 주의 플랜 캐시는 메모리 압박 시 또는 관련 객체가 변경될 때 항목이 제거됩니다. 단기적인 스파이크 쿼리는 이미 사라졌을 수 있으므로, 장기 이력을 보려면 Query Store를 사용하세요.

현재 활성 요청을 실시간으로 보고 싶을 때는 sys.dm_exec_requests를 활용합니다.

-- 5초 이상 실행 중인 쿼리와 현재 대기 유형
SELECT
    r.session_id,
    r.status,
    r.wait_type,
    r.wait_time / 1000.0        AS wait_sec,
    r.cpu_time / 1000.0         AS cpu_sec,
    r.total_elapsed_time / 1000.0 AS elapsed_sec,
    r.logical_reads,
    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 current_statement
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id > 50          -- 시스템 세션 제외
  AND r.total_elapsed_time > 5000
ORDER BY r.total_elapsed_time DESC;

Query Store로 계획 회귀(Plan Regression) 추적과 강제 적용

Query Store는 SQL Server 2016부터 제공되는 기능으로, 쿼리의 실행 계획과 런타임 통계를 데이터베이스 내부에 자동으로 저장합니다. 플랜 캐시와 달리 재시작 후에도 이력이 유지되므로 "배포 전후로 성능이 달라진 쿼리"를 정확히 추적할 수 있습니다.

-- Query Store 활성화 (데이터베이스 단위)
ALTER DATABASE YourDatabase
SET QUERY_STORE = ON (
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    INTERVAL_LENGTH_MINUTES = 60,
    MAX_STORAGE_SIZE_MB = 1024,
    QUERY_CAPTURE_MODE = AUTO,  -- 빈도 낮은 쿼리 자동 필터
    SIZE_BASED_CLEANUP_MODE = AUTO
);

Query Store의 핵심 뷰 관계는 다음과 같습니다.

sys.query_store_query
    └─ sys.query_store_query_text  (쿼리 텍스트)
    └─ sys.query_store_plan        (실행 계획 버전들)
           └─ sys.query_store_runtime_stats  (시간대별 런타임 통계)
                  └─ sys.query_store_runtime_stats_interval

계획 회귀 감지

배포 이후 특정 쿼리가 느려졌다면 같은 query_id에 여러 plan_id가 존재하며, 그 중 최근 계획의 평균 시간이 급증해 있습니다.

-- 동일 쿼리에 여러 계획이 있고, 최근 계획이 더 느린 경우 탐지
SELECT
    q.query_id,
    qt.query_sql_text,
    p.plan_id,
    p.last_compile_start_time,
    rs.avg_duration / 1000.0  AS avg_ms,
    rs.count_executions,
    p.is_forced_plan
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p       ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
JOIN sys.query_store_runtime_stats_interval i
    ON rs.runtime_stats_interval_id = i.runtime_stats_interval_id
WHERE i.start_time >= DATEADD(HOUR, -24, GETUTCDATE())
ORDER BY q.query_id, rs.avg_duration DESC;

이전 계획 강제 적용

회귀가 확인됐다면 좋은 계획의 plan_id를 찾아 강제 적용합니다. 다음 번 컴파일부터 SQL Server는 해당 계획을 그대로 사용합니다.

-- ✅ 이전 성능이 좋았던 plan_id를 강제 적용
EXEC sys.sp_query_store_force_plan
    @query_id = 42,
    @plan_id  = 17;

-- 강제 적용 해제
EXEC sys.sp_query_store_unforce_plan
    @query_id = 42,
    @plan_id  = 17;

💡 TIP SSMS에서는 [쿼리 저장소 → 재발한 쿼리] 보고서를 통해 GUI로 회귀 계획을 식별하고 한 클릭으로 강제 적용할 수 있습니다.

매개변수 스니핑(Parameter Sniffing) 문제와 해결 옵션

저장 프로시저나 매개변수화 쿼리는 처음 컴파일될 때의 매개변수 값을 기준으로 실행 계획을 생성합니다. 이 계획이 캐시에 저장되어 이후 모든 호출에 재사용되는데, 처음 값이 극단적으로 분포가 치우쳐 있으면 다른 매개변수 값에는 최악의 계획이 됩니다. 이것이 매개변수 스니핑 문제입니다.

-- 예시: 대부분 주문은 최근 1년치이지만, 일부는 10년치를 조회
CREATE OR ALTER PROCEDURE dbo.GetOrdersByDate
    @StartDate DATE,
    @EndDate   DATE
AS
BEGIN
    SELECT OrderId, CustomerId, TotalAmount
    FROM dbo.Orders
    WHERE OrderDate BETWEEN @StartDate AND @EndDate;
END;
GO

-- 처음 호출이 10년 범위이면 → Table Scan 계획이 캐시됨
-- 이후 1일 범위 호출에도 동일한 Table Scan 계획 재사용 → ❌ 비효율

-- 처음 호출이 1일 범위이면 → Index Seek 계획이 캐시됨
-- 이후 10년 범위 호출에는 Index Seek 수백만 번 → ❌ 역시 비효율

해결 옵션

아래 네 가지 접근 방법 중 상황에 맞게 선택합니다.

-- 옵션 1: OPTIMIZE FOR — 특정 대표값으로 최적화
CREATE OR ALTER PROCEDURE dbo.GetOrdersByDate
    @StartDate DATE,
    @EndDate   DATE
AS
BEGIN
    SELECT OrderId, CustomerId, TotalAmount
    FROM dbo.Orders
    WHERE OrderDate BETWEEN @StartDate AND @EndDate
    OPTION (OPTIMIZE FOR (@StartDate = '2024-01-01', @EndDate = '2024-01-31'));
    -- ✅ 가장 흔한 범위 패턴 기준으로 플랜 고정
END;
GO

-- 옵션 2: OPTIMIZE FOR UNKNOWN — 통계 기반 평균값으로 컴파일
CREATE OR ALTER PROCEDURE dbo.GetOrdersByDateUnknown
    @StartDate DATE,
    @EndDate   DATE
AS
BEGIN
    SELECT OrderId, CustomerId, TotalAmount
    FROM dbo.Orders
    WHERE OrderDate BETWEEN @StartDate AND @EndDate
    OPTION (OPTIMIZE FOR UNKNOWN);
    -- ✅ 스니핑 자체를 비활성화. 분포가 고른 경우에 적합
END;
GO

-- 옵션 3: 지역 변수로 스니핑 회피 (단, 통계 평균값 사용)
CREATE OR ALTER PROCEDURE dbo.GetOrdersByDateLocalVar
    @StartDate DATE,
    @EndDate   DATE
AS
BEGIN
    DECLARE @S DATE = @StartDate,
            @E DATE = @EndDate;

    SELECT OrderId, CustomerId, TotalAmount
    FROM dbo.Orders
    WHERE OrderDate BETWEEN @S AND @E;
    -- ✅ 옵션 2와 유사 효과. 레거시 코드에 최소한의 변경으로 적용 가능
END;
GO

-- 옵션 4: WITH RECOMPILE — 매번 컴파일 (호출 빈도 낮을 때만 사용)
CREATE OR ALTER PROCEDURE dbo.GetOrdersByDateRecompile
    @StartDate DATE,
    @EndDate   DATE
AS
BEGIN
    SELECT OrderId, CustomerId, TotalAmount
    FROM dbo.Orders
    WHERE OrderDate BETWEEN @StartDate AND @EndDate
    OPTION (RECOMPILE);
    -- ⚠️ 호출 빈도가 높으면 CPU 오버헤드 증가
END;
GO

⚠️ 주의 SQL Server 2022 이상에서는 매개변수 민감 계획(PSP, Parameter Sensitive Plan) 최적화가 기본 활성화됩니다. 단일 쿼리에 대해 범위별 복수 계획을 자동 관리하므로 위 옵션 전에 먼저 확인하세요.

확장 이벤트(Extended Events)로 가볍게 워크로드 추적하기

SQL Trace와 Profiler는 오버헤드가 크고 SQL Server 2019 이후 deprecated 상태입니다. 확장 이벤트(XEvents)는 동일한 정보를 훨씬 낮은 비용으로 수집할 수 있는 현대적인 대안입니다.

-- 실행 시간이 1초(1,000,000 마이크로초) 이상인 쿼리만 캡처하는 세션
CREATE EVENT SESSION [SlowQueryCapture] ON SERVER
ADD EVENT sqlserver.sql_statement_completed (
    ACTION (
        sqlserver.sql_text,
        sqlserver.client_app_name,
        sqlserver.username,
        sqlserver.database_name,
        sqlserver.plan_handle
    )
    WHERE (duration >= 1000000)   -- 1초 = 1,000,000 µs
),
ADD EVENT sqlserver.rpc_completed (
    ACTION (
        sqlserver.sql_text,
        sqlserver.client_app_name,
        sqlserver.username,
        sqlserver.database_name
    )
    WHERE (duration >= 1000000)
)
ADD TARGET package0.ring_buffer (
    SET max_memory = 51200       -- 50 MB, 메모리 기반 버퍼
),
ADD TARGET package0.event_file (
    SET filename = N'C:\XEvents\SlowQueryCapture.xel',
    max_file_size = 100,         -- MB 단위
    max_rollover_files = 5
)
WITH (
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    TRACK_CAUSALITY = OFF        -- ✅ 오버헤드 최소화
);

-- 세션 시작
ALTER EVENT SESSION [SlowQueryCapture] ON SERVER STATE = START;

수집된 ring_buffer 데이터를 실시간으로 조회할 수 있습니다.

-- ring_buffer 타깃에서 이벤트 데이터 파싱
SELECT
    event_data.value('(event/@name)[1]',         'varchar(50)')  AS event_name,
    event_data.value('(event/@timestamp)[1]',    'datetime2')    AS ts,
    event_data.value('(event/data[@name="duration"]/value)[1]',
                     'bigint') / 1000              AS duration_ms,
    event_data.value('(event/action[@name="sql_text"]/value)[1]',
                     'nvarchar(max)')               AS sql_text,
    event_data.value('(event/action[@name="username"]/value)[1]',
                     'nvarchar(128)')               AS username
FROM (
    SELECT CAST(target_data AS XML) AS ring_data
    FROM sys.dm_xe_session_targets t
    JOIN sys.dm_xe_sessions s ON t.event_session_address = s.address
    WHERE s.name = 'SlowQueryCapture'
      AND t.target_name = 'ring_buffer'
) AS rb
CROSS APPLY ring_data.nodes('//RingBufferTarget/event') AS xe(event_data)
ORDER BY ts DESC;

세션이 불필요해지면 반드시 중지하고 삭제합니다.

ALTER EVENT SESSION [SlowQueryCapture] ON SERVER STATE = STOP;
DROP EVENT SESSION [SlowQueryCapture] ON SERVER;

💡 TIP SSMS의 [관리 → 확장 이벤트 → 새 세션 마법사]를 사용하면 GUI로 이벤트와 필터를 설정할 수 있습니다. 복잡한 세션도 마법사로 초안을 만든 뒤 T-SQL로 미세 조정하는 방식을 권장합니다.

tempdb·메모리·I/O 압박을 DMV로 식별하기

운영 서버의 세 가지 공통 리소스 압박 — tempdb 경합, 메모리 부족, 디스크 I/O — 을 각각 진단하는 DMV 쿼리를 살펴봅니다.

tempdb 경합 진단

tempdb의 할당 페이지(2:1, 2:3 등)에 대한 래치 경합은 대규모 병렬 워크로드에서 자주 발생합니다.

-- tempdb 할당 경합 확인 (PAGELATCH_* 대기 중 tempdb 관련)
SELECT
    session_id,
    wait_type,
    wait_duration_ms,
    resource_description
FROM sys.dm_os_waiting_tasks
WHERE wait_type LIKE 'PAGELATCH%'
  AND resource_description LIKE '2:%'   -- 2번 파일 = tempdb
ORDER BY wait_duration_ms DESC;

-- tempdb 데이터 파일 현황
SELECT
    file_id,
    name,
    physical_name,
    size * 8 / 1024.0   AS size_mb,
    growth,
    is_percent_growth
FROM tempdb.sys.database_files;

💡 TIP tempdb 경합 완화의 1순위는 데이터 파일 수를 논리 CPU 수(최대 8개)와 동일하게 늘리고 크기를 동일하게 사전 할당하는 것입니다.

메모리 압박 진단

-- 버퍼 풀 사용 현황
SELECT
    physical_memory_in_use_kb / 1024.0 AS mem_used_mb,
    page_fault_count,
    memory_utilization_percentage
FROM sys.dm_os_process_memory;

-- 메모리 부족으로 쿼리가 대기 중인지 확인
SELECT
    pool_id,
    granted_memory_kb / 1024.0      AS granted_mb,
    used_memory_kb / 1024.0         AS used_mb,
    requested_memory_kb / 1024.0    AS requested_mb,
    ideal_memory_kb / 1024.0        AS ideal_mb,
    is_small,
    dop,
    wait_order,                      -- 대기 중인 쿼리는 NULL이 아닌 양수, 그랜트를 받은 쿼리는 NULL (grant_time IS NULL이면 아직 대기 중)
    query_cost,
    sql_handle
FROM sys.dm_exec_query_memory_grants
-- ⚠️ 주의: 이 필터는 온프레미스 / 관리형 인스턴스 기준입니다.
--          Azure SQL Database에서는 pool_id, wait_order, scheduler_id, group_id 등이 NULL로 마스킹되어
--          WHERE wait_order > 0 조건이 의도대로 동작하지 않으므로 grant_time IS NULL 기준으로 대기 여부를 판단하세요.
WHERE wait_order > 0
ORDER BY requested_memory_kb DESC;

-- ❌ 메모리 부족 시 tempdb 유출(spill) 발생 여부
-- RESOURCE_SEMAPHORE 대기 + 아래 쿼리로 확인
-- ⚠️ 주의: total_spills 컬럼은 SQL Server 2016 SP2 / 2017 CU3(KB4041814) 이상에서만 제공됩니다.
--          그 이전 빌드에서는 'invalid column name' 오류가 발생하므로 사용할 수 없습니다.
SELECT TOP 10
    qs.total_spills,
    qs.execution_count,
    qs.total_spills / qs.execution_count AS avg_spills,
    SUBSTRING(st.text, 1, 200) AS query_snippet
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE qs.total_spills > 0
ORDER BY qs.total_spills DESC;

물리 I/O 핫스팟 진단

-- 파일별 I/O 누적 통계 (핫 파일 식별)
SELECT
    DB_NAME(vfs.database_id)   AS db_name,
    mf.physical_name,
    vfs.io_stall_read_ms,
    vfs.num_of_reads,
    CASE WHEN vfs.num_of_reads = 0 THEN 0
         ELSE vfs.io_stall_read_ms / vfs.num_of_reads
    END                        AS avg_read_ms,
    vfs.io_stall_write_ms,
    vfs.num_of_writes,
    CASE WHEN vfs.num_of_writes = 0 THEN 0
         ELSE vfs.io_stall_write_ms / vfs.num_of_writes
    END                        AS avg_write_ms,
    vfs.io_stall               AS total_io_stall_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs
JOIN sys.master_files mf
    ON vfs.database_id = mf.database_id
   AND vfs.file_id = mf.file_id
ORDER BY vfs.io_stall DESC;

평균 읽기 지연이 20ms 이상이면 스토리지 I/O가 병목입니다. SSD 스토리지에서는 15ms가 정상 범위이며 HDD는 515ms가 통상적입니다.

⚠️ 주의 sys.dm_io_virtual_file_stats는 서버 시작 이후 누적 값입니다. 현재 순간의 I/O를 보려면 짧은 간격으로 두 번 조회한 뒤 델타를 계산하세요.

요약

• 대기 통계(sys.dm_os_wait_stats)는 서버 병목 진단의 출발점으로, I/O·잠금·CPU·메모리 중 어떤 리소스가 부족한지 알려줍니다.
• sys.dm_exec_query_stats + 플랜 캐시는 현재 시점의 비용 큰 쿼리를 빠르게 식별하지만 휘발성이므로, 장기 이력은 Query Store에서 확인합니다.
• Query Store의 sp_query_store_force_plan으로 배포 후 발생한 계획 회귀를 즉시 롤백할 수 있습니다.
• 매개변수 스니핑 문제는 OPTIMIZE FOR, OPTIMIZE FOR UNKNOWN, 지역 변수, OPTION (RECOMPILE) 중 분포 특성과 호출 빈도에 맞는 옵션을 선택합니다.
• 확장 이벤트는 Profiler/Trace를 대체하는 경량 트레이싱 도구로, 임계값 필터로 불필요한 이벤트를 사전 차단해 오버헤드를 최소화합니다.
• tempdb 경합, 메모리 부족, 파일 I/O 핫스팟은 각각 전용 DMV(dm_os_waiting_tasks, dm_exec_query_memory_grants, dm_io_virtual_file_stats)로 진단합니다.

연습문제

1. 현재 서버에서 상위 5개 대기 유형을 조회하고, 각 유형이 어떤 리소스 압박을 나타내는지 표로 정리하는 쿼리를 작성하세요. 단, 내부 유휴 대기 유형(예: SLEEP_TASK, LAZYWRITER_SLEEP)은 제외해야 합니다.

   힌트 sys.dm_os_wait_stats에서 WHERE wait_type NOT IN (...) 조건과 OVER()를 사용한 비율 계산을 함께 활용하세요.

2. Query Store가 활성화된 데이터베이스에서, 지난 6시간 동안 동일한 query_id에 대해 두 개 이상의 실행 계획이 존재하며 최근 계획의 평균 실행 시간이 이전 계획보다 2배 이상 느린 쿼리를 찾는 쿼리를 작성하세요.

   힌트 sys.query_store_plan을 자기 조인하거나 MIN/MAX로 plan_id별 평균 시간을 집계한 뒤 비율을 계산합니다.

3. 아래와 같은 저장 프로시저가 있습니다. 매개변수 스니핑이 발생할 수 있는 이유를 설명하고, 호출 빈도가 매우 높은 상황에서 최소한의 코드 변경으로 해결하는 방법을 제시하세요.

   CREATE PROCEDURE dbo.GetProductsByCategory
       @CategoryId INT
   AS
       SELECT ProductId, Name, Price
       FROM dbo.Products
       WHERE CategoryId = @CategoryId;
   

   힌트 카테고리별 제품 수의 분포 편차를 생각해 보세요. OPTION (RECOMPILE)이 아닌 방법 중 하나를 선택하세요.

4. 현재 서버의 각 데이터베이스 파일에 대해 평균 읽기 지연(ms)과 평균 쓰기 지연(ms)을 계산하고, 평균 읽기 지연이 15ms 이상인 파일만 반환하는 쿼리를 작성하세요.

   힌트 sys.dm_io_virtual_file_stats(NULL, NULL)과 sys.master_files를 조인하고, 0으로 나누기를 NULLIF 또는 CASE로 방지하세요.