lundi 6 février 2012 16:16
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.
Toutes les réponses
mardi 7 février 2012 05:37Modérateur
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)
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.
TechNet Community Support
mardi 7 février 2012 10:15
That's fine but can I find out when a query plan was last generated and cached for a specific query?