Execution Plans

I’ve been delving deep into the world of execution plans recently and I’ve come up with the following two queries which are quite handy for viewing your current execution plans stored in your plan cache. I’ve sporadically used it to look through and familiarise myself with execution plans in general and also so I can look into optimising any existing stored procedures or my ad-hoc queries and reports.

--QUERY to list all execution plans for your Stored Procedures 
SELECT sp.name, qp.query_plan, CP.usecounts, cp.cacheobjtype, cp.size_in_bytes, cp.usecounts, SQLText.text
FROM sys.dm_exec_cached_plans AS CP
CROSS APPLY sys.dm_exec_sql_text( plan_handle)AS SQLText
CROSS APPLY sys.dm_exec_query_plan( plan_handle)AS QP
INNER JOIN sys.procedures AS sp
ON qp.objectId = sp.object_Id
WHERE objtype = 'Proc' AND cp.cacheobjtype = 'Compiled Plan'

This next one is for ad-hoc queries which depending on when you last restarted your SQL Services or cleared out your cache, could be quite large or quite small!

--Query to List all execution plans for ad-hoc queries (depending on the last time SQL server was restarted, this could be quite big!)
SELECT qp.query_plan, CP.usecounts, cp.cacheobjtype, cp.size_in_bytes, cp.usecounts, SQLText.text
FROM sys.dm_exec_cached_plans AS CP
CROSS APPLY sys.dm_exec_sql_text( plan_handle)AS SQLText
CROSS APPLY sys.dm_exec_query_plan( plan_handle)AS QP
WHERE objtype = 'Adhoc' AND cp.cacheobjtype = 'Compiled Plan'