none
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
    Monday, February 06, 2012 4:16 PM

All replies

  • 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

    Tuesday, February 07, 2012 5:37 AM
  • That's fine but can I find out when a query plan was last generated and cached for a specific query?


    Patrick

    Tuesday, February 07, 2012 10:15 AM