Execution Plan’s Plan A

So you want to make your life easier. You want a nice big whopping bonus. You want that dodgy admin who talks in korn shell to finally respect you because you can also script dammit.. Well you have come to the right place.Let’s get to the juicy bits. This script will show you: o Result Set 1: Shows you execution plans information, if you have high plan usage and lots of Adhoc queries consider changing to ‘optimize for ad hoc workloads’ o Result Set 2: Shows execution plan text that occurs often, so high usage plans o Result Set 3: Lists Stored Procedures where the high count usage plans occur. This should be pretty straight forward for performance people. (If you have ended up on this little corner of the web then surely you are serious about looking and you are rather specific about aforementioned content.)

/*-*-*-*-*-*-*-*-* ﷽‎ *-*-*-*-*-*-*-*-*-*-*-*/
SELECT objtype AS [CacheType]
, COUNT_BIG(*) AS [Total Plans]
, SUM(size_in_bytes*0.000000953613) [TotalMB]
, AVG(usecounts) AS [Avg Use Count], SUM(CAST((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 AS [Total MBs - USE Count 1]
, SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1] FROMsys.dm_exec_cached_plans GROUPBY objtype ORDER BY [Total MBs - USE Count 1] DESC
/*8888888888888888888888888888888888888888888*/
SELECT TOP 10 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], SUM(cp.usecounts) [UseCounts], 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)
HAVING SUM(cp.size_in_bytes) > 524800 --1024*512 about 0.5 MB.. small enough to discard
ORDER BY [TotalMB] DESC

I wish I could fix the stored procedures as well, but this is as close as I can get to a one click “one-button-fix-my-whole-sql-server-thingy”

Updated 2012-12-06 

Adrian