반응형
데이터베이스 관리자(DBA)와 데이터 엔지니어에게 SQL Server 모니터링은 단순한 상태 확인을 넘어, 장애를 사전에 예방하고 성능 저하를 빠르게 진단하는 핵심 역량입니다. 실제 운영 환경에서는 예상치 못한 성능 문제가 발생하며, 이를 신속하게 파악하고 해결하는 능력이 비즈니스 연속성을 보장합니다.
모니터링의 필요성과 접근 방법
SQL Server 모니터링은 크게 사전 예방적(Proactive) 모니터링과 사후 대응적(Reactive) 모니터링으로 나뉩니다. 사전 예방적 모니터링은 성능 지표를 지속적으로 추적하여 문제가 발생하기 전에 조치를 취하는 방식이며, 사후 대응적 모니터링은 이미 발생한 장애에 대해 신속하게 원인을 파악하고 복구하는 방식입니다. 효과적인 데이터베이스 운영을 위해서는 두 가지 접근 방법을 모두 활용해야 합니다.
SQL Server는 다양한 모니터링 도구를 제공합니다. 동적 관리 뷰(DMV), 확장 이벤트(Extended Events), SQL Server Profiler, 쿼리 저장소(Query Store) 등이 대표적입니다. 각 도구는 특정 상황에 적합하며, 상황에 따라 조합하여 사용하면 더욱 효과적인 모니터링이 가능합니다.
반응형
핵심 성능 지표와 DMV 활용
CPU 병목 현상 진단
CPU 사용률이 지속적으로 80% 이상을 유지한다면 CPU 병목 현상을 의심해야 합니다. CPU 병목은 최적화되지 않은 쿼리 계획, 잘못된 인덱스 설계, 또는 하드웨어 리소스 부족으로 인해 발생합니다.
현재 CPU를 많이 사용하는 쿼리 확인:
-- 현재 CPU를 가장 많이 사용하는 상위 10개 쿼리
PRINT '-- top 10 Active CPU Consuming Queries (aggregated)--';
SELECT TOP 10
GETDATE() runtime,
query_hash,
SUM(cpu_time) 'Total_Request_Cpu_Time_Ms',
SUM(logical_reads) 'Total_Request_Logical_Reads',
MIN(start_time) 'Earliest_Request_start_Time',
COUNT(*) 'Number_Of_Requests',
SUBSTRING(REPLACE(REPLACE(MIN(statement_text), CHAR(10), ' '), CHAR(13), ' '), 1, 256) AS "Statement_Text"
FROM (
SELECT
req.*,
SUBSTRING(ST.text, (req.statement_start_offset / 2)+1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE req.statement_end_offset
END-req.statement_start_offset)/ 2)+1) AS statement_text
FROM sys.dm_exec_requests AS req
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST
) AS query_stats
GROUP BY query_hash
ORDER BY Total_Request_Cpu_Time_Ms DESC;장기 실행 중인 CPU 집약적 쿼리 확인:
-- 현재 실행 중이며 CPU를 많이 사용하는 상위 10개 세션
PRINT '--top 10 Active CPU Consuming Queries by sessions--';
SELECT TOP 10
req.session_id,
req.start_time,
cpu_time 'cpu_time_ms',
OBJECT_NAME(ST.objectid, ST.dbid) 'ObjectName',
SUBSTRING(
REPLACE(REPLACE(SUBSTRING(ST.text, (req.statement_start_offset / 2)+1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE req.statement_end_offset
END-req.statement_start_offset)/ 2)+1),
CHAR(10), ' '), CHAR(13), ' '), 1, 512) AS statement_text
FROM sys.dm_exec_requests AS req
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST
ORDER BY cpu_time DESC;과거 CPU 사용량이 높았던 쿼리 분석 (쿼리 저장소 활용):
-- 최근 2시간 동안 CPU 사용량이 높았던 상위 15개 쿼리
WITH AggregatedCPU AS (
SELECT
q.query_hash,
SUM(count_executions * avg_cpu_time / 1000.0) AS total_cpu_millisec,
SUM(count_executions * avg_cpu_time / 1000.0)/ SUM(count_executions) AS avg_cpu_millisec,
MAX(rs.max_cpu_time / 1000.00) AS max_cpu_millisec,
MAX(max_logical_io_reads) max_logical_reads,
COUNT(DISTINCT p.plan_id) AS number_of_distinct_plans,
COUNT(DISTINCT p.query_id) AS number_of_distinct_query_ids,
SUM(CASE WHEN rs.execution_type_desc='Aborted' THEN count_executions ELSE 0 END) AS Aborted_Execution_Count,
SUM(CASE WHEN rs.execution_type_desc='Regular' THEN count_executions ELSE 0 END) AS Regular_Execution_Count,
SUM(CASE WHEN rs.execution_type_desc='Exception' THEN count_executions ELSE 0 END) AS Exception_Execution_Count,
SUM(count_executions) AS total_executions,
MIN(qt.query_sql_text) AS sampled_query_text
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q ON qt.query_text_id=q.query_text_id
JOIN sys.query_store_plan AS p ON q.query_id=p.query_id
JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id=p.plan_id
JOIN sys.query_store_runtime_stats_interval AS rsi ON rsi.runtime_stats_interval_id=rs.runtime_stats_interval_id
WHERE rs.execution_type_desc IN ('Regular', 'Aborted', 'Exception')
AND rsi.start_time >= DATEADD(HOUR, -2, GETUTCDATE())
GROUP BY q.query_hash
),
OrderedCPU AS (
SELECT
query_hash,
total_cpu_millisec,
avg_cpu_millisec,
max_cpu_millisec,
max_logical_reads,
number_of_distinct_plans,
number_of_distinct_query_ids,
total_executions,
Aborted_Execution_Count,
Regular_Execution_Count,
Exception_Execution_Count,
sampled_query_text,
ROW_NUMBER() OVER (ORDER BY total_cpu_millisec DESC, query_hash ASC) AS RN
FROM AggregatedCPU
)
SELECT
OD.query_hash,
OD.total_cpu_millisec,
OD.avg_cpu_millisec,
OD.max_cpu_millisec,
OD.max_logical_reads,
OD.number_of_distinct_plans,
OD.number_of_distinct_query_ids,
OD.total_executions,
OD.Aborted_Execution_Count,
OD.Regular_Execution_Count,
OD.Exception_Execution_Count,
OD.sampled_query_text,
OD.RN
FROM OrderedCPU AS OD
WHERE OD.RN <= 15
ORDER BY total_cpu_millisec DESC;메모리 사용량 모니터링
메모리는 SQL Server 성능에 직접적인 영향을 미치는 핵심 리소스입니다. 메모리 부족 상태가 발생하면 과도한 페이징이 발생하여 디스크 I/O가 급증하고 전체 시스템 성능이 저하됩니다.
현재 메모리 할당 상태 확인:
-- SQL Server 메모리 할당 현황
SELECT
sqlserver_start_time,
(committed_kb/1024) AS Total_Server_Memory_MB,
(committed_target_kb/1024) AS Target_Server_Memory_MB,
(committed_kb - committed_target_kb)/1024 AS Memory_Difference_MB
FROM sys.dm_os_sys_info;운영체제 메모리 상태 확인:
-- 운영체제 메모리 가용성 확인
SELECT
total_physical_memory_kb/1024 AS Total_Physical_Memory_MB,
available_physical_memory_kb/1024 AS Available_Physical_Memory_MB,
total_page_file_kb/1024 AS Total_Page_File_MB,
available_page_file_kb/1024 AS Available_Page_File_MB,
system_memory_state_desc
FROM sys.dm_os_sys_memory;메모리 부족으로 대기 중인 프로세스 확인:
-- 메모리 부여 대기 중인 쿼리
SELECT
session_id,
wait_duration_ms,
wait_type,
resource_description
FROM sys.dm_os_waiting_tasks
WHERE wait_type LIKE 'RESOURCE_SEMAPHORE%'
ORDER BY wait_duration_ms DESC;I/O 병목 현상 진단
디스크 I/O는 데이터베이스 성능에서 가장 빈번하게 발생하는 병목 지점입니다. 느린 쿼리의 상당수가 과도한 디스크 읽기/쓰기로 인해 발생합니다.
I/O 집약적인 쿼리 찾기:
-- I/O를 가장 많이 사용하는 상위 10개 쿼리
SELECT TOP 10
(total_logical_reads/execution_count) AS avg_logical_reads,
(total_logical_writes/execution_count) AS avg_logical_writes,
(total_physical_reads/execution_count) AS avg_physical_reads,
execution_count,
statement_start_offset,
p.query_plan,
q.text
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_query_plan(plan_handle) p
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q
ORDER BY (total_logical_reads + total_logical_writes)/execution_count DESC;I/O 대기 시간 확인:
-- 현재 I/O 대기 중인 세션
SELECT
session_id,
wait_type,
wait_duration_ms,
resource_description,
blocking_session_id
FROM sys.dm_os_waiting_tasks
WHERE wait_type LIKE 'PAGEIOLATCH%'
ORDER BY wait_duration_ms DESC;I/O 보류 상태 확인:
-- 디스크 I/O 대기 중인 요청 수
SELECT SUM(pending_disk_io_count) AS [Number_of_pending_IOs]
FROM sys.dm_os_schedulers;
-- 세부 I/O 대기 정보
SELECT *
FROM sys.dm_io_pending_io_requests;Wait Statistics 분석
Wait Statistics는 SQL Server가 어떤 리소스를 기다리는지를 보여주는 핵심 지표입니다. 대기 유형을 분석하면 병목 지점을 정확히 파악할 수 있습니다.
전체 대기 통계 확인:
-- 누적 대기 통계 조회
SELECT
wait_type,
wait_time_ms / 1000.0 AS wait_time_sec,
(wait_time_ms - signal_wait_time_ms) / 1000.0 AS resource_wait_time_sec,
signal_wait_time_ms / 1000.0 AS signal_wait_time_sec,
waiting_tasks_count,
wait_time_ms / NULLIF(waiting_tasks_count, 0) AS avg_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE',
'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH',
'WAITFOR', 'LOGMGR_QUEUE', 'CHECKPOINT_QUEUE',
'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH',
'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT',
'DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'
)
ORDER BY wait_time_ms DESC;세션별 대기 통계:
-- 특정 세션의 대기 통계 분석
SELECT
session_id,
wait_type,
wait_time_ms,
wait_time_ms / NULLIF(waiting_tasks_count, 0) AS avg_wait_time_ms,
max_wait_time_ms
FROM sys.dm_exec_session_wait_stats
WHERE session_id > 50 -- 시스템 세션 제외
ORDER BY wait_time_ms DESC;주요 대기 유형별 의미:
- PAGEIOLATCH_XX: 디스크에서 데이터를 읽어오기 위한 대기. 물리적 I/O 병목을 의미
- LCK_M_XX: 잠금 대기. 블로킹이나 데드락 문제 가능성
- CXPACKET: 병렬 쿼리 실행 시 스레드 간 동기화 대기
- SOS_SCHEDULER_YIELD: CPU 경합. 너무 많은 작업이 CPU를 요청
- WRITELOG: 트랜잭션 로그 쓰기 대기. 로그 파일 I/O 병목
- RESOURCE_SEMAPHORE: 메모리 부여 대기. 메모리 부족 상황
장애 시나리오별 대응 방법
시나리오 1: 블로킹(Blocking) 발생
블로킹은 한 세션이 리소스에 대한 잠금을 보유하고 있어 다른 세션이 대기하는 상황입니다. 장시간 블로킹이 지속되면 사용자 응답 시간이 급격히 증가합니다.
블로킹 세션 확인:
-- 현재 블로킹 발생 상황 조회
SELECT
blocking.session_id AS blocking_session_id,
blocked.session_id AS blocked_session_id,
blocking_text.text AS blocking_query,
blocked_text.text AS blocked_query,
blocked.wait_type,
blocked.wait_time,
blocked.wait_resource
FROM sys.dm_exec_requests blocked
INNER JOIN sys.dm_exec_sessions blocking
ON blocked.blocking_session_id = blocking.session_id
CROSS APPLY sys.dm_exec_sql_text(blocking.sql_handle) blocking_text
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) blocked_text
WHERE blocked.blocking_session_id > 0;블로킹 트리 구조 확인:
-- 블로킹 체인 전체 구조 파악
WITH BlockingTree AS (
SELECT
session_id,
blocking_session_id,
wait_type,
wait_time,
wait_resource,
0 AS Level
FROM sys.dm_exec_requests
WHERE blocking_session_id = 0
AND session_id IN (SELECT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id <> 0)
UNION ALL
SELECT
r.session_id,
r.blocking_session_id,
r.wait_type,
r.wait_time,
r.wait_resource,
bt.Level + 1
FROM sys.dm_exec_requests r
INNER JOIN BlockingTree bt ON r.blocking_session_id = bt.session_id
)
SELECT
REPLICATE(' ', Level) + CAST(session_id AS VARCHAR(10)) AS session_hierarchy,
session_id,
blocking_session_id,
wait_type,
wait_time,
wait_resource,
Level
FROM BlockingTree
ORDER BY Level, session_id;세션 상세 정보 및 실행 쿼리 확인:
-- 특정 세션의 실행 쿼리 확인
DBCC INPUTBUFFER(53); -- 53은 SPID 번호
-- 세션 종료 (헤드 블로커만 종료해야 함)
KILL 53;대응 방안:
- 헤드 블로커(Head Blocker) 파악: 블로킹 체인의 최상위 세션을 찾아 해당 트랜잭션이 왜 오래 실행되는지 분석
- 트랜잭션 커밋 유도: 가능하다면 헤드 블로커 세션에 커밋/롤백 요청
- 세션 강제 종료: 비즈니스 영향을 검토한 후 KILL 명령으로 세션 종료
- 인덱스 최적화: 반복적인 블로킹이 발생한다면 인덱스 추가로 잠금 범위 축소
- 트랜잭션 로직 개선: 트랜잭션 처리 시간을 최소화하도록 애플리케이션 로직 개선
시나리오 2: 데드락(Deadlock) 발생
데드락은 두 개 이상의 세션이 서로가 보유한 리소스를 기다리며 무한 대기에 빠지는 상황입니다. SQL Server는 데드락을 자동으로 감지하여 희생자(Victim)를 선택하고 롤백시킵니다.
데드락 정보 확인:
-- 시스템 로그에서 데드락 확인
EXEC sp_readerrorlog 0, 1, 'deadlock';
-- Extended Event로 데드락 추적
CREATE EVENT SESSION [Deadlock_Capture] ON SERVER
ADD EVENT sqlserver.xml_deadlock_report (
ACTION(sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_name)
)
ADD TARGET package0.asynchronous_file_target (
SET filename = N'C:\XE\deadlock_capture.xel',
max_file_size = 10,
max_rollover_files = 5
)
WITH (MAX_DISPATCH_LATENCY = 5 SECONDS);
-- Extended Event 시작
ALTER EVENT SESSION [Deadlock_Capture] ON SERVER STATE = START;데드락 방지 방법:
- 트랜잭션 순서 통일: 모든 트랜잭션이 동일한 순서로 리소스를 접근하도록 설계
- 트랜잭션 최소화: 트랜잭션 처리 시간을 최대한 짧게 유지
- 인덱스 최적화: 불필요한 테이블 스캔을 방지하여 잠금 범위 축소
- 격리 수준 조정: READ_COMMITTED_SNAPSHOT 격리 수준 사용 검토
- 타임아웃 설정: 데드락 발생 시 자동으로 재시도하는 로직 구현
-- 데드락 재시도 예제 (Python)
/*
import time
import pyodbc
def execute_with_retry(connection, query, max_retries=3):
for attempt in range(max_retries):
try:
cursor = connection.cursor()
cursor.execute(query)
connection.commit()
break
except pyodbc.Error as err:
if "deadlock" in str(err).lower():
connection.rollback()
print(f"Deadlock detected, retry {attempt + 1}/{max_retries}")
time.sleep(2)
else:
raise
*/시나리오 3: Tempdb 병목 현상
Tempdb는 임시 테이블, 테이블 변수, 정렬 작업 등에 사용되는 시스템 데이터베이스입니다. 많은 동시 사용자가 Tempdb를 사용하면 페이지 할당 경합이 발생할 수 있습니다.
Tempdb 경합 확인:
-- 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:1:%'; -- Database ID 2 = tempdb상세 경합 분석:
-- Tempdb 사용 중인 세션 정보
SELECT
r.session_id,
r.exec_context_id,
r.wait_duration_ms,
r.wait_type,
r.blocking_session_id,
r.resource_description,
CASE r.wait_type
WHEN 'CXPACKET' THEN
RIGHT(r.resource_description, CHARINDEX('=', REVERSE(r.resource_description)) - 1)
ELSE NULL
END AS Node_ID,
s.program_name,
st.text,
r.database_id,
qp.query_plan,
r.cpu_time
FROM sys.dm_os_waiting_tasks r
INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
INNER JOIN sys.dm_exec_requests er ON s.session_id = er.session_id
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
OUTER APPLY sys.dm_exec_query_plan(er.plan_handle) qp
WHERE s.is_user_process = 1
ORDER BY r.session_id, r.exec_context_id;Tempdb 최적화 방법:
- Tempdb 파일 분할: CPU 코어 수만큼 또는 1/4, 1/2로 데이터 파일 분할
- 추적 플래그 1118 사용: 혼합 익스텐트를 유니폼 익스텐트로 할당하여 SGAM 경합 방지
- 임시 테이블 사용 최소화: 테이블 변수 또는 CTE 사용 검토
- 인덱스 추가: 임시 테이블에 적절한 인덱스 생성하여 불필요한 스캔 방지
-- Tempdb 파일 분할 예제
ALTER DATABASE tempdb
ADD FILE (
NAME = tempdev2,
FILENAME = 'D:\MSSQL\Data\tempdb2.ndf',
SIZE = 8192MB,
FILEGROWTH = 512MB
);
ALTER DATABASE tempdb
ADD FILE (
NAME = tempdev3,
FILENAME = 'D:\MSSQL\Data\tempdb3.ndf',
SIZE = 8192MB,
FILEGROWTH = 512MB
);
ALTER DATABASE tempdb
ADD FILE (
NAME = tempdev4,
FILENAME = 'D:\MSSQL\Data\tempdb4.ndf',
SIZE = 8192MB,
FILEGROWTH = 512MB
);시나리오 4: 인덱스 조각화
인덱스 조각화는 데이터 삽입, 업데이트, 삭제 작업이 반복되면서 인덱스 페이지가 물리적으로 분산되는 현상입니다. 조각화가 심해지면 쿼리 성능이 저하됩니다.
인덱스 조각화 상태 확인:
-- 데이터베이스 내 모든 인덱스의 조각화 상태
SELECT
OBJECT_NAME(ips.object_id) AS TableName,
i.name AS IndexName,
ips.index_type_desc,
ips.avg_fragmentation_in_percent,
ips.avg_page_space_used_in_percent,
ips.page_count,
ips.record_count
FROM sys.dm_db_index_physical_stats(
DB_ID(), NULL, NULL, NULL, 'SAMPLED'
) ips
INNER JOIN sys.indexes i
ON ips.object_id = i.object_id
AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 10
AND ips.page_count > 100
ORDER BY ips.avg_fragmentation_in_percent DESC;인덱스 재구성 및 재구축:
-- 조각화 10~30%: 인덱스 재구성 (REORGANIZE)
ALTER INDEX [IndexName] ON [TableName] REORGANIZE;
-- 조각화 30% 이상: 인덱스 재구축 (REBUILD)
ALTER INDEX [IndexName] ON [TableName] REBUILD
WITH (ONLINE = ON, MAXDOP = 4);
-- 모든 인덱스 일괄 처리
ALTER INDEX ALL ON [TableName] REBUILD
WITH (ONLINE = ON, MAXDOP = 4);인덱스 유지보수 자동화 스크립트:
-- 조각화 정도에 따른 자동 처리
DECLARE @TableName NVARCHAR(128);
DECLARE @IndexName NVARCHAR(128);
DECLARE @Fragmentation FLOAT;
DECLARE @SQL NVARCHAR(MAX);
DECLARE index_cursor CURSOR FOR
SELECT
OBJECT_NAME(ips.object_id) AS TableName,
i.name AS IndexName,
ips.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips
INNER JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 10
AND ips.page_count > 100
AND i.name IS NOT NULL;
OPEN index_cursor;
FETCH NEXT FROM index_cursor INTO @TableName, @IndexName, @Fragmentation;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @Fragmentation > 30
BEGIN
SET @SQL = 'ALTER INDEX [' + @IndexName + '] ON [' + @TableName + '] REBUILD WITH (ONLINE = ON);';
PRINT 'Rebuilding: ' + @SQL;
EXEC sp_executesql @SQL;
END
ELSE IF @Fragmentation > 10
BEGIN
SET @SQL = 'ALTER INDEX [' + @IndexName + '] ON [' + @TableName + '] REORGANIZE;';
PRINT 'Reorganizing: ' + @SQL;
EXEC sp_executesql @SQL;
END
FETCH NEXT FROM index_cursor INTO @TableName, @IndexName, @Fragmentation;
END
CLOSE index_cursor;
DEALLOCATE index_cursor;시나리오 5: 쿼리 실행 계획 문제
쿼리 옵티마이저가 잘못된 실행 계획을 선택하면 동일한 쿼리라도 성능이 급격히 저하될 수 있습니다. 통계 정보 부족, 매개변수 스니핑, 인덱스 누락 등이 원인입니다.
실행 계획 분석:
-- 특정 쿼리의 실행 계획 확인
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT * FROM Orders WHERE OrderDate >= '2024-01-01';
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
-- 예상 실행 계획 확인 (실제 실행 없이)
-- SSMS에서 Ctrl+L 또는 상단 메뉴에서 "예상 실행 계획 표시" 클릭쿼리 저장소를 통한 성능 저하 쿼리 찾기:
-- 최근 실행 대비 성능이 저하된 쿼리
DECLARE @recent_start_time DATETIME = DATEADD(HOUR, -1, GETUTCDATE());
DECLARE @recent_end_time DATETIME = GETUTCDATE();
DECLARE @history_start_time DATETIME = DATEADD(DAY, -7, @recent_start_time);
DECLARE @history_end_time DATETIME = @recent_start_time;
WITH history AS (
SELECT
p.query_id,
CONVERT(FLOAT, SUM(rs.avg_duration * rs.count_executions)) total_duration,
SUM(rs.count_executions) count_executions,
COUNT(DISTINCT p.plan_id) num_plans
FROM sys.query_store_runtime_stats AS rs
JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id
WHERE (rs.first_execution_time >= @history_start_time
AND rs.last_execution_time < @history_end_time)
GROUP BY p.query_id
),
recent AS (
SELECT
p.query_id,
CONVERT(FLOAT, SUM(rs.avg_duration * rs.count_executions)) total_duration,
SUM(rs.count_executions) count_executions,
COUNT(DISTINCT p.plan_id) num_plans
FROM sys.query_store_runtime_stats AS rs
JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id
WHERE (rs.first_execution_time >= @recent_start_time
AND rs.last_execution_time < @recent_end_time)
GROUP BY p.query_id
)
SELECT
hist.query_id,
qt.query_sql_text,
ROUND((recent.total_duration/recent.count_executions - hist.total_duration/hist.count_executions)
* (recent.count_executions), 2) AS additional_duration_workload,
ROUND(recent.total_duration, 2) total_duration_recent,
ROUND(hist.total_duration, 2) total_duration_hist,
recent.count_executions count_executions_recent,
hist.count_executions count_executions_hist
FROM history hist
INNER JOIN recent ON hist.query_id = recent.query_id
INNER JOIN sys.query_store_query q ON q.query_id = hist.query_id
INNER JOIN sys.query_store_query_text qt ON qt.query_text_id = q.query_text_id
WHERE recent.count_executions > 10
ORDER BY additional_duration_workload DESC;강제 계획 적용:
-- 특정 쿼리에 대해 특정 실행 계획 강제 적용
EXEC sp_query_store_force_plan @query_id = 12345, @plan_id = 67890;
-- 강제 적용 해제
EXEC sp_query_store_unforce_plan @query_id = 12345, @plan_id = 67890;통계 업데이트:
-- 특정 테이블 통계 업데이트
UPDATE STATISTICS [TableName] WITH FULLSCAN;
-- 데이터베이스 전체 통계 업데이트
EXEC sp_updatestats;
-- 통계 정보 확인
DBCC SHOW_STATISTICS('TableName', 'IndexName');Extended Events를 활용한 심층 모니터링
Extended Events는 SQL Server의 강력한 이벤트 추적 시스템으로, Profiler보다 오버헤드가 낮고 더욱 세밀한 모니터링이 가능합니다.
장시간 실행 쿼리 추적:
-- 10초 이상 실행되는 쿼리 추적
CREATE EVENT SESSION [Long_Running_Query] ON SERVER
ADD EVENT sqlserver.sql_statement_completed (
SET collect_statement = 1
ACTION (
sqlserver.client_app_name,
sqlserver.client_hostname,
sqlserver.database_name,
sqlserver.plan_handle,
sqlserver.query_plan_hash,
sqlserver.session_id,
sqlserver.sql_text,
sqlserver.username
)
WHERE duration > 10000000 -- 마이크로초 단위 (10초)
)
ADD TARGET package0.event_file (
SET filename = N'C:\XE\Long_Running_Query.xel',
max_file_size = 100,
max_rollover_files = 5
)
WITH (
MAX_MEMORY = 4096 KB,
EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY = 30 SECONDS,
MAX_EVENT_SIZE = 0 KB,
MEMORY_PARTITION_MODE = NONE,
TRACK_CAUSALITY = ON,
STARTUP_STATE = ON
);
-- Extended Event 시작
ALTER EVENT SESSION [Long_Running_Query] ON SERVER STATE = START;블로킹 추적:
-- 5초 이상 블로킹된 세션 추적
CREATE EVENT SESSION [Blocked_Process] ON SERVER
ADD EVENT sqlserver.blocked_process_report (
ACTION (
sqlserver.client_app_name,
sqlserver.client_hostname,
sqlserver.database_name
)
)
ADD EVENT sqlserver.xml_deadlock_report (
ACTION (
sqlserver.client_app_name,
sqlserver.client_hostname,
sqlserver.database_name
)
)
ADD TARGET package0.asynchronous_file_target (
SET filename = N'C:\XE\blocked_process.xel',
metadatafile = N'C:\XE\blocked_process.xem',
max_file_size = 65536,
max_rollover_files = 5
)
WITH (MAX_DISPATCH_LATENCY = 5 SECONDS);
-- Extended Event 시작
ALTER EVENT SESSION [Blocked_Process] ON SERVER STATE = START;
-- 블로킹 임계값 설정 (밀리초 단위)
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'blocked process threshold', 5;
RECONFIGURE;Extended Event 결과 조회:
-- XEL 파일에서 데이터 읽기
SELECT
CAST(event_data AS XML) AS event_data_xml,
event_data
FROM sys.fn_xe_file_target_read_file(
'C:\XE\Long_Running_Query*.xel',
NULL, NULL, NULL
);
-- XML 파싱하여 읽기 쉽게 변환
;WITH EventData AS (
SELECT
CAST(event_data AS XML) AS event_xml
FROM sys.fn_xe_file_target_read_file(
'C:\XE\Long_Running_Query*.xel',
NULL, NULL, NULL
)
)
SELECT
event_xml.value('(event/@name)[1]', 'VARCHAR(50)') AS event_name,
event_xml.value('(event/@timestamp)[1]', 'DATETIME') AS event_timestamp,
event_xml.value('(event/data[@name="duration"]/value)[1]', 'BIGINT') / 1000000.0 AS duration_sec,
event_xml.value('(event/action[@name="sql_text"]/value)[1]', 'VARCHAR(MAX)') AS sql_text,
event_xml.value('(event/action[@name="database_name"]/value)[1]', 'VARCHAR(128)') AS database_name,
event_xml.value('(event/action[@name="session_id"]/value)[1]', 'INT') AS session_id
FROM EventData;세션 및 사용자 모니터링
현재 활성 세션 조회:
-- 현재 활성 세션 목록
SELECT
s.session_id,
s.login_name,
s.host_name,
s.program_name,
s.status,
s.cpu_time,
s.memory_usage,
s.total_elapsed_time,
s.last_request_start_time,
s.last_request_end_time,
c.client_net_address,
r.blocking_session_id,
r.wait_type,
r.wait_time,
st.text AS current_query
FROM sys.dm_exec_sessions s
LEFT JOIN sys.dm_exec_connections c ON s.session_id = c.session_id
LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE s.is_user_process = 1
ORDER BY s.session_id;장시간 유휴 세션 찾기:
-- 30분 이상 INACTIVE 상태인 세션
SELECT
session_id,
login_name,
host_name,
program_name,
status,
last_request_end_time,
DATEDIFF(MINUTE, last_request_end_time, GETDATE()) AS idle_minutes
FROM sys.dm_exec_sessions
WHERE status = 'sleeping'
AND is_user_process = 1
AND DATEDIFF(MINUTE, last_request_end_time, GETDATE()) > 30
ORDER BY idle_minutes DESC;로그 분석 및 모니터링
SQL Server 오류 로그 확인:
-- 최근 오류 로그 읽기
EXEC sp_readerrorlog 0, 1;
-- 특정 키워드로 로그 검색
EXEC sp_readerrorlog 0, 1, 'error';
EXEC sp_readerrorlog 0, 1, 'fail';
EXEC sp_readerrorlog 0, 1, 'I/O';트랜잭션 로그 상태 모니터링:
-- 데이터베이스별 로그 파일 사용량
SELECT
DB_NAME(database_id) AS DatabaseName,
name AS LogFileName,
type_desc,
physical_name,
size * 8.0 / 1024 AS SizeMB,
FILEPROPERTY(name, 'SpaceUsed') * 8.0 / 1024 AS UsedMB,
size * 8.0 / 1024 - FILEPROPERTY(name, 'SpaceUsed') * 8.0 / 1024 AS FreeMB
FROM sys.master_files
WHERE type_desc = 'LOG'
ORDER BY UsedMB DESC;
-- 로그 재사용 대기 이유 확인
SELECT
name,
log_reuse_wait_desc
FROM sys.databases;성능 개선 사례 및 Best Practice
사례 1: 인덱스 추가로 쿼리 성능 50% 개선
한 기업의 주문 관리 시스템에서 주문 날짜로 조회하는 쿼리가 평균 15초 이상 소요되는 문제가 발생했습니다. 실행 계획을 분석한 결과 테이블 스캔이 발생하고 있었고, OrderDate 컬럼에 인덱스를 추가하여 평균 7초로 단축했습니다.
-- 문제 쿼리
SELECT OrderID, CustomerID, OrderDate, TotalAmount
FROM Orders
WHERE OrderDate BETWEEN '2024-01-01' AND '2024-12-31';
-- 실행 계획 확인 결과: Table Scan 발생
-- 인덱스 추가
CREATE NONCLUSTERED INDEX IX_Orders_OrderDate
ON Orders(OrderDate)
INCLUDE (CustomerID, TotalAmount);
-- 결과: 15초 → 7초로 성능 개선 (53% 향상)사례 2: 파티셔닝으로 대용량 데이터 처리 성능 40% 개선
1억 건 이상의 데이터를 가진 Sales 테이블에서 특정 연도 데이터만 조회하는 쿼리의 성능이 저하되었습니다. 테이블을 연도별로 파티셔닝하여 쿼리 성능을 40% 향상시켰습니다.
-- 파티션 함수 생성
CREATE PARTITION FUNCTION pf_SalesDate (DATETIME)
AS RANGE RIGHT FOR VALUES
('2021-01-01', '2022-01-01', '2023-01-01', '2024-01-01', '2025-01-01');
-- 파티션 스키마 생성
CREATE PARTITION SCHEME ps_SalesDate
AS PARTITION pf_SalesDate
TO (fg_2020, fg_2021, fg_2022, fg_2023, fg_2024, fg_2025);
-- 기존 테이블을 파티션 테이블로 변환
CREATE TABLE Sales_New (
SalesID INT,
SalesDate DATETIME,
ProductID INT,
Amount DECIMAL(18,2),
CONSTRAINT PK_Sales_New PRIMARY KEY (SalesID, SalesDate)
) ON ps_SalesDate(SalesDate);
-- 데이터 마이그레이션
INSERT INTO Sales_New SELECT * FROM Sales;
-- 파티션별 데이터 확인
SELECT
$PARTITION.pf_SalesDate(SalesDate) AS PartitionNumber,
COUNT(*) AS RowCount
FROM Sales_New
GROUP BY $PARTITION.pf_SalesDate(SalesDate);사례 3: 배치 작업 최적화로 실행 시간 60% 단축
매일 500만 건의 데이터를 처리하는 배치 작업이 6시간 이상 소요되었습니다. 배치 크기를 조정하고 병렬 처리를 적용하여 2시간 30분으로 단축했습니다.
-- 기존 방식: 전체 데이터를 한 번에 처리
INSERT INTO ArchiveTable
SELECT * FROM TransactionTable
WHERE TransactionDate < DATEADD(MONTH, -6, GETDATE());
-- 개선된 방식: 배치 단위로 나누어 처리
DECLARE @BatchSize INT = 10000;
DECLARE @RowsAffected INT = 1;
WHILE @RowsAffected > 0
BEGIN
BEGIN TRANSACTION;
INSERT INTO ArchiveTable
SELECT TOP (@BatchSize) *
FROM TransactionTable WITH (ROWLOCK)
WHERE TransactionDate < DATEADD(MONTH, -6, GETDATE());
SET @RowsAffected = @@ROWCOUNT;
DELETE TOP (@BatchSize) FROM TransactionTable WITH (ROWLOCK)
WHERE TransactionDate < DATEADD(MONTH, -6, GETDATE());
COMMIT TRANSACTION;
WAITFOR DELAY '00:00:01'; -- 1초 대기로 시스템 부하 분산
END;모니터링 자동화 및 알림 설정
SQL Server Agent Job을 활용한 자동 모니터링:
-- 블로킹 자동 감지 및 알림
CREATE PROCEDURE sp_AlertBlockingProcess
AS
BEGIN
DECLARE @BlockingCount INT;
DECLARE @AlertMessage NVARCHAR(MAX);
SELECT @BlockingCount = COUNT(*)
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0
AND wait_time > 30000; -- 30초 이상 블로킹
IF @BlockingCount > 0
BEGIN
SET @AlertMessage = 'Critical: ' + CAST(@BlockingCount AS NVARCHAR(10)) +
' sessions are blocked for more than 30 seconds.';
-- 이메일 알림 발송
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DatabaseAlerts',
@recipients = 'dba@company.com',
@subject = 'SQL Server Blocking Alert',
@body = @AlertMessage;
END;
END;
-- Job 생성 및 스케줄링 (5분마다 실행)
EXEC sp_add_job
@job_name = 'Monitor_Blocking';
EXEC sp_add_jobstep
@job_name = 'Monitor_Blocking',
@step_name = 'Check Blocking',
@command = 'EXEC sp_AlertBlockingProcess';
EXEC sp_add_schedule
@schedule_name = 'Every_5_Minutes',
@freq_type = 4, -- Daily
@freq_interval = 1,
@freq_subday_type = 4, -- Minutes
@freq_subday_interval = 5;
EXEC sp_attach_schedule
@job_name = 'Monitor_Blocking',
@schedule_name = 'Every_5_Minutes';정리 및 권장사항
SQL Server 모니터링은 단순한 상태 확인이 아니라 데이터베이스의 건강성을 유지하고 장애를 예방하는 핵심 활동입니다. 효과적인 모니터링을 위해서는 다음 사항을 권장합니다:
실시간 모니터링 항목:
- CPU 사용률 (지속적으로 80% 이상이면 주의)
- 메모리 가용량 (Available Memory가 20% 이하면 경고)
- 디스크 I/O 대기 시간 (PAGEIOLATCH 대기가 빈번하면 디스크 병목)
- 블로킹 세션 (30초 이상 블로킹 발생 시 알림)
- 트랜잭션 로그 사용량 (로그 파일이 80% 이상 차면 확장 필요)
정기적 점검 항목:
- 인덱스 조각화 상태 (주간 점검, 조각화 30% 이상이면 재구축)
- 통계 정보 업데이트 (주간 점검 및 업데이트)
- 데이터베이스 무결성 검사 (DBCC CHECKDB 주간 실행)
- 쿼리 성능 추이 분석 (Query Store 활용)
- 성장 추세 분석 (데이터베이스 및 로그 파일 크기)
알림 설정 기준:
- Critical: 즉각 대응 필요 (블로킹 1분 이상, CPU 95% 이상, 디스크 여유 공간 5% 미만)
- Warning: 모니터링 강화 필요 (블로킹 30초 이상, CPU 80% 이상, 디스크 여유 공간 15% 미만)
- Info: 참고 사항 (장시간 실행 쿼리, 비정상 종료된 세션)
모니터링 도구 선택:
- 실시간 성능 확인: DMV 쿼리
- 장기 추세 분석: Query Store, Performance Monitor
- 이벤트 추적: Extended Events
- 종합 모니터링: Azure Monitor, SQL Server Management Studio
SQL Server 모니터링은 지속적인 학습과 경험 축적이 필요한 영역입니다. 각 환경의 특성에 맞게 모니터링 전략을 수립하고, 발생한 장애 사례를 문서화하여 향후 유사한 문제에 신속하게 대응할 수 있도록 준비해야 합니다.
반응형
'● Data Insights > SQL' 카테고리의 다른 글
| SQL 저장 프로시저 최적화 가이드 (3) | 2025.11.19 |
|---|---|
| MSSQL 분산 테이블(Distribution) 방식 개요 (0) | 2025.11.17 |
| MSSQL 실행계획과 쿼리 통계: 개념, 중요성, 활용 방법 및 내부 로직 (0) | 2025.11.15 |
| MSSQL 로그인 및 보안 관리 가이드 (0) | 2025.11.14 |
| SQL에서 Stored Procedure 사용 가이드 (0) | 2025.11.08 |
