Dot Net | C#.Net | ASP.Net | SQL Server

How to check what is in SQL Server plan cache

To see what is in SQL Server plan cache we will make use of the following 3 dynamic management views and functions provided by sql server

1. sys.dm_exec_cached_plans
2. sys.dm_exec_sql_text
3. sys.dm_exec_query_plan

Use the following query to see what is in the plan cache

SELECT cp.usecounts, cp.cacheobjtype, cp.objtype, st.text, qp.query_plan

FROM sys.dm_exec_cached_plans AS cp

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp

ORDER BY cp.usecounts DESC

To remove all elements from the plan cache use the following command
DBCC FREEPROCCACHE