Extended Event Query Post Execution Showplan in SQL Server

Play Extended Event Query Post Execution Showplan in SQL Server
Sign in to queue

Description

Query Post Execution Showplan event is a very useful event to find problematic queries and execution plans based on CPU usage or duration while analyzing performance issues.


It can increase the execution time of all queries by a fraction of a millisecond, irrespective of the total query cost, which means, the overhead of query_post_execution_showplan event is high for cheaper queries compared to expensive queries.


In all cases, one should enable the event on an ad hoc basis and with restrictive predicate to limit the number of events.


The below video demonstrates this with hands-on example. SQLTest Tool simulates the hands-on labs for you, no registration necessary. You can practice the online hands-on example while watching the video.

 

 

Tag:

SQL Server

Embed

Download

Download this episode

The Discussion

  • User profile image
    JMKehayias
    This is the problem with non-real world workloads and incorrect conclusions about the events overall performance impact. For this specific workload the impact may only be 2ms per statement, but the execution plan is also incredibly simple and small compared to a real world workload plan which can take much longer for generation and increases the overhead of this event. Additionally the ring_buffer is the absolute worst target to use for events that generate lots of data like the showplanxml, since the DMV can only output a 4MB XML document, and it is highly likely that the target will hold events that you have absolutely no way to access if they don't fit in the 4MB of XML serialized out of the DMV.

Add Your 2 Cents