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

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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?

Answer 4

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.