실행 계획 캐싱 및 다시 사용

  • 아티클

업데이트: 2006년 7월 17일

SQL Server 2005에는 실행 계획과 데이터 버퍼를 모두 저장하는 데 사용되는 메모리 풀이 있습니다. 실행 계획이나 데이터 버퍼에 할당되는 풀 비율은 시스템 상태에 따라 동적으로 변동됩니다. 실행 계획을 저장하는 데 사용되는 메모리 풀 부분을 프로시저 캐시라고 합니다.

SQL Server 2005 실행 계획은 다음으로 구성됩니다.

  • 쿼리 계획
    대량 실행 계획은 여러 사용자가 사용하는 재진입용 읽기 전용 데이터 구조입니다. 이것을 쿼리 계획이라고 합니다. 쿼리 계획에는 사용자 컨텍스트가 저장되지 않습니다. 메모리에는 쿼리 계획의 복사본이 두 개까지만 존재할 수 있습니다. 즉, 모든 직렬 실행을 위한 복사본과 모든 병렬 실행을 위한 복사본이 각각 하나씩 있을 수 있습니다. 병렬 복사본은 병렬 처리 수준에 관계없이 모든 병렬 실행에 적용됩니다.
  • 실행 컨텍스트
    쿼리를 현재 실행하고 있는 각 사용자는 매개 변수 값 등의 해당 실행 관련 데이터를 보유하는 데이터 구조를 갖습니다. 이 데이터 구조를 실행 컨텍스트라고 합니다. 실행 컨텍스트 데이터 구조는 다시 사용됩니다. 사용자가 쿼리를 실행하는 경우 사용 중인 구조가 없으면 새 사용자를 위한 컨텍스트로 다시 초기화됩니다.

실행 컨텍스트, 동일한 쿼리, 다른 리터럴

SQL Server 2005에서 SQL 문이 실행될 때 관계형 엔진은 먼저 프로시저 캐시를 조사하여 동일한 SQL 문에 대한 기존 실행 계획이 있는지 확인합니다. SQL Server 2005에서는 발견된 기존 계획을 다시 사용하여 SQL 문을 다시 컴파일하는 오버헤드를 줄입니다. 기존의 실행 계획이 없는 경우 SQL Server 2005에서 쿼리에 대해 새로운 실행 계획이 생성됩니다.

SQL Server 2005에는 특정 SQL 문에 대한 기존 실행 계획을 찾는 효율적인 알고리즘이 있습니다. 대부분의 시스템에서 이러한 검색에 사용되는 최소 리소스는 모든 SQL 문을 컴파일하는 대신 기존 계획을 다시 사용함으로써 절약되는 리소스보다도 적습니다.

캐시에서 사용되지 않은 기존 실행 계획과 새 SQL 문을 대응시키는 알고리즘을 적용하려면 모든 개체 참조가 정규화되어야 합니다. 예를 들어 다음에서 첫 번째 SELECT 문은 기존 계획과 일치되지 않지만 두 번째 문은 일치됩니다.

SELECT * FROM Contact

SELECT * FROM Person.Contact

개별 실행 계획이 SQL Server 2000 및 SQL Server 2005 인스턴스에서 다시 사용될 가능성이 SQL Server 6.5 이전 버전에서 다시 사용될 가능성보다 더 큽니다.

실행 계획 에이징

실행 계획은 생성 후 프로시저 캐시에 보관됩니다. SQL Server 2005에서는 공간이 필요한 경우에만 캐시에서 오래되고 사용하지 않는 계획을 에이징합니다. 각 쿼리 계획과 실행 컨텍스트에는 구조를 컴파일하는 데 드는 비용을 나타내는 관련 비용 요소가 있습니다. 이러한 데이터 구조에는 에이지 필드도 있습니다. 개체가 연결에서 참조될 때마다 에이지 필드는 컴파일 비용 요소에 따라 증가합니다. 예를 들어 쿼리 계획의 비용 요소가 8이고 두 번 참조되면 에이지는 16이 됩니다. 지연 기록기 프로세스는 프로시저 캐시의 개체 목록을 주기적으로 검색합니다. 그런 다음 각 검색에 대해 각 개체의 에이지 필드 값을 1씩 감소시킵니다. 다른 사용자가 예제 쿼리 계획을 참조하지 않는 경우 16번의 프로시저 캐시 검색 후 예제 쿼리 계획의 에이지는 0으로 감소합니다. 다음 조건에 해당할 경우 지연 기록기 프로세스에서 개체를 할당 취소합니다.

  • 메모리 관리자에 메모리가 필요하고 사용할 수 있는 모든 메모리가 현재 사용되고 있는 경우
  • 개체의 에이지 필드가 0인 경우
  • 연결에서 개체가 현재 참조되지 않는 경우

