프로시저 캐시 분석 쿼리

링크 : 

1. 1. 데이터 파일의 구조
2. 2. DBMS 메모리 구조
3. 3. 인덱스 기본
4. 4. 정렬을 제거하는 방법
5. 5. 프로시져 캐시 경합 확인하기
6. 6. Loop 쿼리 제거하기
7. 7. 튜닝 결과 샘플 보기

-- 프로시저 캐시 경합을 모니터링 할 수 있는 정보를 출력한다.

SELECT text, objtype, refcounts, usecounts, size_in_bytes, disk_ios_count, context_switches_count, 

           pages_allocated_count, original_cost, current_cost 

FROM sys.dm_exec_cached_plans p

CROSS APPLY sys.dm_exec_sql_text(plan_handle) 

JOIN sys.dm_os_memory_cache_entries e

ON p.memory_object_address = e.memory_object_address 

WHERE cacheobjtype = 'Compiled Plan' AND type in ('CACHESTORE_SQLCP', 'CACHESTORE_OBJCP')

컬럼을 노출하지 않기 위해 블라인드 처리한 부분들이 있지만 해당 내용은 동일한 동작을 하는 쿼리가 리터럴 SQL로 작성되어 프로시저 캐시를 서로 경합하면서 점유하고 있는 상태의 극히 일부분이다. 대부분 동적 쿼리를 만들면서 바인딩 변수를 매핑하지 않아 생기는 버그들이다. 아니면 관리툴에서 날짜를 스트링으로 처리하던가하는 경우도 있다.

text와 objtype를 참고하여 캐시 경합을 발생 시키는 쿼리들을 찾아서 문제를 제거하자.

-- 실행계획 캐시를 비운다.

dbcc freesystemcache('SQL Plans');

go

쿼리 수정 후 문제가 해결되었는지 확인하기 위해 프로시저 캐시의 실행계획을 삭제한다. 일시적으로 하드파싱이 일어나는 성능 저하가 발생하지만 어차피 우리가 원하는 일이다.

다시 프로시저 캐시를 모니터링 하면서 실행계획이 재사용 가능한지 adhoc 쿼리가 또 발생하는지 등 원하는 결과가 나올 때까지 개선하도록 한다.

관련 링크 : 

- sys.dm_exec_cached_plans

- sys.dm_os_memory_cache_entries

- freesystemcache