How to retrieve actual query plan for a specific stored procedure using Extended Events?

ScottM 331 Reputation points
2021-09-22T21:04:29.153+00:00

Is there an example of how to define an extended event that will capture the actual query plan for a single / specific stored procedure each time it executes?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,343 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,599 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 106.8K Reputation points
    2021-09-22T21:58:48.623+00:00

    You could probably to this by setting up an event session with the appropriate filters.

    BUT DON'T DO THIS! At least not in production.

    If you create an event session with any of the events that captures the actual execution plan, all process will start to generate query plans, no matter the filtering. This is because the payload for the event is created first, before the filtering.

    When I have tested this with a workload that runs an optimised cursor (that is, many, short-running statements), I have seen performance penalty of a factor 12!

    If you really need to do this, use good ol' Trace instead. In my tests, the performance penalty was "only" a factor of 3.

    What is the actual problem you are trying to solve.

    1 person found this answer helpful.

3 additional answers

Sort by: Most helpful
  1. Erland Sommarskog 106.8K Reputation points
    2021-09-23T21:52:43.48+00:00

    Possibly expensive stored procedure (the original app author has moved on) and would like to view the actual query plan for a single stored proc in context of the running app.

    Makes sense.

    But as I said, it is not really recommendable. It is better to figure out exactly which SET options the application uses, and then run from SSMS. You can use the plan cache queries that Olaf posted to verify that you get the right options. To wit, I would expect that the entry used by the application has the highest execution_count. (Assuming that it is a static query without OPTION (RECOMPILE).

    If you are on SQL 2019, there is another option. You can do:

    ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON
    

    Once the application has executed the procedure, you can use a variation of this query to get the actual plan:

    SELECT qp.query_plan
    FROM   (SELECT DISTINCT plan_handle, sql_handle FROM sys.dm_exec_query_stats) qs
    CROSS  APPLY sys.dm_exec_sql_text(qs.sql_handle) est
    CROSS  APPLY sys.dm_exec_query_plan_stats(qs.plan_handle) qp
    WHERE  est.objectid  = object_id (@procname)
      AND  est.dbid      = db_id(@dbname)
    

    A friend said to do this using extended events instead of profiler because profiler is slow and deprecated.

    Indeed, tracing with Profiler directly can be a true disaster. Most often you should run the trace server-side. Although, for this particular case, don't think there would be any difference, since the number of events are small.

    And, also true for many events, extended events has lower overhead than Trace. But in the particular case of actual execution plans, X-Events is unbelievably expensive.

    1 person found this answer helpful.
    0 comments No comments

  2. Olaf Helper 43,166 Reputation points
    2021-09-23T06:17:13.607+00:00

    view the actual query plan for a single stored proc ...

    Why not querying the cached execution plans?

    SELECT databases.name,
        dm_exec_sql_text.text AS TSQL_Text,
        dm_exec_query_stats.creation_time, 
        dm_exec_query_stats.execution_count,
        dm_exec_query_stats.total_worker_time AS total_cpu_time,
        dm_exec_query_stats.total_elapsed_time, 
        dm_exec_query_stats.total_logical_reads, 
        dm_exec_query_stats.total_physical_reads, 
        dm_exec_query_plan.query_plan
    FROM sys.dm_exec_query_stats 
    CROSS APPLY sys.dm_exec_sql_text(dm_exec_query_stats.plan_handle)
    CROSS APPLY sys.dm_exec_query_plan(dm_exec_query_stats.plan_handle)
    INNER JOIN sys.databases
    ON dm_exec_sql_text.dbid = databases.database_id
    

    ... in context of the running app

    Do you think every app gets it's own execution plan for a SP?


  3. CathyJi-MSFT 21,126 Reputation points Microsoft Vendor
    2021-09-23T06:55:26.443+00:00

    Hi @ScottM ,

    Check if below MS blog could help you.

    Using xEvents to capture an Actual Execution Plan


    If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar thread.

    0 comments No comments