Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Execution Plan Shortcoming in Extended Events

DZone's Guide to

Execution Plan Shortcoming in Extended Events

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 now, I do.

· Database Zone ·
Free Resource

Download "Why Your MySQL Needs Redis" and discover how to extend your current MySQL or relational database to a Redis database.

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 2 that bugs me. So, we capture the plan. Here's an example script. Captures all the various plans and the batch and 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, theSELECT  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, and Reason for Early Termination are all there — properties and details about the plan itself. Now, the weird thing is, if you look at 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, I do. Hopefully, we'll see this changed with a new cumulative update sooner rather than later. In the meantime, 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.

Read "Developing Apps Using Active-Active Redis Enterprise" and discover the advantages over other active-actve databases.

Topics:
database ,execution plans ,xml ,extended events

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}