Extended Events Action to collect actual execution plan - by Jonathan Kehayias

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 648351 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 2/28/2011 9:25:03 AM
Access Restriction Public


Denali CTP1 added an Event to collect te actual execution plan using Extended Events, but this would be very useful as an action.  A good use case for this would be:

I have a parameter sensitive procedure that when statistics get sampled or skewed, plan generation under a sub-optimal value will cause a bad plan that throws off execution time drastically.  I could create an Event Session like:

ADD EVENT sqlserver.sp_statement_completed(
 ACTION (sqlserver.execution_plan)
 WHERE (object_id = 10334249058 AND package0.greater_than_max_int64(duration,10000)) 
Where I set the baseline duration and can collect the execution_plan when it skews so I have the skewing value as well.  I definitely see how this as an Action could sink a system because people don't pay attention to what they are doing, but it would have an amazing benefit under specific implementations as well. 
The problem with the query_post_execution_showplan Event is it doesn't return the duration.  If it could return duration, it would be possible to make this work as well.
Sign in to post a comment.
Posted by Microsoft on 5/2/2011 at 1:07 PM
We have added "duration" and "cpu_time" columns to query_post_execution_showplan event as the result of this request. This corresponds to option #3 in my first reply on this item.

The change should appear in the Denali release. Just FYI, most likely, it won’t appear in the next public CTP but should be in the released/release candidate versions.

Thank you for your support of this feedback item!

Posted by Microsoft on 3/10/2011 at 4:36 PM
@Rob: This is Denali+ request due to significant changes in number of exposed events in Denali (the events we are discussing supporting data you would normally get in SQL Trace) - it is highly unlikely we will port this to earlier releases.

@all: Regarding delivery vehicle: request like this could appear in a CU/PCU (aka service pack) of Denali but let's see if I can make this happen in Denali.

Posted by Jonathan Kehayias on 3/9/2011 at 9:03 PM

It is incredibly disappointing to know that this won't likely be possible for another entire release cycle of SQL Server. The benefits of this in troubleshooting performance problems is unprecedented in any currently available.
Posted by Rob Farley on 3/9/2011 at 9:02 PM
Hi Boris,

I think this is an extremely useful feature for those few times when a QO bug causes a query to execute poorly. This is sometimes something that can be determined by comparing the Estimated Number of Rows with the Actual Number of Rows at various points in the plan. To do this comparison, the plan must be able to be grabbed through XE. It's no good trying to run it later, because the particular conditions which caused the QO bug to play its part may not occur when the DBA is investigating.

Hopefully this is something that can be added in a Hotfix? I'd be disappointed to see it as a Denali-only feature, as older systems need it too.

Posted by Microsoft on 3/9/2011 at 4:47 PM
Thank you Aaron and Jonathan for the comments! Jonathan you did give me more info on what I was looking for.

I do not dispute general usefulness of the proposal #1 below.

Let me explain my side of things – we can make a number of implementation choices even when we do exactly #1 below – these choices are made based on our knowledge/assumptions on how the information is going to be used (or abused). This discussion helps support or deny some of them.

Besides that, there may be other ways to solve the same problem which may be cheaper implementation-wise, and/or faster, and/or happen sooner and/or cover more cases in terms of troubleshooting.

In any case, the feedback is taken. Unfortunately, at this point in time, it is very unlikely this (or something similar to this) will happen in “Denali”.

Posted by AaronBertrand on 3/9/2011 at 2:50 PM
Yes, once we have the plan we can study it to try to determine why it exceeded typical runtimes. In the best case we can compare it to a previous or the current version of the plan (which did not exceed the runtime).
Posted by Jonathan Kehayias on 3/9/2011 at 12:01 PM
I am not sure I understand the question. I have the plan so I now have the information I need to make decisions about what is causing a particular stored procedure or statement to skew, and I can take action on that information. What I would do depends entirely on the situation that I find from the information collected. It could be hinting the query, or it could be changing my statistics sample ratio, disabling Auto-Update Stats on the object in question and scheduling fullscan updates of the statistics, or even changing my code so that I don't encounter a parameter sensitivity issue. There is a lot that we can do with the additional information that the Actual plan provides.
Posted by Microsoft on 3/9/2011 at 11:49 AM
Thanks for additional comments! Now let's develop the scenario further.

Let's say we go with #1, you got the plan - now what?

Posted by Jonathan Kehayias on 3/8/2011 at 6:08 PM
Numbers 1 and 3 seem like the only really viable options because I want the plan that executed to cause the problem, specifically for parameter sensitive queries. Number 2 is just the query fingerprints, and number 4 is only going to give me the cached plan and not the actual. Number 1 as a customizable column would be the best, but not just for sp_statement completed, sql_statement_completed, and the other plan generating events as well.
Posted by Microsoft on 3/8/2011 at 5:48 PM
Hi Jonathan,

Thank you for your suggestion!

There are some design issues with creating "execution plan" as an action. There are few alternative ideas, depending on the goals:

1. SQL Server can have optional “execution plan” column in sp_statement_completed (see collect_object_name and collect_statement as an example). Collecting it with filter will make it a bit more expensive than pure action but still should do the job efficiently
2. SQL Server can have action for other plan identifying entities (see http://connect.microsoft.com/SQLServer/feedback/details/635095/) and use that info to act further – the referenced DCR is likely to appear in the next released version of SQL Server.
3. SQL Server to have duration (and other execution stats?) on query_post_execution_showplan (this is very similar to #1)
4. User can create an app on top of the existing event (sp_statement_completed) and pull the plan from plan cache when event fires (this you can do today and this is the least convenient option).

Would #2 work for you? Do you need the plan primarily to have the values of parameters?

Please let me know if you have any additional comments on this.

Thank you and regards,
Boris Baryshnikov.
SQL Server Engine