프로시저 캐시에서 중복 쿼리 계획 확인

프로시저 캐시에서 중복 쿼리 계획 확인

 

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

 

SQL Server에서 쿼리를 실행하면 실행 계획이 생성되며 이는 캐시에 저장 된다. 동일한 쿼리가 호출 될 경우 캐시에서 실행 계획이 재사용 된다. 실행계획이 중복되는 경우는 저장 프로시저에서 외부에서 만든 임시 테이블을 참조하는 경우이다. 외부 임시 테이블을 참조하는 프로시저를 호출하는 경우 실행 계획 캐시는 SPID(프로세스ID)가 키에 추가되어 생성되어 같은 세션에서 다시 실행 될 때 저장 프로시저에 대한 계획이 재사용 된다.

 

다음 스크립트는 중복 계획을 확인하는 방법이다.

-- Look and see if there is any hash bucket with a large number of entries (> 20) -- which may cause slower lookup of entries select p1.* from sys.dm_exec_cached_plans p1 join (select bucketid, count(*) as cache_entries, count(distinct plan_handle) as distinct_plans from sys.dm_exec_cached_plans p group by bucketid having count(*) > 20) as p2 on p1.bucketid = p2.bucketid

 

 

-- Save all of the "duplicate" plans for this specific query in a table in tempdb select     qs.sql_handle,     qs.statement_start_offset,     qs.statement_end_offset,     qs.creation_time,     qs.execution_count,     qs.plan_generation_num,     p.* into tempdb..DuplicateCachePlans from sys.dm_exec_query_stats qs     join sys.dm_exec_cached_plans p on qs.plan_handle = p.plan_handle where qs.sql_handle = 0x0500FF7F98FFFFFF506126F90300000001000000000000000000000000000000000000000000000000000000     and qs.query_hash = '0x00000003F9266060'     select p.plan_handle, pa.* from tempdb..DuplicateCachePlans p cross apply sys.dm_exec_plan_attributes (p.plan_handle) as pa

 

 

[참고자료]

http://blogs.msdn.com/b/psssql/archive/2014/04/03/i-think-i-am-getting-duplicate-query-plan-entries-in-sql-server-s-procedure-cache.aspx

 

 

2014-04-08 / 강성욱 / http://sqlmvp.kr

 

 

SQLSERVER, mssql, SQL튜닝, SQL강좌, DB튜닝, 쿼리튜닝, 데이터베이스튜닝, 프로시저캐시, 플랜캐시, 실행계획, sys.dm_exec_cached_plans, sys.dm_exec_query_stats