에이지 필드는 개체가 참조될 때마다 증가하므로 자주 참조되는 개체의 에이지 필드는 0으로 감소되지 않고 캐시에서 에이징되지도 않습니다. 자주 참조되지 않는 개체는 할당 취소하는 것이 적절하지만 다른 개체에 메모리가 필요한 경우를 제외하고는 실제로 할당 취소되지 않습니다.

실행 계획 다시 컴파일

특정 데이터베이스 변경 시 새로운 데이터베이스 상태에 따라 실행 계획이 비효율적이거나 유효하지 않게 될 수 있습니다. SQL Server에서는 실행 계획을 무효화하고 해당 계획을 유효하지 않은 것으로 표시하는 변경 내용을 검색합니다. 이러한 경우에는 쿼리를 실행하는 다음 연결을 위해 새 계획을 다시 컴파일해야 합니다. 다음과 같은 조건에서 계획이 무효화될 수 있습니다.

  • 쿼리에서 참조하는 테이블이나 뷰가 변경된 경우(ALTER TABLE 및 ALTER VIEW)
  • 실행 계획에 사용되는 인덱스가 변경된 경우
  • UPDATE STATISTICS 등의 문에서 명시적으로 생성되거나 자동으로 생성되어 실행 계획에 사용되는 통계가 업데이트된 경우
  • 실행 계획에 사용되는 인덱스가 삭제된 경우
  • 명시적으로 sp_recompile을 호출하는 경우
  • 쿼리에서 참조하는 테이블을 수정하는 다른 사용자가 INSERT 또는 DELETE 문으로 키를 많이 변경한 경우
  • 트리거가 있는 테이블의 경우 inserted 또는 deleted 테이블의 행 수가 현저하게 증가하는 경우
  • WITH RECOMPILE 옵션을 사용하여 저장 프로시저를 실행하는 경우

대부분의 다시 컴파일은 문 정확성이나 잠재적으로 더 빠른 쿼리 실행 계획을 얻는 데 필요합니다.

SQL Server 2000에서는 일괄 처리 내의 문이 다시 컴파일을 발생시킬 때마다 저장 프로시저, 트리거, 임시 일괄 처리 또는 준비된 문을 통해 제출되었는지에 관계없이 전체 일괄 처리가 다시 컴파일됩니다. SQL Server 2005에서는 다시 컴파일을 유발하는 일괄 처리 내의 문만 다시 컴파일됩니다. 이 차이 때문에 SQL Server 2000과 SQL Server 2005의 다시 컴파일 횟수는 비교할 수 없습니다. 또한 SQL Server 2005에서는 기능 집합이 확장되어 더 많은 다시 컴파일 유형을 제공합니다.

다시 컴파일을 유발하고 이에 따라 CPU 시간 및 잠금과 관련하여 성능 저하를 일으키는 문의 수는 대개 적으므로 문 수준 다시 컴파일이 성능에 유리합니다. 문 수준 다시 컴파일을 사용하면 다시 컴파일하지 않아도 되는 일괄 처리 내의 다른 문의 경우 이러한 성능 저하가 발생하지 않습니다.

SQL Server 2005에서 SQL Server 프로파일러 SP:Recompile 추적 이벤트는 문 수준 다시 컴파일을 보고합니다. SQL Server 2000에서는 이 추적 이벤트가 일괄 처리 다시 컴파일만 보고합니다. 또한 SQL Server 2005에서는 이 이벤트의 TextData 열이 채워집니다. 따라서 SQL Server 2000에서처럼 다시 컴파일을 발생시킨 Transact-SQL 텍스트를 찾기 위해 SP:StmtStarting 또는 SP:StmtCompleted를 추적하지 않아도 됩니다.

