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?
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?
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.
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
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?
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.
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.
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.
19 people are following this question.
Year and Month aggregation in same Pivot table in SQL Server
SQL Server Query for Searching by word in a string with word breakers
How to show first row group by part id and compliance type based on priorities of Document type?
Query to list all the databases that have a specific user
T-sql query to find the biggest table in a database with a clustered index