I use Extended Events almost exclusively for capturing query metrics. They are the most consistent and lowest cost mechanism for getting the time and resources used by a query. They can be filtered, combined with other events, they’re just marvelous… until you capture an execution plan.

Execution Plans in Extended Events

Don’t get me wrong. Capturing execution plans with Extended Events is the way to go if you’re attempting to automate the process of capturing plans on specific queries on an active system. It’s step two that bugs me. So, we capture the plan. Here’s an example script. Captures all the various plans and the batch, puts ’em together using causality tracking:

CREATE EVENT SESSION ExecutionPlansOnAdventureWorks2014
ON SERVER
    ADD EVENT sqlserver.query_post_compilation_showplan
    (WHERE (   sqlserver.database_name = N'AdventureWorks2014')),
    ADD EVENT sqlserver.query_post_execution_showplan
    (WHERE (   sqlserver.database_name = N'AdventureWorks2014')),
    ADD EVENT sqlserver.query_pre_execution_showplan
    (WHERE (   sqlserver.database_name = N'AdventureWorks2014')),
    ADD EVENT sqlserver.sql_batch_completed
    (WHERE (   sqlserver.database_name = N'AdventureWorks2014'))
    ADD TARGET package0.event_file
    (SET filename = N'C:\PerfData\ExecutionPlansOnAdventureWorks2014.xel')
WITH (MAX_MEMORY = 4096KB,
      EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
      MAX_DISPATCH_LATENCY = 30 SECONDS,
      MAX_EVENT_SIZE = 0KB,
      MEMORY_PARTITION_MODE = NONE,
      TRACK_CAUSALITY = ON,
      STARTUP_STATE = OFF);
GO

Cool beans. Does what I want, when I want, where I want. Excellent. Here is a captured plan shown graphically in SSMS:

Notice anything missing? Yeah, the first operator, the SELECT operator (technically, not really an operator, but they don’t have any name or designation in the official documentation, so I’m calling them operators). It’s not there. Why do I care?

Because it’s where all the information about the plan itself is stored. Stuff like, Cached Plan Size, Compile Time, Optimizer Statistics Usage, Reason for Early Termination, is all there, properties and details about the plan itself. Now, the weird thing is, if you look to the XML, as shown here, all that data is available:

What’s going on, as near as I can guess, is that the XML captured by Extended Events is ever so slightly different and it just can’t display that first operator correctly. You can see it for yourself. Capture a plan using Extended Events and one using SMSS (or Query Store, or the DMVs, they all behave the same way), then look at the XML for each plan. They are a little different. Just enough to change how the plan displays within the graphical viewer. Odd stuff.

Conclusion

This is a pain. It’s not an end of the world scenario, just a pain. I’d prefer to not have to dig into the XML when I capture a plan with Extended Events, but, for the moment, you do. Hopefully we’ll see this changed with a new cumulative update sooner rather than later. In the mean time, you know where to look for that information, the XML.

One point worth noting, capturing execution plans through Extended Events is expensive (much cheaper than Trace Events, but still expensive). Be judicious in the use of this functionality.

If you’re not yet using Extended Events, or, you want to know more about how to use them, and a whole bunch of other tooling in SQL Server as part of query tuning, then come and spend the day with me. I’m taking this show on the road:

For SQLSaturday Philadelphia on April 20, 2018. Please sign up here.

For SQLSaturday NYC on May 18, 2018. Go here to register.

For SQLSaturday Indianapolis (and my first time speaking in Indiana) on August 10, 2018. Please go here to sign up.

For SQLSaturday Oslo on August 31, 2018. Click here right now to register.

The post Execution Plan Shortcoming in Extended Events appeared first on Grant Fritchey.