sqlserver.query_post_execution_showplan Performance Impact - by Jonathan Kehayias

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


28
0
Sign in
to vote
ID 732870 Comments
Status Closed Workarounds
Type Bug Repros 3
Opened 3/23/2012 8:29:22 AM
Access Restriction Public

Description

The sqlserver.query_post_execution_showplan event as currently implemented has a significant impact to the performance of a SQL Server.  Even with a predicate definition that would prevent the event from firing, it drags down server performance by 21% based on a repeated set of tests.  If the expense for this event is the collection of the showplan_xml column, it should be changed to be a Customizable column, allowing the predicate evaluation to short circuit the expensive collection of the data.  Ideally, this event wouldn't have a significant impact to a production environment when filtered to minimize its execution in the server.
Sign in to post a comment.
Posted by Jonathan Kehayias on 3/16/2013 at 7:37 PM
Hey Susan, Actually, it would be better if this would be implemented as an action in Extended Events as requested in my first Connect item (http://connect.microsoft.com/SQLServer/feedback/details/648351/extended-events-action-to-collect-actual-execution-plan) which would only incur the expense of collecting the data after the event was guaranteed to fire. I know that this has some design challenges, but it would be the best way of being able to collect this information with minimized overhead to the workload on the server. The ability to collect the actual plan is something that would help many troubleshooting scenarios, but the overhead associated with doing it through this event is unreasonable for production usage.
Posted by Microsoft on 8/24/2012 at 4:17 PM
Hello Jonathan,

There are two different issues here: (1) The ability to prevent data collection for short-running queries and (2) the cost (effect on performance) of collecting the data. While I can see that a solution for (1) would be useful, in our current design there is no way to prevent data collection for short-running queries because the query duration is not known ahead of time. Once the option to collect the execution plan is set, the data will be collected for every query, even if the filter results in the data not being shown.

The effect on query performance of collecting the plan will depend on the cost of the query itself. For short running queries, the percent reduction in performance will be much higher than for long running queries. There is a fixed overhead per query in addition to cost that is roughly proportional to the complexity of the query plan. You would be unlikely to see a 21% reduction in performance for a decision support query that scans a lot of data and does multiple joins, for example.

I can see that reducing the performance effect in general (i.e. addressing the second issue) would be useful. At this point, we do not have plans to address the performance impact of collecting the execution plans. However, we will keep it in mind for a future release.

Again, thanks for the feedback.

Susan Price
Senior Program Manager
SQL Server Database Engine
Posted by Jonathan Kehayias on 8/24/2012 at 9:06 AM
So are you saying that there is nothing that can be done to improve the performance impact that this event has on the engine and a 21% reduction in performance for a single event is By Design? I know you guys have pressure to close items but this is ridiculous.
Posted by Microsoft on 8/24/2012 at 8:59 AM
Hello Jonathan,

SQL Server has to collect all the information for the showplan event before knowing that the duration will not exceed the predicate. By the time duration is known, it is too late to "undo" the performance effect of collecting the data.

I am going to resolve this connect item as "by design." Please do not hesitate to respond if you have questions or believe the item was closed prematurely.

Thank you for your feedback,

Susan Price
Senior Program Manager
SQL Server Database Engine
Posted by Jonathan Kehayias on 8/23/2012 at 4:22 PM
Hey Susan,

I used a filter on duration since this column was added to this event based on another connect item I submitted during the early CTP's of 2012, and I am very interested in being able to use the event to collect information about cases where execution exceeds the expected duration. I set it to be > 60 seconds which equates to duration > 60000000 in the session predicate. The workload I tested with has no queries that exceed 2-5 seconds in it and no showplan events were generated in the output event_file files for the session. The performance impact under load was explained in the details, 21% reduction in batch requests a second by simply adding the event with a predicate that doesn't allow it to fire. Aaron was able to corroborate it with a completely different workload from what I was testing. You have to drive high concurrent work at the server, I personally ran Distributed Replay across 4 clients with a separate controller, against a dedicated SQL Server to generate my repeatable workload in stress mode.
Posted by Microsoft on 8/23/2012 at 3:55 PM
Hello Jonathan,

We are trying to understand your scenario so that we can repro it. What predicate are you using to try to prevent the event from firing? Can you send a script of what you did so that we can reproduce it and investigate more deeply?

Thank you,

Susan Price
Senior Program Manager
SQL Server Database Engine
Posted by AaronBertrand on 3/23/2012 at 11:01 AM
I experienced the same magnitude of impact on a workload I tested with and without the post_execution showplan event. I had high hopes that this might finally be an efficient method to capture the actual plan along with the execution of a query, but a ~20% hit to performance is not justifiable.