DP-300 시험에서 데이터베이스 튜닝 파트는 '언제 어떤 도구를 쓰는가'를 묻습니다. 인덱스를 재구성할 때 REBUILD냐 REORGANIZE냐, 통계를 수동으로 갱신해야 하는 상황은 언제인가, MAXDOP를 어디서 설정해야 하는가 같은 선택 문제가 핵심입니다. 선택지가 모두 그럴듯해 보이기 때문에 맥락 없이 암기하면 흔들립니다. 각 개념이 왜 존재하는지 이해하면 처음 보는 시나리오도 자연스럽게 풀립니다.
인덱스 단편화 — 책장 정리와 책 위치 메모
오랫동안 쓴 도서관 책장을 상상해보세요. 처음에는 번호 순서대로 꽂혀 있었지만, 반납과 대출이 반복되면서 빈자리가 생기고 책들이 제자리를 잃습니다. 데이터베이스 인덱스도 INSERT, UPDATE, DELETE가 쌓이면 이렇게 단편화됩니다.
단편화율은 로 측정하며, Microsoft 공식 임계값은 두 구간입니다. 단편화가 5~30%이면 — 책을 옮기지 않고 위치 메모만 고치는 작업으로, 페이지를 제자리에서 재배열하고 항상 온라인으로 실행됩니다. 단편화가 30%를 넘으면 — 책장을 통째로 비우고 처음부터 다시 정리하는 작업으로, 기본값은 오프라인이지만 을 추가하면 쿼리를 막지 않고 실행할 수 있습니다.
는 통계를 전체 스캔 기준으로 자동 갱신하지만 는 통계를 건드리지 않습니다. 조각화 해소와 통계 갱신을 동시에 하려면 가 유일한 선택입니다.
통계와 실행 계획 — 지도가 오래되면 길을 잃는다
자동차 내비게이션이 3년 전 지도를 쓴다면 없어진 도로로 안내할 수 있습니다. 쿼리 옵티마이저도 마찬가지입니다. 통계(statistics)는 각 컬럼의 데이터 분포를 담은 히스토그램인데, 이 정보가 오래되면 옵티마이저가 잘못된 실행 계획을 선택합니다.
는 기본값이 ON으로, 테이블 행의 약 20%가 변경되면 자동 갱신합니다. 수억 행 테이블에서는 2천만 행 변경이 필요해 갱신이 거의 일어나지 않을 수 있습니다. 이때는 으로 수동 갱신하거나, 으로 비동기 처리합니다. 는 마지막 갱신 이후 변경된 테이블의 통계만 선택적으로 갱신해 유지 관리 스크립트로 적합합니다.
DBCC CHECKDB — 건강 검진, 그런데 응급 수술도 있다
매년 건강검진을 받듯 데이터베이스도 정기적으로 무결성 검사가 필요합니다. 는 페이지 손상, 할당 오류, 제약 조건 위반을 모두 검사하는 포괄적인 진단 도구입니다. 는 nonclustered index를 건너뛰어 검사를 빠르게 마치고, 는 물리적 페이지 구조만 확인합니다.
손상이 발견되었을 때 옵션은 데이터 손실을 감수하고 손상을 제거합니다. 백업 복원이 불가능할 때 최후 수단으로만 씁니다. 시험에서 이 옵션이 나오면 '마지막 수단, 데이터 손실 위험'을 떠올리세요.
Automatic Tuning — 잠든 사이에도 돌아가는 자동 정비소
밤새 공장 기계가 스스로 이상 징후를 감지하고 설정을 되돌린다면 어떨까요? Azure SQL Database의 Automatic Tuning이 정확히 그 역할을 합니다.
은 Query Store 데이터를 기반으로 쿼리 플랜 회귀를 자동 감지합니다. 어제까지 0.1초였던 쿼리가 오늘 갑자기 10초가 됐다면, 이전의 좋은 플랜을 자동으로 강제합니다. Azure SQL Database에서는 기본값 ON이며, SQL Managed Instance와 SQL Server에서는 수동으로 활성화해야 합니다. CREATE INDEX와 DROP INDEX 추천 기능은 Azure SQL Database 전용으로, 추천 적용 후 성능이 나빠지면 자동으로 롤백합니다.
MAXDOP와 Resource Governor — 몇 개의 차선을 열 것인가
고속도로에서 차선이 너무 많으면 합류 지점에서 오히려 정체가 생깁니다. MAXDOP(Max Degree of Parallelism)는 하나의 쿼리가 동시에 사용할 수 있는 CPU 코어 수를 제한합니다.
MAXDOP 설정에는 세 가지 레벨이 있습니다. 인스턴스 레벨은 으로 설정합니다. 데이터베이스 레벨은 으로 설정하며 인스턴스 설정을 오버라이드합니다. 쿼리 레벨은 힌트로 개별 쿼리에만 적용됩니다. OLTP는 MAXDOP 1~4, OLAP는 코어 수의 절반 이상이 일반적입니다.
Resource Governor는 SQL Server와 SQL Managed Instance에서 워크로드를 그룹으로 분류해 CPU와 메모리 한도를 설정합니다. Classifier Function으로 어떤 연결이 어느 그룹에 들어갈지 결정합니다. Azure SQL Database에서는 지원하지 않습니다.
REBUILD vs REORGANIZE 한눈에 비교
| 항목 | REBUILD | REORGANIZE | |:--|:--|:--| | 단편화 임계값 | 30% 초과 | 5~30% | | 온라인 실행 | ONLINE = ON 옵션 필요 | 항상 온라인 | | 통계 갱신 | 자동 전체 갱신 | 갱신 없음 | | 잠금 영향 | 오프라인 시 테이블 잠금 | 낮음 |
시험에서 자주 나오는 함정: "온라인으로 실행되는 인덱스 재구성"이라는 표현이 나와도 단편화가 30% 초과라면 답은 입니다. REORGANIZE에는 ONLINE 옵션 자체가 없습니다.
!REBUILD vs REORGANIZE
Intelligent Query Processing — 실행하면서 스스로 학습한다
새로운 직원이 첫날보다 한 달 후에 일을 더 잘하는 이유는 경험에서 배우기 때문입니다. SQL Server 2017 이상의 Intelligent Query Processing(IQP)이 비슷한 원리로 작동합니다.
Adaptive Joins는 실행 시점에 실제 행 수를 보고 Hash Join과 Nested Loop Join 사이에서 동적으로 전환합니다. Memory Grant Feedback은 쿼리가 실제로 사용한 메모리를 기록하고 다음 실행 때 자동 조정합니다. Batch Mode on Rowstore는 히프·B-tree 테이블에서도 배치 처리 방식을 사용해 분석 쿼리를 가속합니다. IQP 기능들은 데이터베이스 호환성 수준 140 이상에서 활성화됩니다.
시험 핵심 정리
"인덱스 단편화 5~30%, 온라인 실행" -- REORGANIZE "인덱스 단편화 30% 초과, 통계도 갱신" -- REBUILD "REBUILD 오프라인 실행 방지" -- WITH (ONLINE = ON) "통계 오래됨, 전체 스캔으로 갱신" -- UPDATE STATISTICS WITH FULLSCAN "통계 갱신을 쿼리 블로킹 없이" -- AUTO_UPDATE_STATISTICS_ASYNC ON "플랜 회귀 자동 감지·복구" -- FORCE_LAST_GOOD_PLAN "인덱스 자동 추천 (Azure SQL Database 전용)" -- Automatic Tuning CREATE/DROP INDEX "데이터베이스 무결성 검사, 최후 복구 수단" -- DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS "워크로드별 CPU·메모리 분리 (MI/VM 전용)" -- Resource Governor "쿼리 병렬 CPU 코어 수 데이터베이스 레벨 설정" -- ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP "실행 중 메모리 할당 자동 조정" -- Memory Grant Feedback "실행 중 조인 방식 동적 전환" -- Adaptive Joins
Automatic Tuning = 플랜 회귀 자동 복구 (Azure SQL Database는 인덱스 추천까지), MAXDOP = 병렬 코어 수 제한 (스코프 3단계), Resource Governor = MI·VM 워크로드 분리