mango bio photo

mango

Que sera sera

Email Facebook

SQL Server Cached Plan

SQL Server Execution Plan Cache란?

쿼리, 프로시저, Prepared Statement의 플랜을 저장해놓은 저장소

전체 Cached Plan 용량

select sum(convert(bigint, size_in_bytes))/ 1048576 [size_in_MB]  from sys.dm_exec_cached_plans

TYPE별 Cached Plan 용량

SELECT objtype, count(*) num_of_plans, sum(convert(numeric, size_in_bytes)) / 1048576 [size_in_MB]
FROM (
     SELECT objtype, query_hash, query_plan_hash, text,size_in_bytes FROM sys.dm_exec_cached_plans AS cp
     JOIN sys.dm_exec_query_stats AS qs ON cp.plan_handle = qs.plan_handle
     CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
) T
GROUP BY objtype

TYPE별 쿼리별 Cached Plan 개수 및 용량

SELECT objtype,query_hash, query_plan_hash, COUNT(*), MAX(text), sum(convert(numeric, size_in_bytes)) / 1048576 [size_in_MB]
FROM (
     SELECT objtype,query_hash, query_plan_hash, text,size_in_bytes FROM sys.dm_exec_cached_plans AS cp
     JOIN sys.dm_exec_query_stats AS qs ON cp.plan_handle = qs.plan_handle
     CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
) T
GROUP BY objtype,query_hash, query_plan_hash
ORDER BY COUNT(*) DESC

Reference