MS SQL Server 쿼리 최적화기 (Optimization)

MS SQL 튜닝 하기
1. MS SQL Server 쿼리 최적화 하기

쿼리 처리 과정
Parsing > Standardization > Optimization > Compilation > Execute

Optimization
통계 및 조각 정보를 바탕으로 실행 계획 작성, 통계를 바탕으로 처리된다.
쿼리 분석 > 인덱스 선택 > 조인 처리

Plan Caching (Compilation 시 발생됨)
Ad-hoc 쿼리의 경우 모두 쿼리 처리의 5단계를 수행하여 처리된다. Ad-hoc 쿼리가 캐시에 저장되는 경우는 컴파일 단계까지의 비용이 캐시에 저장하는 것보다 클때 뿐이다. (7.0 까지는 캐싱 자체를 않했다.)
또한 매게 변수까지 정확하게 같아야만 캐싱된 쿼리를 수행할 수 있다.
하지만 저장 프로시저의 경우 항상 플랜을 사용하도록 강제화 된다. 단 실행 시 해당 저장 프로시저의 소유자 및 스키마 명을 명시하여 실행시키는 것이 혹시 발생 할 지 모르는 스키마 락을 예방시켜준다.

use master
go
select bucketid, cacheobjtype,refcounts, usecounts, setopts, sql
        from syscacheobjects order by sql

refcounts : 참조된 횟수
usecounts : 사용된 횟수
동일 쿼리에 setopts 가 다르게 나타난 경우 : 옵션이 다르게 설정된 채 쿼리가 호출됨
다음 구문은 저장 프로시저 생성 시 지정해 주는 것이 좋다.
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

자동 재 컴파일
- 실행 계획이 캐쉬에 없을 경우
- 인덱스가 변경된 경우
- 데이터가 변경된 경우
* 매개 변수의 경우 auto-parameterization 을 통해 캐싱이 된다. 단 매개 변수 값이 급격하게 변하는 경우 최초 재공된 매개 변수의 플랜이 의미가 없을 경우가 있다. 이경우에는 "with recompile" 옵션을 통해 재 컴파일을 실행 한다. (단 일회성으로 캐싱된다.)
* DBCC FREEPROCCACHE 를 통해 모든 프로시저 캐쉬를 제거 할 수 있다.

동적 쿼리가 저주인 이유
저장 프로시저를 동적 쿼리를 통해 작성 하는 경우 매개 변수에 대한 auto-parameterization이 이루어 지지 않고, 일반 Ad-hoc 쿼리와 같이 파라메터의 약간의 변화에도 재 컴파일이 되게 된다. 이 경우에는 굳이 파라메터를 넘겨야 하는 경우 sp_executesql를 사용하도록 하자.

참고 :
SQL Server 2000/2005 튜닝 (정원혁, 손광수 공저)
동적 SQL의 축복과 저주 (Erland Sommarskog)