Show me the SP

I want to see which execution plans that are chowing my cache.

SELECT 'Heavy Utilized Plans'[ ],cp.objtype, ISNULL(sp.name,CASE
WHEN PATINDEX('%CREATE F%',LTRIM(st.text)) > 0 THEN 'Other'
WHEN PATINDEX('%CREATE P%',LTRIM(st.text)) > 0 THEN 'Other'
ELSE LTRIM(st.text)
END) [Culprit], st.text [SPText], SUM(cp.usecounts) [UseCounts], CONVERT(DECIMAL(6,5),SUM(cp.size_in_bytes)*0.000000953613) [TotalMB]
FROM sys.dm_exec_cached_plansAS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp--WHERE cp.objtype IN ('Adhoc')--, 'Prepared')
LEFT OUTER JOIN sys.procedures sp ON Sp.object_id = st.objectid
GROUP BY cp.objtype
, ISNULL(sp.name,CASE
WHEN PATINDEX('%CREATE F%',LTRIM(st.text)) > 0 THEN 'Other'
WHEN PATINDEX('%CREATE P%',LTRIM(st.text)) > 0 THEN 'Other'
ELSE LTRIM(st.text)
END), st.text
HAVING SUM(cp.size_in_bytes) > 524800 --256*512 about 0.5 MB.. small enough to discard
ORDER BY [TotalMB] DESC

Simplified.

Adrian

0 views0 comments