Whenever a query is run for the first time in SQL Server, it is compiled and a query plan is generated for the query. Every query requires a query plan before it is actually executed. This query plan is stored in SQL Server query plan cache. This way when that query is run again, SQL Server doesn’t need to create another query plan; rather it uses the cached query plan which improved database performance
Whenever a query is run for the first time in SQL Server, it is compiled and a query plan is generated for the query. Every query requires a query plan before it is actually executed. This query plan is stored in SQL Server query plan cache. This way when that query is run again, SQL Server doesn’t need to create another query plan; rather it uses the cached query plan which improved database performance.
The duration that a query plan stays in the plan cache depends upon how often a query is executed. Query plans that are used more often, stay in the query plan cache for longer durations, and vice-versa.
How to view the SQL Server query plan cache
SQL Server provides the following dynamic management views and functions that can be used to find out what is in the plan cache at any given time.
- sys.dm_exec_cached_plans
- sys.dm_exec_sql_text
- sys.dm_exec_query_plan
Let us use these functions and views to see what is in the SQL Server cached query plan. Execute the following query on your SSMS (SQL Server Management Studio):
SELECT cplan.usecounts, cplan.objtype, qtext.text, qplan.query_plan FROM sys.dm_exec_cached_plans AS cplanCROSS APPLY sys.dm_exec_sql_text(plan_handle) AS qtextCROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qplanORDER BY cplan.usecounts DESC
Clearing the plan cache
To clear the plan cache
DBCC FREEPROCCACHE
Ramalakshmi's Tech Blog 