MSSQL 성능 모니터링

MS SQL에서 성능 모니터링을 할 수 있는 기본적인 쿼리를 작성하여, 서버의 CPU 사용률, 메모리 사용량, 디스크 I/O, 잠금 정보 등을 확인할 수 있습니다. 이 정보를 활용하면 성능 병목 지점을 분석하는 데 유용합니다. 아래는 주요 성능 관련 쿼리 예시들입니다.

1. CPU 사용률 확인 (최근 실행된 쿼리 기준)

SELECT TOP 10
    creation_time,
    last_execution_time,
    total_worker_time AS CPU_Time,
    total_elapsed_time AS Total_Time,
    total_physical_reads AS Reads,
    total_logical_writes AS Writes,
    execution_count,
    SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1,
        ((CASE statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE qs.statement_end_offset END
            - qs.statement_start_offset) / 2) + 1) AS query_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time DESC;

2. 메모리 사용량 확인 (Buffer Pool 사용량)

SELECT
    (CASE database_id WHEN 32767 THEN 'Resource Database' ELSE DB_NAME(database_id) END) AS DatabaseName,
    COUNT(*) * 8 / 1024 AS UsedMemory_MB
FROM sys.dm_os_buffer_descriptors
GROUP BY DB_NAME(database_id), database_id
ORDER BY UsedMemory_MB DESC;

3. 디스크 I/O 확인 (최근 사용된 파일 기준)

SELECT
    DB_NAME(vfs.database_id) AS DatabaseName,
    mf.name AS FileName,
    vfs.io_stall_read_ms AS ReadStallMS,
    vfs.io_stall_write_ms AS WriteStallMS,
    vfs.num_of_reads AS NumReads,
    vfs.num_of_writes AS NumWrites,
    vfs.size_on_disk_bytes / 1024 / 1024 AS SizeMB
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
JOIN sys.master_files AS mf
    ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
ORDER BY ReadStallMS DESC, WriteStallMS DESC;

4. 잠금(Lock) 정보 확인

SELECT
    tl.resource_type,
    tl.resource_database_id,
    tl.resource_associated_entity_id AS ResourceID,
    tl.request_mode,
    tl.request_status,
    tl.request_session_id,
    wt.blocking_session_id,
    DB_NAME(tl.resource_database_id) AS DatabaseName,
    t.text AS QueryText
FROM sys.dm_tran_locks AS tl
JOIN sys.dm_exec_sessions AS es
    ON tl.request_session_id = es.session_id
LEFT JOIN sys.dm_exec_requests AS wt
    ON tl.request_session_id = wt.session_id
CROSS APPLY sys.dm_exec_sql_text(wt.sql_handle) AS t
ORDER BY tl.resource_database_id, tl.resource_associated_entity_id;

5. 대기 상태(Waits) 정보 확인

SELECT
    wait_type,
    wait_time_ms / 1000.0 AS WaitTime_Sec,
    waiting_tasks_count,
    wait_time_ms / waiting_tasks_count AS AvgWaitTime_ms
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE 'SQLTRACE%'  -- 비활성화된 트레이스 이벤트 필터링
ORDER BY wait_time_ms DESC;

위 쿼리들은 성능 모니터링을 위한 기본적인 정보들을 제공합니다. 각 쿼리의 결과를 종합적으로 분석하면 시스템의 현재 상태를 파악할 수 있으며, 필요 시 병목 현상을 해결할 수 있는 단서를 제공해줍니다.