SQL SERVER – Filtering CPU Bound Execution Plans with Extended Events

Let me take a tour to what are we talking here from a scenario point of view. Prior to SQL Server 2012, tracing the execution plan is an all or nothing approach. In profiler, if we select execution plan, we will get every occurrence of every execution plan for all the statements that are executed inside SQL Server captured by the trace. This will bloat up the trace file very quickly and unimaginable for us to do any sort of analysis. So in general performance templates used inside profiler we do not capture execution plans – and rightly so.

I have been waiting for the ability to capture execution plan only when a query executes longer than a certain amount of time. Yes, I can add a few filters to profile, but this wasn’t the most efficient way to work. In this blog we will look at the Extended Event query_post_execution_showplan (similar to Showplan XML Statistics Profile) in SQL Server 2012 which has an interesting addition of cpu_time and duration for filtering. Personally, I thought this is a great addition to the usage of Extended Events.

Why use this?

We want to use this to monitor the overall server performance and troubleshooting when things go wrong. When the server responses are slow and we have not identified any particular query, we can choose to capture all the execution plans that consumed CPU_Time or duration exceeding certain threshold. This is one of the ways this feature can be used effectively.

Extended Event UI

If you are not familiar with the Extended Events UI, here is a great start for this task. Goto SQL Server Management Studio -> Management Node -> Extended Events -> Sessions -> Right Click and select “New Session…”.

This starts a wizard which will make our configuration quite easy. In Event library, search using the keyword “showplan”. Query_post_execution_showplan will show up as a search result. Assuming you are on SQL Server 2012 and above, note the cpu_time and duration are among available event fields.

Next, select this event and move the entry to the “Selected events:” list. This enables the “Configure” button at the top of the dialog box.

Click the Filter (Predicate) tab. You can choose cpu_time, operator and value. Note that cpu_time is in microseconds. For example, if you want to capture execution plans on all queries taking 10 seconds of CPU, you will choose 10000000. This can be done for queries that are executing more than 10 seconds too by selecting the “duration” field.

Extended Event T-SQL

Instead of showing the UI version of the command, I have gone ahead and scripted out with the duration field the same extended events. The command for the same is:

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


In addition to the filter, I have gone ahead and added few global variables which we might be interested as part of the collection process. Use this with caution and be careful in collecting every single event using Extended Events. I was pleasantly surprised to see the cpu_time and duration in SQL Server 2014 and hence thought it is worth a mention.

Finally, if you are doing it on a Live server. Then make sure to enable the Extended Event and we can use the “watch live data” which I found very useful in SSMS.

If you have used other events of Extended Events in your environment, I would like to learn from you on how you used them and to enable what scenarios.

Reference: Pinal Dave (https://blog.sqlauthority.com)