SQL Server: Get cached plans

If you ever wanted to see which SQL plans are cached and how many times called, you can use the following query:

SELECT [cp].[refcounts] ,
[cp].[usecounts] ,
[cp].[objtype] ,
[st].[dbid] ,
[st].[objectid] ,
[st]. ,
[qp].[query_plan], [cp].[plan_handle]
FROM sys.dm_exec_cached_plans cp
	CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
	CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
	INNER JOIN sys.[databases] d ON st.[dbid] = d.[database_id]
WHERE d.[name] LIKE 'YOUR_DATABASE_NAME'
ORDER BY [cp].[usecounts] DESC

Provided plan handle can be used in

DBCC FREEPROCCACHE

as parameter to remove just selected sql plan from the cache.

Leave a Reply

Your email address will not be published. Required fields are marked *