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

 

  • 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

 



강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

 





profile

강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

Kakao Talk : SQLMVP

Line : jevida


현재 LA에 거주하고 있으며 SQL에 관심있는 분이면 언제든 친추 환영합니다.