SQL Server 성능/특성 정리: DMV/plan cache/parameter sniffing으로 느림을 설명하기

SQL Server 성능/특성 정리: DMV/plan cache/parameter sniffing으로 느림을 설명하기

이 글은 Microsoft SQL Server를 성능/운영 관점에서 길게 정리한 개인 노트입니다. 목표는 "지금 느린 이유"를 증거로 좁히고, 재발을 줄이는 체크리스트를 갖추는 것입니다.

전제: 성능은 DB만으로 결정되지 않습니다. 쿼리 패턴, 데이터 분포, 인덱스/스키마, 애플리케이션 트랜잭션 경계, 인프라(IO/네트워크)가 함께 결정합니다. 그래서 이 글은 '성능 모델(어디서 시간이 쓰이나) -> 설계/쿼리 -> 설정 -> 운영' 순서로 정리합니다.

SQL Server는 '통계 + 플랜 캐시 + 파라미터 스니핑'을 이해하면 성능이 안정화됩니다. Plan Cache/TempDB/병렬성 설정이 흔한 레버입니다.

이 DB를 언제 선택하나

정답은 항상 케이스 바이 케이스지만, 선택을 빠르게 만들기 위해 기준을 먼저 둡니다. 아래에 해당이 많을수록 이 DB를 고려할 만합니다.

- 엔터프라이즈 환경에서 강력한 도구/모니터링/운영 체계를 활용하고 싶을 때
- Azure/Windows 생태계와 통합 운영이 중요한 조직
- 복잡한 OLTP와 리포팅을 함께 다루되 튜닝 프로세스를 갖출 수 있을 때

성능 모델: 어디서 시간이 쓰이나

SQL Server는 옵티마이저가 통계 기반으로 실행 계획을 만들고, 그 계획을 플랜 캐시에 저장해 재사용합니다. 여기서 파라미터 스니핑이 생기면 같은 쿼리인데 어떤 값에서는 빠르고 어떤 값에서는 느린 문제가 발생할 수 있습니다.

또한 TempDB는 정렬/해시/버전 저장소 등 다양한 작업에 쓰여 병목이 되기 쉽고, 병렬성(MAXDOP)과 메모리 그랜트도 지연을 좌우합니다. 쿼리/인덱스뿐 아니라 엔진 동작을 같이 봐야 합니다.

핵심 용어 빠른 사전

성능/장애 분석에서 자주 쓰는 단어는 팀마다 다르게 이해되기 쉽습니다. 용어를 짧게 정의하고, 어떤 지표/증상과 연결되는지 같이 적어둡니다.

용어	설명	실무에서 연결되는 것
Plan Cache	실행 계획 재사용 캐시	나쁜 계획이 고정되면 특정 조건에서만 느려짐
Parameter Sniffing	첫 실행 파라미터에 맞춘 계획 고정	값 분포가 극단적이면 성능이 요동
Query Store	쿼리/플랜 기록과 관리	회귀 추적/플랜 강제에 유용
TempDB	임시 작업 공간	정렬/해시/버전 저장소 병목의 주 무대
Wait Stats	대기 이벤트 통계	병목 타입(CPU/IO/락)을 분리하는 기준
Memory Grant	쿼리 실행 메모리 할당	부족하면 spill, 과하면 다른 워크로드를 압박

옵션/핵심 요소(3~6)

자주 부딪히는 핵심 요소를 표로 먼저 고정해 두면, 장애/튜닝 때 팀 커뮤니케이션이 빨라집니다.

항목	의미	언제 쓰는지(실무 상황)
Actual Execution Plan	실제 실행 계획	추정치와 실제가 다른지 확인할 때
Statistics	데이터 분포 정보	옵티마이저 추정이 틀릴 때
Plan Cache	실행 계획 재사용	파라미터 스니핑/회귀를 설명할 때
Query Store	쿼리 성능 기록/플랜 관리	회귀를 추적하고 플랜을 관리할 때
TempDB	임시 작업 공간	정렬/해시/버전 저장소 병목을 볼 때

실무 튜닝 포인트(설정/옵션별)

설정에는 정답이 없습니다. 워크로드(읽기/쓰기/동시성/데이터 크기) 기준으로 관측하고 조정합니다. 아래는 실무에서 가장 자주 만지는 레버를 '항목별'로 정리한 것입니다.

AUTO_UPDATE_STATISTICS
의미: 통계 자동 갱신
언제 만지나: 통계가 낡아 계획이 틀릴 때
주의/트레이드오프: 대규모 변경에서 갱신 비용/타이밍 고려

QUERY_STORE
의미: 쿼리/플랜 기록
언제 만지나: 플랜 회귀를 추적/고정할 때
주의/트레이드오프: 운영 저장 공간/오버헤드 고려

MAXDOP
의미: 병렬성 제한
언제 만지나: 병렬 쿼리로 전체 시스템이 흔들릴 때
주의/트레이드오프: 너무 낮추면 대형 쿼리가 느려질 수 있음

cost threshold for parallelism
의미: 병렬 계획 선택 임계치
언제 만지나: 작은 쿼리까지 병렬로 가는 비용을 줄일 때
주의/트레이드오프: 환경에 맞게 조정 필요

