Examining the procedure cache for queries and execution plans

This query below gives the list of cached plans and their corresponding SQL statements. The usecount in this case is greater than one but it can be changed depending on the need. On clicking the hyper link in the query_plan column, SSMS will display the execution plan. The query skips system databases along with Distribution and RS databases

Note: The cache gets cleared when SQL Server is re-started or when DBCC FREEPROCCACHE is run. This will reset the procedure cahe


SELECT DB_NAME(dmqp.[dbid]) AS [database] , dmep.usecounts , dmep.size_in_bytes/1024 AS size_in_KB , dmep.cacheobjtype , dmep.objtype , dmst.text , dmqp.query_plan FROM sys.dm_exec_cached_plans dmep CROSS APPLY sys.dm_exec_query_plan(plan_handle) dmqp CROSS APPLY sys.dm_exec_sql_text (plan_handle) dmst WHERE dmqp.[dbid] > 4 AND DB_NAME(dmqp.dbid) NOT IN ('distribution', 'reportserver', 'reportservertempdb') AND dmep.usecounts > 1 ORDER BY dmep.objtype

SELECT DB_NAME(dmqp.[dbid]) AS [database]
, dmep.usecounts
, dmep.size_in_bytes/1024 AS size_in_KB
, dmep.cacheobjtype
, dmep.objtype
, dmst.text
, dmqp.query_plan
FROM sys.dm_exec_cached_plans dmep
CROSS APPLY sys.dm_exec_query_plan(plan_handle) dmqp
CROSS APPLY sys.dm_exec_sql_text (plan_handle) dmst
WHERE dmqp.[dbid] > 4
AND DB_NAME(dmqp.dbid) NOT IN ('distribution', 'reportserver', 'reportservertempdb')
AND dmep.usecounts > 1
ORDER BY dmep.objtype