데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

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

 

  • 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

 

No. Subject Author Date Views
» 프로시저 캐시에서 중복 쿼리 계획 확인 jevida(강성욱) 2016.10.18 3656
1952 Sp_reset_connection jevida(강성욱) 2016.10.18 3420
1951 성능분석 17탄 – SQLServer 블록킹 / 네트워크 관련 성능 카운터 jevida(강성욱) 2016.10.15 3727
1950 성능분석 16탄 – 메모리 / CPU 관련 성능 카운터 jevida(강성욱) 2016.10.15 5229
1949 성능분석 15탄 – I/O 관련 성능 카운터 jevida(강성욱) 2016.10.15 4013
1948 성능분석 14탄 – SQL Server 사용 성능 카운터 jevida(강성욱) 2016.10.15 3967
1947 성능분석 13탄 – 누락된 인덱스(missing index) jevida(강성욱) 2016.10.15 2355
1946 성능분석 12탄 – 문제 쿼리 식별 jevida(강성욱) 2016.10.15 2132
1945 성능분석 11탄 – 실행 계획 분석 jevida(강성욱) 2016.10.15 4179
1944 성능분석 10탄 – 쿼리 실행 대기 시간 분석(xevent) jevida(강성욱) 2016.10.15 1919
1943 성능분석 9탄 – 쿼리 실행 분석 jevida(강성욱) 2016.10.15 4021
1942 성능분석 8탄 – IO 통계 (DISK 활동 분석) jevida(강성욱) 2016.10.15 1468
1941 성능분석 7탄 – 프로파일러 대기 유형 및 PREEMPTIVE_OS_WRITEFILEGATHER jevida(강성욱) 2016.10.15 1613
1940 성능분석 6탄 – CPU 경합 및 동시성 관련 대기 유형 jevida(강성욱) 2016.10.15 1892
1939 성능분석 5탄 – 메모리 및 네트워크 관련 대기 유형 jevida(강성욱) 2016.10.15 1980
1938 성능분석 4탄 – 디스크 및 IO 관련 대기 유형 jevida(강성욱) 2016.10.15 2100
1937 성능분석 3탄 – 집계 대기 통계 jevida(강성욱) 2016.10.15 1924
1936 성능분석 2탄 – 실행 요청을 기다리는 작업 확인 및 분석 (병렬 처리 대기 확인) jevida(강성욱) 2016.10.15 1365
1935 성능분석 1탄 – 실행 요청을 기다리는 작업 확인 및 분석 jevida(강성욱) 2016.10.15 1998
1934 확장이벤트를 사용하여 데드락 정보 확인 jevida(강성욱) 2016.10.15 1557





XE Login