tempdb files
의미: TempDB 파일 구성
언제 만지나: TempDB contention이 보일 때
주의/트레이드오프: 디스크/코어 수에 맞게 설계

parameter sniffing mitigation
의미: 스니핑 완화(예: recompile)
언제 만지나: 값 분포가 극단적이라 계획이 요동칠 때
주의/트레이드오프: 무분별한 힌트는 회귀 위험

memory grant
의미: 메모리 그랜트
언제 만지나: 대형 정렬/해시에서 메모리 대기가 보일 때
주의/트레이드오프: 잘못 튜닝하면 다른 워크로드가 피해

쿼리/스키마 설계에서 성능이 갈리는 지점

SQL Server에서도 인덱스는 가장 큰 레버입니다. 다만 플랜 캐시가 강하게 작동하므로 한 번 만들어진 계획이 계속 재사용되는 특성을 항상 염두에 둬야 합니다.

특정 파라미터 값에서만 느린 문제라면 통계/인덱스 외에 파라미터 스니핑을 의심하고, Query Store로 회귀를 추적하거나 재컴파일/플랜 관리 전략을 고려합니다.

-- Query Store 활성화(운영 정책에 따라)
ALTER DATABASE CURRENT SET QUERY_STORE = ON;

-- 파라미터 스니핑 완화 예(상황에 따라 선택)
SELECT * FROM Orders WHERE CustomerId = @CustomerId OPTION (RECOMPILE);

진단 명령/쿼리 모음(바로 실행용)

장애 때는 문서 찾을 시간이 없습니다. 아래는 현장에서 자주 쓰는 '첫 5분' 진단 명령/쿼리만 모은 것입니다. 환경/권한에 따라 일부는 제한될 수 있습니다.

-- 실행 중인 요청(대기/상태 포함)
SELECT session_id, status, wait_type, wait_time, cpu_time, total_elapsed_time FROM sys.dm_exec_requests ORDER BY total_elapsed_time DESC;

-- Query Store(활성화된 경우)에서 상위 쿼리 확인은 운영 정책에 맞게
-- TempDB/IO는 DMV와 모니터링 도구를 병행

자주 하는 실수(운영/튜닝)

실수 체크리스트:
- 특정 파라미터에서만 느린데도 파라미터 스니핑/플랜 캐시를 고려하지 않는다
- Query Store 없이 회귀를 '감'으로 추적한다
- TempDB 병목을 무시하고 인덱스만 늘린다
- MAXDOP/병렬성 정책 없이 병렬 쿼리 폭주를 허용한다
- 통계 갱신 정책이 흐릿해 계획이 흔들리는데도 방치한다
- 응급으로 힌트를 남발해 회귀를 더 키운다

운영/모니터링 체크리스트

운영 체크는 wait stats, top queries(Query Store), TempDB IO/대기, CPU/병렬성, 플랜 회귀를 중심으로 둡니다. 갑자기 느려졌다면 플랜이 바뀌었거나(또는 나쁜 플랜이 캐시에 고정) 자원이 경합한 경우가 흔합니다.

공통 체크리스트:
- 느린 쿼리 top N을 지속 수집(시간/호출수/총시간)
- 지연(p95/p99)과 오류율을 애플리케이션 지표와 함께 본다
- 캐시/메모리/디스크 IO 중 무엇이 병목인지 먼저 분리한다
- 복제/HA를 쓰면 일관성 요구가 높은 기능을 분리한다
- 튜닝/변경 후에는 동일 조건으로 재측정해 회귀를 막는다

트러블슈팅 루틴(순서 고정)

장애 때는 선택지가 너무 많아서 흔히 길을 잃습니다. 아래 순서를 팀 표준으로 정해두면, 원인 추적이 빨라지고 '감으로 설정만 만지는' 일을 줄일 수 있습니다.

1) 느린 쿼리와 발생 조건(특정 파라미터)을 확인한다
2) Actual Execution Plan/통계 상태를 본다
3) Query Store로 회귀 여부를 확인한다
4) 인덱스/통계를 정리하고 필요하면 플랜을 강제/완화한다
5) TempDB/병렬성/대기(wait stats)를 함께 점검한다
6) 변경 후 동일 조건으로 재측정한다

문제 상황(정확히 1개)

상황 -> 같은 쿼리인데 특정 고객(customerId)에서만 10배 느려진다.
원인 -> 파라미터 분포가 극단적이라 한 번 캐시된 실행 계획이 다른 값에는 최적이 아니었고(파라미터 스니핑), 그 계획이 재사용됐다.
해결 -> 통계/인덱스를 먼저 점검하고, 필요하면 Query Store로 좋은 플랜을 강제하거나 OPTION(RECOMPILE) 등으로 스니핑 영향을 완화한다.
예방 팁 -> 값 분포가 극단적인 쿼리를 식별해 Query Store로 모니터링하고, 릴리스 전 대표 파라미터 세트로 성능 테스트를 수행한다.

참고/출처

정확한 동작/버전별 차이는 공식 문서를 기준으로 확인합니다. 실무에서는 버전 차이가 곧 성능/장애 차이입니다.

• SQL Server Documentation
• Query Store