mssql procedure plan 이슈 확인 ( adhoc 쿼리가 plan을 재사용 하지 못하고 과도하게 단일 plan이 생성되어, 주기적 캐시 초기화 발생 )

확인 내용

* procedure가 올바른 plan을 선정하였는지

* cached plan 사이즈가 효율적으로 사용되고 있는지

* plan에 해당하는 query확인

 

 

Procedure query_id 및 plan_id 확인

하나의 SP에 여러 plan이 존재할수 있음 

그중 비효율적인 plan을 사용하는 경우 이슈가 발생

--'spTest_get' SP를 분석

select qsq.query_id, qp.plan_id, qst.query_sql_text, qp.query_plan 
from sys.query_store_query qsq
inner join sys.query_store_query_text qst
	on qsq.query_text_id = qst.query_text_id
inner join sys.query_store_plan qp
	on qp.query_id = qsq.query_id
where object_id = object_id('spTest_get')

/**
query_id	plan_id
5324	662
5324	782
5324	787
5324	807
5324	818

3315	663
8092	1200
8093	1201
**/

Cached plan 카운트 및 사이즈

adhoc 쿼리를 과도하게 호출 시 cached plan이 저장 가능한 공간을 초과 할 수 있음.

아래의 경우 1회만 사용되는 plan이 대부분인 케이스

1회만 사용되는 plan을 sp_executesql 형태로 변경하거나 동일 plan을 사용하도록 유도가 필요함

-- 모든 cached plan을 조회

select objtype, count(*) as cnt, sum(convert(bigint, size_in_bytes))/(1024*1024) as size_mb
from sys.dm_exec_cached_plans
--where usecounts = 1
group by objtype
order by cnt desc

/**
objtype	cnt	size_mb
Adhoc	156329	6906			<--
Prepared	3708	967
View	1285	181
Proc	215	253
**/


-- cached plan 사용 횟수가 1회 경우만 조회
select objtype, count(*) as cnt, sum(convert(bigint, size_in_bytes))/(1024*1024) as size_mb
from sys.dm_exec_cached_plans
where usecounts = 1
group by objtype
order by cnt desc

/**
objtype	cnt	size_mb
Adhoc	153763	1889			<--
Prepared	3145	242
Proc	41	19
**/

query_hash 통한 이슈 query 확인

이슈 query 확인 후 수정

-- 실행 count가 1인 경우만 조회

select query_hash, count(*) as cnt
from sys.dm_exec_query_stats
where execution_count = 1
group by query_hash
order by cnt desc

/**
query_hash	cnt
0xD1CC06V33C082806	135420      <--- 확인
0x3129830A6071HND2	2108
0x30E30C19A239CX39	218
0x690CY4EC5110G475	218
~
**/


-- 이슈 query text 확인

select top 100 st.text
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text (qs.sql_handle) as st
where qs.query_hash = 0xD1CC06V33C082806 

/**
USE master   INSERT INTO *******************
USE master   INSERT INTO *******************
**/