아티클
09/27/2010

실행 계획 캐싱 및 다시 사용

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

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

쿼리 계획

대량 실행 계획은 여러 사용자가 사용하는 재진입용 읽기 전용 데이터 구조입니다. 이것을 쿼리 계획이라고 합니다. 쿼리 계획에는 사용자 컨텍스트가 저장되지 않습니다. 메모리에는 쿼리 계획의 복사본이 한 개나 두 개만 있습니다. 하나는 모든 직렬 실행용이고 다른 하나는 모든 병렬 실행용입니다. 병렬 복사본은 병렬 처리 수준에 관계없이 모든 병렬 실행에 적용됩니다.
실행 컨텍스트

쿼리를 현재 실행하고 있는 각 사용자는 매개 변수 값 등의 해당 실행 관련 데이터를 보유하는 데이터 구조를 갖습니다. 이 데이터 구조를 실행 컨텍스트라고 합니다. 실행 컨텍스트 데이터 구조는 다시 사용됩니다. 사용자가 쿼리를 실행하는 경우 사용 중인 구조가 없으면 새 사용자를 위한 컨텍스트로 다시 초기화됩니다.

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

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

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

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

SELECT * FROM Contact

SELECT * FROM Person.Contact

프로시저 캐시에서 실행 계획 제거

실행 계획은 이를 저장하기에 충분한 메모리가 있는 한 프로시저 캐시에 계속 남아 있습니다. 메모리의 여유가 많지 않으면 데이터베이스 엔진에서는 비용을 기반으로 한 방법을 사용하여 프로시저 캐시에서 어떤 실행 계획을 제거할지 결정합니다. 비용을 기반으로 한 결정을 내리기 위해 데이터베이스 엔진에서는 다음 요인에 따라 각 실행 계획에 대한 현재 비용 변수를 늘리거나 줄입니다.

사용자 프로세스에서 캐시에 실행 계획을 삽입하는 경우 현재 비용을 원래 쿼리 컴파일 비용과 같게 설정하고, 임시 실행 계획의 경우 사용자 프로세스에서 현재 비용을 0으로 설정합니다. 그 후 사용자 프로세스에서 실행 계획을 참조할 때마다 현재 비용을 원래 컴파일 비용으로 다시 설정하고, 임시 실행 계획의 경우 사용자 프로세스에서 현재 비용을 늘립니다. 어떠한 계획이든 현재 비용의 최대값은 원래 컴파일 비용입니다.

메모리의 여유가 많지 않으면 데이터베이스 엔진에서는 프로시저 캐시에서 실행 계획을 제거하여 이에 대처합니다. 어떤 계획을 제거할지 결정하기 위해 데이터베이스 엔진에서는 각 실행 계획의 상태를 반복 조사하고 현재 비용이 0인 계획을 제거합니다. 메모리가 부족하다는 이유만으로는 현재 비용이 0인 실행 계획이 자동으로 제거되지 않습니다. 데이터베이스 엔진에서 계획을 조사하여 현재 비용이 0이라는 사실을 확인했을 때만 해당 계획이 제거됩니다. 실행 계획을 조사할 때 데이터베이스 엔진에서는 쿼리에 현재 사용되고 있지 않은 계획에 대해 현재 비용을 0에 가깝게 점차 줄여나갑니다.

데이터베이스 엔진에서는 메모리 요구 사항을 충족하기에 충분할 만큼 실행 계획이 제거될 때까지 실행 계획을 되풀이하여 조사합니다. 그 결과로 메모리가 부족한 상태에서 실행 계획의 비용이 여러 차례에 걸쳐 증감할 수 있습니다. 충분한 메모리가 다시 확보되면 데이터베이스 엔진에서는 사용되지 않는 실행 계획의 현재 비용을 더 이상 줄이지 않으며 해당 비용이 0인 계획을 포함한 모든 실행 계획이 프로시저 캐시에 계속 남습니다.

데이터베이스 엔진에서는 리소스 모니터와 사용자 스레드를 사용하여 프로시저 캐시에서 메모리를 확보하여 메모리 부족 문제에 대처합니다. 리소스 모니터와 사용자 스레드를 통해 계획의 실행 여부를 동시에 조사하여 사용되지 않는 실행 계획 각각의 현재 비용을 줄일 수 있습니다. 전체적인 메모리 부족 현상이 발생하면 리소스 모니터를 통해 프로시저 캐시에서 실행 계획이 제거됩니다. 리소스 모니터에서는 시스템 메모리, 프로세스 메모리, 리소스 풀 메모리 및 모든 캐시의 최대 크기에 대한 정책을 따르는 방식으로 메모리를 확보합니다.

모든 캐시의 최대 크기는 버퍼 풀 크기에 따라 결정되며 최대 서버 메모리를 초과할 수 없습니다. 최대 서버 메모리를 구성하는 방법에 대한 자세한 내용은 sp_configure(Transact-SQL)에서 최대 서버 메모리 설정을 참조하십시오.

단일 캐시 메모리 부족 현상이 발생하면 사용자 스레드를 통해 프로시저 캐시에서 실행 계획이 제거됩니다. 이때는 단일 캐시의 최대 크기와 단일 캐시의 최대 항목 수에 대한 정책을 따릅니다.

다음 예제에서는 어떤 실행 계획이 프로시저 캐시에서 제거되는지 보여 줍니다.

실행 계획은 자주 참조되므로 비용이 절대로 0이 되지 않습니다. 메모리가 부족하지 않고 현재 비용이 0이 아닌 경우 계획이 프로시저 캐시에 남아 있고 제거되지 않습니다.
임시 실행 계획이 삽입되고 메모리 부족 현상이 발생하기 전에 다시 참조되지 않습니다. 임시 계획의 현재 비용이 0으로 초기화되므로 데이터베이스 엔진에서 실행 계획을 조사할 때 그 현재 비용이 0임을 확인하고 해당 계획을 프로시저 캐시에서 제거합니다. 메모리가 부족하지 않으면 현재 비용이 0인 임시 실행 계획이 프로시저 캐시에 남아 있습니다.

단일 계획이나 모든 계획을 캐시에서 수동으로 제거하려면 DBCC FREEPROCCACHE(Transact-SQL)를 사용하십시오.

실행 계획 다시 컴파일

특정 데이터베이스 변경 시 새로운 데이터베이스 상태에 따라 실행 계획이 비효율적이거나 유효하지 않게 될 수 있습니다. 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 이상 버전에서는 기능 집합이 확장되어 더 많은 다시 컴파일 유형을 제공합니다.

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

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

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를 해제하는 방법은 통계를 사용하여 쿼리 성능 향상를 참조하십시오.