DB 성능 튜닝을 이야기할 때 절대 빠질 수 없는 존재가 있다.
바로 옵티마이저(Query Optimizer) 이다.
많은 개발자들이 "인덱스가 있는데 왜 느리지?" 라고 생각하지만,
실제 실행 계획을 결정하는 것은 옵티마이저다.
1. 옵티마이저란?
옵티마이저는 우리가 작성한 SQL을 분석해서
가장 비용이 낮다고 판단되는 실행 계획(Execution Plan) 을 선택하는 엔진이다.
핵심 역할:
- 어떤 인덱스를 사용할지 결정
- 조인 순서 결정
- Nested Loop / Hash Join / Merge Join 선택
- 병렬 처리 여부 결정
- Table Scan vs Index Seek 결정
즉,
SQL을 실제로 어떻게 실행할지 정하는 두뇌
2. 옵티마이저는 어떻게 판단할까?
MSSQL은 Cost-Based Optimizer (비용 기반 옵티마이저) 를 사용한다.
판단 기준
- 통계(Statistics)
- 인덱스 정보
- 데이터 분포
- 예상 Row 수 (Cardinality Estimation)
- CPU 비용
- I/O 비용
옵티마이저는 실제 데이터를 읽지 않고
통계 정보를 기반으로 "예상 비용"을 계산한다.
👉 그래서 통계가 틀리면 실행 계획도 틀릴 수 있다.
3. 실행 계획 확인 방법
예상 실행 계획
Ctrl + L실제 실행 계획
Ctrl + M또는
SET STATISTICS IO ON;
SET STATISTICS TIME ON;4. 왜 인덱스가 있는데도 Table Scan이 나올까?
많이 겪는 사례다.
예:
SELECT *
FROM Orders
WHERE OrderDate >= '2024-01-01'인덱스가 있어도 Scan이 나올 수 있는 이유:
- 조회 비율이 너무 높음 (예: 70% 이상)
- 통계 정보가 오래됨
- 인덱스 선택도가 낮음
- 암묵적 형변환 발생
- 파라미터 스니핑 문제
옵티마이저는
"인덱스를 타는 것보다 그냥 전체 스캔이 더 싸다"고 판단할 수 있다.
5. Cardinality Estimation (카디널리티 추정)
옵티마이저의 핵심은 예상 Row 수 계산이다.
예상 Row 수가 틀리면:
- 잘못된 조인 순서 선택
- 잘못된 Join 알고리즘 선택
- 병렬 처리 실패
예:
SELECT *
FROM Orders o
JOIN OrderDetails d ON o.Id = d.OrderId예상 Row 수가 적다고 판단하면 Nested Loop 선택
많다고 판단하면 Hash Join 선택
👉 실제 Row 수와 예상 Row 수 차이가 크면 성능 급락
6. 통계(Statistics)의 중요성
옵티마이저는 통계를 기반으로 판단한다.
통계 확인:
DBCC SHOW_STATISTICS ('Orders', 'IX_Orders_OrderDate');통계 업데이트:
UPDATE STATISTICS Orders;또는
EXEC sp_updatestats;통계가 오래되면
옵티마이저는 잘못된 실행 계획을 선택할 수 있다.
7. 파라미터 스니핑(Parameter Sniffing)
MSSQL 옵티마이저의 대표적인 이슈.
프로시저 첫 실행 시 전달된 파라미터 값을 기준으로
실행 계획을 캐시한다.
예:
CREATE PROC GetOrders
@CustomerId INT
AS
BEGIN
SELECT *
FROM Orders
WHERE CustomerId = @CustomerId
END- 특정 고객은 데이터가 1건
- 특정 고객은 데이터가 10만건
처음 실행 값에 따라 실행 계획이 고정된다.
해결 방법:
- OPTION (RECOMPILE)
- OPTIMIZE FOR
- LOCAL 변수 사용
- RECOMPILE 힌트
8. Join 알고리즘 선택
옵티마이저는 상황에 따라 다음 중 하나를 선택한다.
1) Nested Loop
- 소량 데이터에 유리
- 인덱스 있을 때 빠름
2) Hash Join
- 대량 데이터 처리에 적합
- 메모리 사용량 높음
3) Merge Join
- 정렬된 데이터에 유리
- 인덱스 정렬 구조 활용
👉 개발자가 직접 선택하는 것이 아니라
옵티마이저가 비용 계산 후 결정
9. 힌트(Hint)는 언제 써야 할까?
예:
SELECT *
FROM Orders WITH (INDEX(IX_Orders_OrderDate))힌트는 옵티마이저 판단을 강제로 바꾼다.
하지만:
- 통계 변경 시 오히려 독이 될 수 있음
- 유지보수 어려움
- 장기적으로 위험
👉 힌트는 최후의 수단
10. 옵티마이저 활용법
- 실행 계획을 항상 확인한다
- 통계를 이해한다
- 암묵적 형변환을 피한다
- WHERE 조건에 함수 사용을 피한다
- 인덱스를 무조건적으로 믿지는 않는다
마무리
옵티마이저는
SQL Server에서 가장 중요한 성능 결정 요소다.
우리가 SQL을 작성하지만
실제 실행 전략은 옵티마이저가 정한다.
튜닝의 시작은
실행 계획을 읽는 것이다.
