Prior to SQL Server 2012, tracing the execution plan in SQL Server was an all or nothing approach. When using the profiler, selecting the execution plan would capture every occurrence of every execution plan for all the statements executed inside SQL Server, resulting in a bloated trace file that was difficult to analyze. To address this issue, performance templates used inside the profiler did not capture execution plans.

However, with the introduction of SQL Server 2012, a new feature called Extended Events was introduced, which provided a more efficient way to capture execution plans. One of the interesting additions to Extended Events in SQL Server 2012 is the query_post_execution_showplan event, which allows us to capture execution plans only when a query executes longer than a certain amount of time.

Why is this feature useful? It can be used to monitor overall server performance and troubleshoot when things go wrong. For example, when the server responses are slow and we have not identified any particular query causing the issue, we can choose to capture all the execution plans that consumed CPU time or duration exceeding a certain threshold.

To configure the query_post_execution_showplan event in Extended Events, we can use the Extended Events UI in SQL Server Management Studio. To access the UI, go to the Management Node, then Extended Events, and select “New Session…”. In the Event library, search for “showplan” and select the query_post_execution_showplan event. Note that the cpu_time and duration fields are available for filtering. You can specify the threshold for cpu_time or duration and add additional filters if needed.

If you prefer using T-SQL instead of the UI, you can script out the configuration using the following command:

CREATE EVENT SESSION [CPU_XEvent] ON SERVER 
ADD EVENT sqlserver.query_post_execution_showplan (
    ACTION ( package0.process_id , sqlserver.database_name , sqlserver.nt_username , sqlserver.sql_text )
    WHERE ( [duration] >= 10000000 )
)
GO

It is important to use caution when collecting every single event using Extended Events, as it can impact server performance. Additionally, if you are performing these actions on a live server, make sure to enable the Extended Event and use the “watch live data” feature in SSMS for real-time monitoring.

If you have used other events of Extended Events in your environment, I would love to hear about your experiences and how you have utilized them for different scenarios.

Overall, the query_post_execution_showplan event in SQL Server Extended Events provides a powerful tool for capturing execution plans based on specific criteria, allowing for more efficient performance monitoring and troubleshooting.