SQL Server/SQL Server Tip

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

SungWookKang 2015. 7. 23. 10:05
반응형

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

 

  • 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

반응형