반응형
반응형

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

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

'연구개발 > SQL2005' 카테고리의 다른 글

ANSI_NULLS NULL값과 비교  (0) 2010.06.08
QUOTED_IDENTIFIER  (0) 2010.06.08
[SQL 2005 GUIDE] 개체 카탈로그 뷰 - sys.sql_modules  (0) 2009.07.25
[SQL 2005 GUIDE] 추적 데이터와 성능 로그 데이터를 연계 분석하는 방법  (0) 2009.07.25
[SQL 2005 GUIDE] tempdb 성능 이슈 - 추적 플래그 1118  (0) 2009.07.25
반응형

실행 계획을 작성하는 작업은 CPU를 많이 사용하는 작업이기 때문에 새로운 실행 계획을 작성함으로써 얻어지는 이득이 실행 계획 작성에 드는 CPU 비용보다 크다면 재컴파일이 좋지만 그렇지 않은 불필요한 재컴파일은 가능한 한 제거하는 것이 좋습니다. 재컴파일이 발생하면 컴파일된 계획에 잠금을 걸기 때문에 동시성이 저하되므로 성능에 좋지 않은 영향을 미칠 수 있습니다.

실제로 운영 시스템을 모니터링하다 보면 사용자는 알지 못한 채 불필요한 재컴파일이 발생하는 경우가 흔히 있습니다. 그러므로 프로파일러 또는 추적 수집 SP를 사용하여 재컴파일에 대한 모니터링을 수행할 것을 권고합니다.

재컴파일을 모니터링하고자 하는 경우에는 EventSubClass 열을 수집해야 합니다.

다음은 EventSubClas 열의 값에 대한 설명입니다.

EventSubClass

설명

1

스키마 변경으로 인한 재컴파일

2

통계 변경으로 인한 재컴파일

3

지연된 컴파일로 인한 재컴파일

4

SET 옵션 변경으로 인한 재컴파일

5

임시 테이블 변경으로 인한 재컴파일

6

원격 행집합 변경으로 인한 재컴파일

7

FOR BROWSE 권한 변경으로 인한 재컴파일

8

쿼리 통지 환경 변경으로 인한 재컴파일

9

분할된 뷰 변경으로 인한 재컴파일

10

커서 옵션 변경으로 인한 재컴파일

11

OPTION (RECOMPILE) 요청으로 인한 재컴파일




참고로 재컴파일은 주로 다음과 같은 이유로 발생합니다.
- 변경된 행 임계값 (rowmodctr)
- DDL 문
- 스키마 변경
- 임시 테이블 사용
- SET 옵션 사용
   - ANSI_PADDING
   - FORCEPLAN
   - CONCAT_NULL_YIELDS_NULL
   - ANSI_WARNINGS
   - ANSI_NULLS
   - QUOTED_IDENTIFIER
   - ANSI_NULL_DFLT_ON
   - ANSI_NULL_DFLT_OFF

반응형
저작자표시

'연구개발 > SQL2005' 카테고리의 다른 글

[SQL 2005 GUIDE] 추적 중지하기 - 예제 SP 스크립트  (0) 2009.07.23
[SQL 2005 GUIDE] 추적 시작하기 - 예제 SP 스크립트  (0) 2009.07.23
fn_trace_gettable() 함수를 이용하여 추적파일을 테이블로 입력하는 방법  (0) 2009.07.23
[SQL 2005 GUIDE] 추적 - 권고사항  (0) 2009.07.23
[SQL 2005 GUIDE] 추적 - 이벤트와 열  (0) 2009.07.23

+ Recent posts

반응형

티스토리툴바