SQL SERVER OLTP 환경에서 리소스 상태를 모니터링하고 임계치를 설정·계산하는 법
OLTP(Online Transaction Processing) 시스템은 “느낌”으로 운영하기가 어렵습니다. 짧고 잦은 트랜잭션, 높은 동시성, 그리고 응답 시간 SLA(P95/P99) 같은 명확한 목표가 있기 때문입니다.
따라서 리소스 상태(압력)를 숫자와 기준(임계치) 으로 관리할 수 있어야 합니다.
1. OLTP 환경에서 봐야 할 것들: 관점 정리
OLTP 워크로드의 특징은 대체로 다음과 같습니다.
- 짧고 잦은 트랜잭션
- 많은 동시 사용자
- 소량의 행을 자주 읽고 쓰는 패턴
- 대개 “응답 시간 SLA”가 존재 (예: API P95 < 100ms)
이 특성을 고려하면, 모니터링 포인트는 자연스럽게 아래 5개 축으로 수렴합니다.
1) CPU 사용량 + Signal Wait 비율(스케줄러/CPU 압력)
2) 메모리/버퍼 풀 상태(PLE, 읽기 증가, 캐시 효율)
3) 디스크 I/O 성능(파일별 지연, I/O 관련 Wait)
4) tempdb & Version Store(스냅샷 격리/ONLINE 인덱스/정렬·해시 등)
5) Wait Statistics(“어디서 무엇 때문에 기다리는지”의 최종 요약 지표)
핵심은 단순히 “지표를 보는 것”이 아니라, 위 5개 축에서 Baseline(정상 패턴) 을 만들고 각 축별 임계치(경고/심각)를 수치화하는 것입니다.
이렇게 하면 OLTP 시스템의 “건강 상태”를 운영 관점에서 일관되게 관리할 수 있습니다.
2. CPU: 사용률과 Signal Wait로 보는 압력
2.1 무엇을 볼 것인가
OS/PerfMon(또는 모니터링 도구)
Processor: % Processor TimeSystem: Processor Queue Length- 해석 시 “절대값”보다 코어 수 대비 큐 길이, 그리고 지속 시간이 중요합니다.
SQL Server 내부
sys.dm_os_wait_stats에서 CPU 관련 WaitSOS_SCHEDULER_YIELD(스케줄러 양보 → CPU 경쟁/압력 신호)THREADPOOL(워크 스레드 부족)- 요청/쿼리 당 CPU 시간
- Query Store 또는
sys.dm_exec_query_stats기반으로 “CPU 먹는 상위 쿼리” 확인
2.2 간단 모니터링 스크립트(Wait 기반)
아래는 Wait Stats에서 “CPU 압력 후보”를 빠르게 보는 용도로 유용합니다.
주의:
sys.dm_os_wait_stats는 SQL Server 시작 이후 누적값입니다.
운영 분석에서는 “현재 값”을 그대로 보지 말고, 일정 간격으로 샘플링해서 Δ(증분, delta) 로 보는 방식이 가장 안전합니다.
-- CPU 관련 Wait 관찰(누적 기반)
SELECT TOP 10
wait_type,
wait_time_ms,
signal_wait_time_ms,
CAST(100.0 * signal_wait_time_ms / NULLIF(wait_time_ms, 0) AS decimal(10,2)) AS signal_wait_pct,
waiting_tasks_count
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE 'SLEEP%'
ORDER BY wait_time_ms DESC;Signal Wait 비율이 높다는 것은 “대기 중인 스레드가 CPU를 쓰기 위해 스케줄링되길 기다리는 시간”이 크다는 뜻입니다.
즉, 단순 CPU%뿐 아니라 스케줄러 대기(Signal Wait) 가 같이 상승하면 CPU 압력 가능성이 높습니다.
2.3 CPU 임계치 설정 예시(Baseline 기반)
CPU 임계치는 “몇 %가 절대적으로 위험”이라기보다, 내 시스템의 정상 패턴 대비 얼마나 튀는가로 잡는 편이 현실적입니다.
권장 접근:
1) 1~2주 정도 정상/피크 상황 데이터를 수집
2) 그 중 “서비스 품질이 만족스러운 구간(SLA 만족 구간)”만 필터링
3) 그 구간에서 CPU 평균/분포를 계산(예: 5분 평균, P95)
예시(가정):
- SLA 만족 구간에서 CPU 5분 평균 = 35%
- SLA 만족 구간에서 CPU P95 = 60%
임계치(예시):
- 경고: CPU 5분 평균 > 70%
- 심각: CPU 5분 평균 > 85%가 10분 이상 지속
- 보조 신호:
SOS_SCHEDULER_YIELD증가 + signal wait 비율 상승
3. 메모리 & 버퍼 풀: PLE 중심으로 “압력”을 읽기
3.1 주요 지표
PerfMon: SQLServer:Buffer Manager
Page life expectancy (PLE)Page reads/sec,Page writes/sec
기본 개념:
- PLE는 “페이지가 버퍼 풀(메모리)에 얼마나 오래 머무는지(초)”를 나타냅니다.
- 과거에는 “PLE < 300이면 문제” 같은 단일 규칙이 많이 쓰였지만,
실제로는 버퍼 풀 크기/워크로드에 따라 다르게 해석해야 합니다. - 또한 PLE는 NUMA/Buffer Node 단위로 나뉘어 보이는 환경이 있어, 가능하면 인스턴스/노드별 추세를 같이 보는 편이 좋습니다.
3.2 PLE 임계치(룰 오브 썸, 참고) 계산 예시
실무에서 자주 참고하는 경험 공식(룰 오브 썸) 중 하나는:
- PLE 임계치(초) ≈ (버퍼 풀 메모리(GB) / 4) × 300
예를 들어 버퍼 풀 메모리 = 32GB라면:
- 32 / 4 = 8
- 8 × 300 = 2400초
해석:
- PLE가 2400초 근처라면 대체로 정상 범주로 볼 여지가 있고,
- 그보다 훨씬 낮게 떨어지는 구간이 길게 유지된다면 메모리 압력 또는 비효율적인 쿼리(대량 스캔 등) 를 의심할 수 있습니다.
주의: 이 공식은 “절대 정답”이 아니라, Baseline 설계를 위한 출발점으로 사용하고
실제 운영에서는 SLA 만족 구간에서 관측된 PLE 분포를 기준으로 보정하는 편이 더 안전합니다.
3.3 임계치 설계 예시
- 경고: PLE < (계산값의 50%) 상태가 10분 이상 지속
- 심각: PLE < (계산값의 30%) 상태가 10분 이상 지속 +
Page reads/sec가 평소 대비 2배 이상 증가
포인트는 “PLE 하락”이 단독으로 문제라기보다, 물리 읽기(Page reads/sec) 증가와 같이 나타나는지 확인하는 것입니다.
4. 디스크 I/O: 지연(Latency)과 Wait로 병목 찾기
4.1 무엇을 볼 것인가
Windows PerfMon
PhysicalDisk: Avg. Disk sec/ReadPhysicalDisk: Avg. Disk sec/Write
SQL Server DMV
sys.dm_io_virtual_file_stats로 데이터/로그 파일별 평균 지연(ms) 관찰- I/O 관련 Wait
PAGEIOLATCH_*(데이터 페이지 읽기 대기)WRITELOG(로그 flush 대기)ASYNC_IO_COMPLETION등
일반적으로 현업에서는 평균 지연을 다음처럼 “대략적인 체감 범주”로 분류하는 경우가 많습니다(워크로드/스토리지 특성에 따라 다름).
- < 5ms: 매우 우수
- 5~10ms: 양호
- 10~20ms: 주의
- > 20ms: 병목 가능성 높음(지속 시 원인 분석 필요)
4.2 파일별 평균 지연 확인 스크립트(개선 버전)
SELECT
DB_NAME(vfs.database_id) AS [Database],
mf.name AS [LogicalName],
mf.type_desc AS [Type],
vfs.num_of_reads,
vfs.io_stall_read_ms,
CAST(1.0 * vfs.io_stall_read_ms / NULLIF(vfs.num_of_reads, 0) AS decimal(10,2)) AS [AvgRead_ms],
vfs.num_of_writes,
vfs.io_stall_write_ms,
CAST(1.0 * vfs.io_stall_write_ms / NULLIF(vfs.num_of_writes, 0) AS decimal(10,2)) AS [AvgWrite_ms],
mf.physical_name
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 [AvgRead_ms] DESC;주의: 이것도 “누적 평균” 성격이 강합니다.
운영에서는 일정 간격으로 샘플링해 구간 평균/증분 기반 지연을 보는 방식이 더 정확합니다.
4.3 임계치 설계 예시
- 경고: 데이터 파일
AvgRead_ms또는AvgWrite_ms> 15ms가 10분 이상 지속 - 심각: > 30ms가 지속 + I/O 관련 Wait(
PAGEIOLATCH_*,WRITELOG)가 Top Wait 상위에 고착
I/O는 스토리지/아키텍처에 따라 정상 범위가 달라지므로, 최종 임계치는 반드시 Baseline + SLA 만족 구간을 기준으로 보정하는 것을 권장합니다.
5. tempdb & Version Store: ONLINE 인덱스/스냅샷의 숨은 비용
tempdb는 단순히 “임시 테이블 쓰는 곳”이 아닙니다.
- 정렬/해시 연산, 임시 객체
- 스냅샷 격리(
READ_COMMITTED_SNAPSHOT,ALLOW_SNAPSHOT_ISOLATION) - ONLINE 인덱스 작업의 버전 스토어(version store)
특히 OLTP에서 ONLINE 인덱스 작업, 스냅샷 격리를 함께 사용한다면 version store가 tempdb를 빠르게 잠식할 수 있습니다.
5.1 Version Store 모니터링(대표 DMV)
-- DB별 Version Store 사용량 (KB)
SELECT
DB_NAME(database_id) AS [Database],
reserved_page_count * 8 AS reserved_kb
FROM sys.dm_tran_version_store_space_usage
ORDER BY reserved_kb DESC;-- tempdb 파일별 공간 사용량 및 Version Store
USE tempdb;
GO
SELECT
SUM(user_object_reserved_page_count) * 8 AS user_kb,
SUM(internal_object_reserved_page_count) * 8 AS internal_kb,
SUM(version_store_reserved_page_count) * 8 AS version_store_kb,
SUM(unallocated_extent_page_count) * 8 AS free_kb
FROM sys.dm_db_file_space_usage;실무에서는 위 값을 1분 간격으로 샘플링해서 증가 속도(Δversion_store_kb/분) 를 함께 보는 편이 효과적입니다.
5.2 임계치 설계 예시(보수적으로)
- tempdb 전체 크기 대비 Version Store 비율 기준
- 경고: Version Store가 tempdb의 20% 이상
- 심각: 30% 이상 + 증가 속도가 빠르게 유지
- 증가 속도 기준
- 1분 간격 샘플링으로
Δversion_store_kb/분계산 - baseline 대비 2~3배 이상이면 주의
tempdb는 장애를 막는 마지막 방어선이 되는 경우가 많으므로, 임계치를 조금 더 보수적으로 잡는 것이 안전합니다.
6. Wait Statistics: “어디서 기다리는지”로 병목 찾기
대부분의 튜닝 가이드는 Wait Stats를 출발점으로 삼으라고 권합니다. 이유는 단순합니다.
- 최종적으로 “CPU/IO/Lock/Network/Memory” 중 어디에서 시간이 소모되는지
- 가장 먼저 한 눈에 요약해 주기 때문입니다.
대표 Wait 유형 예시:
- CPU:
SOS_SCHEDULER_YIELD,THREADPOOL - I/O:
PAGEIOLATCH_*,WRITELOG,ASYNC_IO_COMPLETION - 락/동시성:
LCK_M_*,LATCH_* - 네트워크/클라이언트:
ASYNC_NETWORK_IO
상위 Wait 조회(기본 형태):
WITH Waits AS
(
SELECT
wait_type,
wait_time_ms = wait_time_ms - signal_wait_time_ms,
signal_wait_time_ms = signal_wait_time_ms,
waiting_tasks_count
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE 'SLEEP%'
)
SELECT TOP 15
wait_type,
wait_time_ms,
signal_wait_time_ms,
waiting_tasks_count
FROM Waits
ORDER BY wait_time_ms DESC;임계치를 “wait_time_ms가 얼마 이상이면 문제”처럼 고정하기보다는,
- 평소 정상 구간에서 Top N Wait 패턴을 저장해 두고
- Top N Wait 이름이 바뀌었는지, 비율이 얼마나 튀었는지
- 즉, “이상 탐지”에 가깝게 운영하는 것이 현실적입니다.
7. 임계치 설정/계산 방법론: Baseline + 비즈니스 SLA
7.1 단계 1 – Baseline 수집
1~2주 이상, 일정 간격(예: 1분/5분)으로 다음 데이터를 수집합니다.
- PerfMon/모니터링 도구: CPU, 메모리, 디스크, 네트워크, tempdb 사용량
- SQL DMVs: Wait Stats, IO Stats, tempdb/version store, Top Queries(쿼리 CPU/Duration 등)
그중 SLA가 만족되는 시간대만 필터링해서 각 지표의 평균/표준편차/P95를 계산합니다.
7.2 단계 2 – 리소스 임계치와 SLA 연결
예를 들어 SLA가 “웹 API P95 응답 시간 < 100ms”라고 가정합니다.
실제 데이터에서 SLA 만족 구간의 리소스가 아래였다면:
- CPU 5분 평균 40%, P95 60%
- I/O 평균 8ms, P95 12ms
- PLE 평균 3000초
- tempdb version store < 10%
임계치를 다음처럼 역산할 수 있습니다(예시):
- CPU 경고: 60% + 여유 10% → 70%
- I/O 경고/심각: 15ms / 30ms
- PLE 경고/심각: 관측 최소값 기준으로 하향 설정
- tempdb version store: 20% / 30% + 증가 속도 조건
즉, 임계치의 출발점은 “남들이 말하는 숫자”가 아니라
우리 시스템이 SLA를 만족할 때의 리소스 레벨이어야 합니다.
7.3 계산 예시 – tempdb Version Store
가정:
- Baseline 동안 tempdb 전체 크기 = 100GB
- SLA 만족 구간에서 Version Store 최대 = 10GB(10%)
임계치 설계(예시):
- 경고: 20GB(20%)
- 심각: 30GB(30%) + 증가 속도 > (baseline 최대 증가 속도의 2배)
이렇게 하면 ONLINE 인덱스 작업처럼 version store를 급격히 사용하는 작업의 영향도를 “수치로” 감지할 수 있습니다.
8. ONLINE = ON 인덱스 작업 시 영향도 체크(OLTP 관점)
이제 OLTP 운영에서 자주 고민하는 주제를 정리합니다.
“ONLINE = ON으로 인덱스를 만들거나 리빌드할 때, 서비스에 어떤 영향을 주며, 무엇을 보고 중단/계속을 결정해야 하는가?”
8.1 ONLINE 인덱스 빌드 개요
사용 구문:
CREATE INDEX ... WITH (ONLINE = ON)ALTER INDEX ... REBUILD WITH (ONLINE = ON)
특징(요약):
- 인덱스 작업 중에도 대부분의 읽기/쓰기 작업이 계속 가능
- 다만 버전 스토어(version store) 를 사용해 동시성을 확보하는 경우가 많아 → tempdb 부하 증가
- 시작/마무리 단계에서 짧게라도 스키마 락(SCH-M 등) 을 잡을 수 있음
- 지원 범위는 SQL Server 버전/에디션/옵션에 따라 달라질 수 있으므로, 반드시 사전 확인 필요
8.2 사전 점검 체크리스트(최소)
(1) 에디션 확인
SELECT SERVERPROPERTY('Edition') AS Edition;(2) CPU 여유
- 최근 피크 시간대 CPU 평균이 충분히 낮은지(예: 60% 미만 등)
SOS_SCHEDULER_YIELD,THREADPOOL이 상위로 치고 올라오지 않는지
(3) tempdb 여유 및 I/O 성능
USE tempdb;
GO
SELECT
SUM(size) * 8 / 1024 AS size_mb
FROM sys.database_files;
SELECT
SUM(unallocated_extent_page_count) * 8 / 1024 AS free_mb,
SUM(version_store_reserved_page_count) * 8 / 1024 AS version_store_mb
FROM sys.dm_db_file_space_usage;(4) 트랜잭션 로그 여유
- ONLINE 인덱스 작업은 로그도 많이 사용합니다.
- 로그 파일 크기/자동 증가/디스크 여유를 반드시 확인합니다.
8.3 ONLINE 인덱스 생성 예시(OLTP 친화적 옵션 예)
USE YourOLTPDB;
GO
CREATE NONCLUSTERED INDEX IX_Orders_CustomerId_OrderDate
ON dbo.Orders (CustomerId, OrderDate)
INCLUDE (Amount)
WITH
(
ONLINE = ON,
SORT_IN_TEMPDB = ON,
MAXDOP = 2
);
GO운영 팁: OLTP에서는 인덱스 작업이 전체 CPU를 잠식하지 않도록 MAXDOP를 보수적으로 두는 편이 안전합니다.
8.4 실행 중 영향도 모니터링(핵심 3가지)
(1) 현재 인덱스 작업 진행 상태/대기 유형
SELECT
r.session_id,
r.command,
r.status,
r.percent_complete,
r.cpu_time,
r.total_elapsed_time,
r.wait_type,
r.wait_time,
r.wait_resource,
t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.command LIKE '%INDEX%'
AND r.database_id = DB_ID('YourOLTPDB');percent_complete로 진행률/예상 소요를 가늠wait_type으로 병목이 CPU/I/O/락/tempdb 어디인지 판단
(2) tempdb Version Store 증가 속도
USE tempdb;
GO
SELECT
GETDATE() AS sample_time,
SUM(version_store_reserved_page_count) * 8 / 1024 AS version_store_mb
FROM sys.dm_db_file_space_usage;이 값을 1분 간격으로 수집하면, ONLINE 인덱스가 version store를 얼마나 빠르게 채우는지 추적 가능합니다.
(3) OLTP 응답 시간·처리량(SLA)
- APM/모니터링에서:
- API 응답 시간(P95/P99)
- TPS
- 타임아웃/에러 비율
- “절대값”보다 Baseline 대비 악화율(%) 을 함께 보는 것이 핵심입니다.
8.5 임계치 기반 “중단/계속” 의사결정 예시
사전 정의 임계치(예시):
- CPU 5분 평균 > 80%(또는 baseline 대비 +30% 이상)
- tempdb Version Store > tempdb의 30%
- API P95 응답 시간 > 2 × baseline(예: 100ms → 200ms 이상)
- I/O 관련 Wait(
PAGEIOLATCH_*,WRITELOG)가 Top 1~2를 장시간 유지
운영 정책(예시):
- 위 항목 중 2개 이상이 5분 이상 지속 → 인덱스 작업 중단(KILL) 및 재스케줄
- 하나만 위배되더라도 SLA 지표(API 지연/타임아웃)가 명확히 악화되면 즉시 중단
- 지원 환경이라면 Resumable(RESUMABLE=ON) 옵션을 활용해 “중단 후 재개” 전략을 고려(버전/에디션 지원 여부 확인)
9. OLTP 리소스 모니터링 & 임계치 체크리스트(실무 요약)
9.1 모니터링 항목(최소 세트)
- CPU: 5분 평균, Signal Wait 비율
- 메모리: PLE, Page reads/sec, 캐시 효율(가능 시)
- 디스크 I/O: Avg. Disk sec/Read/Write, 파일별 AvgRead/AvgWrite(ms)
- tempdb: 공간 사용량, Version Store 크기 및 증가 속도
- Wait Stats: Top N Wait 유형과 비율 변화
- SLA 지표: API P95/P99, TPS, 타임아웃/에러 비율
9.2 임계치 설계 원칙
- 최소 1~2주 Baseline 수집
- SLA 만족 구간에서 평균/P95/최소값 계산
- “경고” = Baseline 상한 + 여유(예: +20%)
- “심각” = Baseline 상한 + 더 큰 여유(예: +40%) 또는 SLA 직접 위배
- 반드시 지속 시간(Duration) 과 증가 속도(Rate) 조건을 함께 둠
9.3 ONLINE 인덱스 작업 운영 원칙
- Edition/버전/지원 옵션 확인
- 작업 전 CPU/tempdb/로그/I/O 여유 확인
- 작업 중:
sys.dm_exec_requests로 진행률·Wait 유형 모니터링- tempdb version store 증가량 추적
- SLA 지표(응답 시간/오류) 실시간 체크
- 임계치 초과 시 중단/재조정 원칙 준수(가능하면 resumable 전략 포함)
