SQL Server 쿼리 처리 아키텍처_실행 계획 캐싱 및 다시 사용

SQL Server 쿼리 처리 아키텍처_실행 계획 캐싱 및 다시 사용

 

• Version : SQL Server 2005, 2008, 2008R2, 2012

 

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

 

 

 

SQL Server 실행계획은 쿼리 계획과 실행 컨텍스트로 나눌 수 있다.

• 쿼리 계획 : 대량 실행 계획은 여러 사용자가 사용하는 재진입용 읽기 전용 데이터 구조이다. 이것을 쿼리 계획이라 한다. 쿼리 계획에는 사용자 컨텍스트가 저장되지 않는다. 메모리에는 쿼리 계획의 복사본이 두 개까지만 존재 할 수 있다. 즉 모든 직렬 실행을 위한 복사본과 모든 병렬 실행을 위한 복사본이 각각 하나씩 있을 수 있다. 병렬 복사본은 병렬처리 수준에 관계없이 모든 병렬 실행에 적용 된다.

 

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

 

 

 

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

 

캐시에 사용되지 않은 기존 실행 계획과 새 SQL 문을 대응시키는 알고리즘을 적용하려면 모든 개체 참조가 정규화 되어야 한다. 예를 들면 다음과 같다.

SELECT * FROM Person;   SELECT * FROM Person.Person;

 

 

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

실행 계획은 충분한 메모리가 있는 한 프로시저 캐시에 계속 남아 있을 수 있다. 메모리의 여유가 많지 않으면 데이터베이스 엔진에서는 비용기반 방법을 사용하여 프로시저 캐시에서 어떤 실행 계획을 제거할지 결정 한다.

비용 기반 결정을 내리기 위해 데이터베이스 엔진에서는 다음 요인에 따라 각 실행 계획에 대한 현재 비용 변수를 늘리거나 줄인다.

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

 

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

 

데이터베이스 엔진은 메모리의 여유공간이 충분히 생길 때 까지 반복적으로 검사하여 실행 계획을 제거 한다. 그 결과 메모리가 부족한 상태에서 실행 계획의 비용이 여러 차례에 걸쳐 증감 할 수 있다. 충분한 메모리가 확보되면 데이터베이스 엔진은 현재 실행계획의 비용을 더 이상 줄이지 않으며 해당 비용이 0인 계획을 포함(임시 실행 계획 포함)하여 모든 실행 계획이 프로시저 캐시에 계속 남는다.

 

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

 

모든 캐시의 최대 크기는 버퍼 풀 크기에 따라 결정되며 최대 서버 메모리를 초과할 수 없다. 서버의 최대 메모리 설정은 아래 링크를 참고 한다.

MN/MAX Server Memory : http://sqlmvp.kr/140167266577

 

 

[캐시에서 모든 실행 계획 수동 제거]

• 캐시에서 SQL 핸들 지정 하여 삭제

DBCC FREEPROCCACHE (0x060006001ECA270EC0215D05000000000000000000000000);

 

• 캐시에서 전체 삭제

DBCC FREEPROCCACH

 

• 리소스 풀에 연결된 모든 캐시 항목 삭제

DBCC FREEPROCCACHE ('default');

 

 

[실행 계획 다시 컴파일]

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

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

 

 

SQL Server 2000에서는 재컴파일이 발생 할 때 마다 전체 일괄 처리가 다시 컴파일 되었지만 2005부터는 일괄 처리 내의 문만 재컴파일 한다. 재컴파일은 CPU 사용 증가 및 잠금과 관련하여 성능 저하를 일으키는 요소가 된다.

 

프로파일러에서 SP:Recompile 및 SQL:StmtRecompile의 EventSubClass열에는 다시 컴파일 하는 이유를 나타내는 정수 코드가 포함된다.

EventSubClass	설명
1	스키마 변경
2	통계 변경
3	지연된 컴파일
4	SET 옵션 변경
5	임시 테이블 변경
6	원격 행 집합 변경
7	FOR BROWSE 권한 변경
8	쿼리 알림 환경 변경
9	분할 뷰 변경
10	커서 옵션 변경
11	OPTION(RECOMPILE) 요청

 

 

 

 

[참고 자료]

실행 계획 캐싱 및 다시 사용 : http://msdn.microsoft.com/ko-kr/library/ms181055(v=sql.105).aspx

SQL Server Memory Pool : http://msdn.microsoft.com/en-us/library/aa224760(v=sql.80).aspx

Plan Caching In SQL Server 2008 :

http://msdn.microsoft.com/en-us/library/ee343986(v=sql.100).aspx

 

 

2013-05-09 / 강성욱 / http://sqlmvp.kr