[SQL 2005 GUIDE] 재컴파일 발생 원인 추적하기

SQL Server 2000에서 재컴파일 이슈를 분석해 보신 분들은 SP:Recompile 이라는 추적 이벤트를 사용해 보셨을 겁니다.

SQL Server 2005에서는 statement level의 재컴파일 발생을 알려 주는 SQL:StmtRecompile 라는 추적 이벤트가 추가되었습니다.
SP:Recompile 이벤트는 저장 프로시저와 트리거에 대해서만 발생하는 이벤트인데 비하여, SQL:StmtRecompile 이벤트는 저장 프로시저, 트리거 뿐만 아니라 ad-hoc batch에 대해서도 발생하는 이벤트입니다. (ad-hoc batch에는 sp_executesql, prepared queries,  dynamic SQL을 사용하여 실행되는 batch들이 포함됩니다.)
SP:Recompile과 SQL:StmtRecompile의 EventSubClass 컬럼에는 재컴파일이 발생하는 원인을 나타내는 정수값이 저장됩니다.

저장 프로시저의 재컴파일이 발생하는 원인을 확인하고자 하는 경우에는 프로파일러 또는 SQLDIAG로 SP:Recompile 이벤트와

EventSubClass 컬럼을 추가하여 Trace를 수집하여 분석하면 됩니다.

 

EventSubClass 컬럼의 값에 대한 다음 설명을 참고 바랍니다.

 

EventSubClass  재컴파일 원인 
1                      스키마 변경
2                      통계(Statistics) 변경
3                      지연된 컴파일 (Deferred compile).
4                      SET 옵션 변경
5                      임시 테이블 변경
6                      원격 행 집합(Remote rowset) 변경 
7                      FOR BROWSE 권한 변경
8                      Query notification 환경 변경
9                      분할된 뷰(Partitioned view) 변경
10                     커서 옵션 변경
11                     OPTION (RECOMPILE) 사용

 

http://msdn2.microsoft.com/en-us/library/ms181055.aspx 참조

 

재컴파일 발생 여부는 SQL Re-compilations/sec 성능 카운터와 Sys.dm_exec_query_stats DMV의 plan_generation_num 값을 확인하면 됩니다. plan_generation_num 값이 증가하면 재컴파일이 발생하고 있다는 것을 나타냅니다.

 

 

참고 -
참고로 리컴파일 이슈는 아주 자주 불러지는 sql 에 대해서 더 중요합니다. 배치 작업을 하는 곳에서 컴파일 시간이 30초 라고 해도 실행시간이 1시간 이다 라고 생각하면 이 컴파일 시간을 무시해도 좋지만, 컴파일 하는데 0.4초 정도 나오는데 연속적으로 100번씩 불러지는 프로시져는 반드시 리컴파일 이슈를 확인하셔야 합니다. (참고로 그냥 적어 봅니다.)