SQL Server 2005에서는 문 수준 다시 컴파일을 보고하는 SQL:StmtRecompile이라는 새 추적 이벤트도 추가합니다. 이 추적 이벤트는 다시 컴파일을 추적하고 디버깅하는 데 사용할 수 있습니다. SP:Recompile은 저장 프로시저와 트리거에 대해서만 생성되는 반면 SQL:StmtRecompile은 sp_executesql, 준비된 쿼리 및 동적 SQL을 사용하여 실행되는 저장 프로시저, 트리거, 임시 일괄 처리 및 일괄 처리에 대해 생성됩니다.

SP:Recompile 및 SQL:StmtRecompile의 EventSubClass 열에는 다시 컴파일하는 이유를 나타내는 정수 코드가 포함됩니다. 다음 표에서는 각 코드 번호의 의미를 설명합니다.

EventSubClass 값 설명

1

스키마가 변경되었습니다.

2

통계가 변경되었습니다.

3

지연된 컴파일입니다.

4

SET 옵션이 변경되었습니다.

5

임시 테이블이 변경되었습니다.

6

원격 행 집합이 변경되었습니다.

7

FOR BROWSE 권한이 변경되었습니다.

8

쿼리 알림 환경이 변경되었습니다.

9

분할된 뷰가 변경되었습니다.

10

커서 옵션이 변경되었습니다.

11

OPTION (RECOMPILE)이 요청되었습니다.

[!참고] AUTO_UPDATE_STATISTICS 데이터베이스 옵션을 ON으로 설정하면 마지막 실행 이후 통계가 업데이트되거나 카디널리티가 크게 변경된 테이블이나 인덱싱된 뷰를 대상으로 하는 쿼리가 모두 다시 컴파일됩니다. 이러한 동작은 일반 사용자 정의 테이블, 임시 테이블 및 DML 트리거로 생성된 inserted 테이블과 deleted 테이블에 적용됩니다. 과도한 재컴파일로 인해 쿼리 성능이 저하되면 이 설정을 OFF로 변경하십시오. AUTO_UPDATE_STATISTICS 데이터베이스 옵션을 OFF로 설정하면 통계나 카디널리티 변경 내용을 기반으로 다시 컴파일이 수행되지 않습니다. 단, DML INSTEAD OF 트리거에 의해 생성되는 inserted 테이블과 deleted 테이블은 예외입니다. 두 테이블은 tempdb에 생성되므로 두 테이블에 액세스하는 쿼리의 다시 컴파일은 tempdb의 AUTO_UPDATE_STATISTICS 설정에 따라 결정됩니다. SQL Server 2000에서는 이 설정이 OFF인 경우에도 DML 트리거 inserted 테이블과 deleted 테이블의 카디널리티 변경 사항을 기반으로 계속하여 쿼리가 다시 컴파일됩니다. AUTO_UPDATE_STATISTICS 해제 방법에 대한 자세한 내용은 인덱스 통계를 참조하십시오.

참고 항목

참조

SQL Server, SQL Statistics 개체

개념

버퍼 관리

관련 자료

SQL Server 2005에서의 일괄 컴파일, 다시 컴파일 및 계획 캐싱 문제(Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005)

도움말 및 정보

SQL Server 2005 지원 받기

변경 내역

릴리스 내역

2006년 7월 17일
새로운 내용
  • 계획을 무효화하는 조건 목록에 WITH RECOMPILE 옵션을 사용하는 저장 프로시저 실행에 대한 내용을 추가했습니다.

2005년 12월 5일
새로운 내용
  • AUTO_UPDATE_STATISTICS 데이터베이스 옵션을 OFF로 설정했을 때의 쿼리 다시 컴파일에 대한 참고에 DML INSTEAD OF 트리거에 의해 생성된 inserted 및 deleted 테이블에는 다르게 적용됨을 추가했습니다.