Contents
Where can you see the actual SQL code of cached procedure plans in SQL Server 2008?
How to view the SQL Server query plan cache
- sys.dm_exec_cached_plans.
- sys.dm_exec_sql_text.
- sys.dm_exec_query_plan.
How do I find the execution history of a stored procedure in SQL Server?
To view the results you can use 2 methods:
- Right click on Server Audit object and click on “View Audit Log”:
- Query the audit files directly to limit the number of columns/rows or to filter by the stored procedure name:
Which is the execution plan in SQL Server?
The Estimated Execution Plan is the compiled plan, as produced by the Query Optimizer based on estimations. This is the query plan that is stored in the plan cache. The Actual Execution Plan is the compiled plan plus its execution context.
How to clear SQL Server execution plan cache?
Each SQL Server execution plan has an age factor, the older an execution plan is it may not still be valid due to changes in the data. So, if we want to clear the plan cache, we can use DBCC FREEPROCCACHE to clear the stored plans. We can also clear the cache for a specific query or stored procedures, by using the plan handle.
When to use XML in SQL execution plan?
A SQL Server execution plan in XML format gives us information in an XML format. The XML plan format is useful when we need to send plan details to someone or if we want to save the plan and review later. An XML plan is harder to read, so it’s rarely used in real time.
When does the actual execution plan become available?
The Actual Execution Plan is the compiled plan plus its execution context. It becomes available after the query execution has completed. This includes actual runtime information such as execution warnings, or in newer versions of the Database Engine, the elapsed and CPU time used during execution.