question

ScottM-7356 avatar image
0 Votes"
ScottM-7356 asked ErlandSommarskog answered

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

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-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered ScottM-7356 edited

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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. A friend said to do this using extended events instead of profiler because profiler is slow and deprecated.

thanks

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered ErlandSommarskog commented

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?

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Olaf, that only gives the estimated plan, and Scott wanted the actual.

And, yes, apps can have different plans, if they have different SET options.

0 Votes 0 ·
Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered

Hi @ScottM-7356,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.