실행 계획 캐시(프로시저 캐시)

 실행 계획 캐시(프로시저 캐시)

 

1. 정의

   SQL Server가 컴파일된 쿼리 실행 계획을 저장하는 저장소

   (성능 향상을 위해 재사용 목적으로 컴파일 된 실행 계획을 저장함)

2. 특징

   - 메모리에 저장

     SQL Server 인스턴스의 서비스 재시작 및 서버 재부팅시 초기화

   - 지속적 변경

     사용 빈도가 낮은 실행 계획 삭제됨

     저장된 실행 계획에 의해 사용되는 데이터베이스 개체 변경시, 해당 개체와 관련된 실행 계획도 삭제됨  

   - 모든 실행 계획이 저장되는 것은 아님

     RECOMPILE 옵션을 포함하는 실행 계획은 캐시에 저장되지 않음

     비용이 0인 실행 계획도 저장안함

   - SQL Server 2005 에서 동적 관리 개체(DMOs, Dynamic Management Objects)가 추가됨

     동적 관리 개체를 통해 실행 계획 캐시에 있는 실행 계획을 SHOW PLAN XML 형식으로 출력가능

 

3. DMV에서 실행 계획 캐시 확인시 주의사항

   - XQuery 관련 지식 필요

     실행 계획이 SHOW PLAN XML 형식으로 반환되기 때문   

   - 더티 리드 사용

     실행 계획 캐시에 대해 쿼리를 실행하는 경우 최소 트랜잭션 격리수준을 사용 권장

     (TRANSACTION ISOLATION LEVEL READ UNCOMMITTED)

   - 부하 테스트 추천

     운영환경의 실행 계획 캐시 에 대한 쿼리 실행전 시스템 부하에 대한 테스트 권장

     (테스트 환경이 있을 경우 거기서 먼저 해보기 바람)

   - 자동 수집 금지

     실행 계획 캐시의 정보를 반환하는 쿼리는 많은 비용을 소모하기 때문에 자동으로 수집하는 Job을 생성하지 않은것이 좋음

4. 부록 (실행 계획 재사용에 영향을 주는 요소)

   - 저장 프로시저가 수행되는 데이터베이스

   - 트리거 실행 시, 영향 받는 행 수(inserted, deleted 테이블의 행 수 변경에 영향)

   - INSTEAD OF 트리거 ? 0행, 1행 모두 “1-plan” 공유

   - AFTER 트리거 ? 1행 “1-plan”, 0행 or n행 “n-plan” (n>1)

   - Bulk Insert문 실행 계획 캐싱 안됨

   - 8KB 이상의 일괄 처리 명령은 캐싱 안됨

   - SET 옵션

   - 테이블의 소유자 지정 여부

   - sys.syscacheobjects 의 uid

   - CREATE PROCEDURE … WITH RECOMPILE 옵션

   - EXEC (프로시저 명) WITH RECOMPILE 옵션

   - 데이터 변경으로 인한 통계 갱신 발생

   - 임시 테이블 사용시 통계 갱신 발생

   - 오브젝트의 스키마 변경으로 인한 재컴파일

   - 데이터베이스 수준의 변경 작업

   - 재컴파일 임계값 (통계 정보 갱신)

     : 일반 테이블(RT : Recompile Threshold)

       Row수가 500을 넘지 않을 경우 500 Row 이상 변경시

       Row수가 500을 넘을 경우 500 + 전체 Row의 20% 변경시

     : 임시 테이블

       Row수가 500을 넘지 않을 경우 500 Row 이상 변경시

       Row수가 6을 넘을 경우 6 + 전체 Row의 20% 변경시

     : 테이블 변수

       통계 갱신으로 인한 재 컴파일이 발생하지 않음

 

출처 : http://kalva.tistory.com/entry/실행-계획-캐시프로시저-캐시