Stored Procedure Cached Execution Plan

Question

Is there a way of querying the DMV’s to find out when an execution plan of a stored procedure was cached?

I had a very slow performing query that I believe has been resolved by the query plan being invalidated and rebuilt but I would like to know for sure if this was the case.

---

Patrick

Answer 1

Hi Patrick,

There are many ways to capture the execution plan for the stored procedure. From SQL Server Management Studio, you can directly enable ‘Include Actual Execution Plan’ or set SHOWPLAN options before you execute the stored procedure. Also, you can make use of SQL Server Profiler to capture it. As for DMVs, you can issue the following query to return the execution plan:

SELECT UseCounts, Cacheobjtype, Objtype, TEXT, query_plan 
FROM sys.dm_exec_cached_plans  
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
CROSS APPLY sys.dm_exec_query_plan(plan_handle) 

For more information:
How do I obtain a Query Execution Plan?
Obtaining Query Execution Plans Through SQL Profiler Traces

And this book can be helpful to analysis the execution plan and find out the cause to the performance issue: SQL Server Execution Plans.

TechNet Subscriber Support
If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

---

Stephanie Lv

TechNet Community Support

Answer 2

That's fine but can I find out when a query plan was last generated and cached for a specific query?

---

Patrick