확인 내용
* 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 *******************
**/
