제목 : 프로시저 캐시의 특정 타입만(SQLCP) 초기화 시키자.

64bit환경에서의 native한 대용량 메모리 지원으로 프로시저 캐시 영역도 따라 늘어났습니다.

분명 이러한 변화된 부분은 이전의 32bit 환경 보다 보다 좋은 성능을 가져올 수 있게 해주었습니다.
하지만, 프로시저 캐시 크기가 증가됨에 따라 예전에 보기 힘들었던 문제가 발생할 수가 있습니다.

아래의 표로 프로시저 캐시의 공간을 계산 할 수 있습니다.
만약 8GB의 가용 메모리를 가지고 있는 SQL Server 2005 SP2 시스템인 경우 대략 3.4GB를 프로시저 캐시로 사용할 수 있습니다.
많은 ad-hoc쿼리가 발생되어 이 공간 메모리를 모두 사용하고 있고, 저장된 계획을 재사용하지 못한다면  쓸데없는 메모리 낭비 및 계속 컴파일이 발생하게 됩니다. 이것은 전체적인 시스템으로 본다면 비효율적이라고 볼 수 있습니다.

위와 같은 환경이라면 당연히 SP 등을 이용하여 컴파일된 실행계획을 가지게 모든 쿼리를 변경하는 것을 고려해야 합니다.

하지만 지금 당장 할 수 있는 작업이 아니기에, 다른 방법을 찾아봐야 합니다.

<SQL 버전별 최대 프로시저 캐시 메모리 크기 구하기>

다른 방법에 대해 찾기 위해서는 우선 프로시저 캐시 저장 구조에 대해서 조금 알아 볼 필요가 있습니다. 간단하게 프로시저 캐시에 대해서 알아보도록 하겠습니다.

프로시저 캐시는 아래와 같이 4가지 타입으로 저장됩니다.

l  CACHESTORE_OBJCP(Object Plans)

-       SP 함수의 컴파일된 계획

l  CACHESTORE_SQLCP(SQL Plans)

-       prepared, ad-hoc쿼리의 컴파일된 계획

l  CACHESTORE_PHDR(Bound Trees)

-       뷰, 제약조건, 기본값 등

l  CACHESTORE_XPROC (Extended Stored Procedures)

-       확장 저장 프로시저

프로시저 캐시의 대부분의 용량을 차지하는것은 CACHESTORE_OBJCP 또는 CACHESTORE_SQLCP입니다. 위에서 설명하고 있듯이 CACHESTORE_OBJCP는 SP 및 함수 입니다. 즉 매번 재 컴파일을 하지 않아도 되는 계획입니다. 하지만 CACHESTORE_SQLCP의 경우 ad-hoc쿼리이기에 매번 재컴파일 할 수 있는 가능성이 매우 크게 됩니다.

대부분 프로시저 캐시가 커져서 문제가 되는 경우는 CACHESTORE_SQLCP 크기가 늘어남으로써 문제가 되고 있습니다. 그렇다면, 재컴파일도 하지 않고 적정한 크기를 가지는 CACHESTORE_OBJCP는 그냥 두고 매번 재컴파일하여 비효율 적인 CACHESTORE_SQLCP만을 프로시저 캐시에서 초기화 하는 것이 보다 효율적인 방법일 수 있습니다.
 

우선 서버에 타입별 프로시저 캐시 크기를 메모리 관련 DMV로 확인 할 수 있습니다.

select * from sys.dm_os_memory_clerks

where type in ('CACHESTORE_OBJCP','CACHESTORE_SQLCP')

이 DBCC 구문은 모든 프로시저 캐시를 초기화 하게 됩니다.

하지만 이것은 비효율일 수 있습니다. 대부분의 메모리를 차지하고 있는건 SQLCP이고

초기화 시켜야 할 것도 SQLCP이기에, OBJCP까지 같이 초기화 되기 때문입니다.

그럼, SQLCP만 초기화 시키는 방법은?

아래를 참고 하세요.

초기화 이전에는 CACHESTORE_SQLCP의 single_pages_kb가 44088KB였으나,
DBCC FREESYSTEMCACHE 구문을 실행하면 552KB로 변경된 것을 확인 할 수 있습니다.

select * from sys.dm_os_memory_clerks

where type in ('CACHESTORE_OBJCP','CACHESTORE_SQLCP')

DBCC FREESYSTEMCACHE('SQL Plans')

select * from sys.dm_os_memory_clerks

where type in ('CACHESTORE_OBJCP','CACHESTORE_SQLCP')

다른 프로시저 캐시에 대해서도 내릴 수 있습니다.

DBCC FREESYSTEMCACHE('OBJECT Plans')

DBCC FREESYSTEMCACHE('BOUND Trees')