You still won’t use them

And I predict that Extended Events will remain as niche-only use for specific troubleshooting scenarios. Very few monitoring tools use them, and the ones that do still rely on traces and DMV queries for the bulk of their collection.  That’s not a good sign, in 2017, considering how long Extended Events have been around.

Why?

They’re a pain in the butt. Microsoft has invested a ton of time expanding the potential uses of them without dedicating any time to making them friendlier to use. Brent and Tara would still rather fire up Profiler, even with equivalent templates in Extended Events.

Why?

XML, probably.

With that out of the way

There are, as of RC2 being released, 194(!) new Events to Extend your mind with. Not all of them are interesting to me, and I haven’t had time to pry into all of the ones that are interesting just yet.

This is a rundown of the new Events with names or descriptions that I found interesting, and will try to spend some time with.

I can’t promise anything

After all, getting some of these to fire is tougher than using a Debugger.

adaptive_join_skipped

I covered this a bit a while back. It still makes the list because uh… It’s my list.

excessive_memory_spilled_to_workfiles

The idea of this one is interesting, but check out this threshold!

If you have queries doing that, you don’t need an Extended Event, you need to replace your developers with pet rocks. They’d be less dangerous.

forced_param*

There are two here, forced_param_clause_skipped_reason and forced_param_statement_ignored_reason. The reasons are listed out here, and have been for years, but now you’ll know why queries aren’t receiving all the wond’rous joys of forced parameterization, without having to guess too much.

implied_join_predicates_possibility

Do all roads lead back to Craig Freedman? Maybe.

“Feature switch” sounds like “Trace Flag” to me. I’m not cool enough to know, but I’m curious if this re-introduces the feature that Craig talks about in his post. I poked around a bit for a regular setting, but couldn’t find one, or a reference to one — sometimes sys.databases will get a column for stuff like this if it’s a database level switch.

interleaved_exec*

There are a bunch of these: interleaved_exec_disabled_reason, interleaved_exec_stats_update, interleaved_exec_status, interleaved_exec_tvf_failed, recompilation_for_interleaved_exec, and sql_tvf_build.

These are all related to interleaved execution of MSTVFs. It’s curious that there’s so much more collection done for these than Adaptive Joins, but maybe that means Adaptive Joins are perfect? Ah, to dream~

It’s like when your mom tells you you’re perfect. She’s had to forget a lot to get those words out of her mouth.

join_condition_description

Woah ho ho, this one is gonna get some noggins joggin! I want you to pay careful attention to this!

Whether the join condition contains a string, binary, uniqueidentifier, or other type that is slow to join on (true) or only simple types like int and datetime (false). Intuitively, these are mostly types > 8 bytes (hence called large). However, even a varchar(6) is consider expensive so will have a true value for the event, though it is less than 8 bytes.

Is SQL Server 2017 jabbing GUID users? Finally admitting that you shouldn’t join on people’s names? Or perhaps just chiding you for being the kind of person who stored a GUID as a NVARCHAR(36)?

We’ll wait and see if this wording changes, before someone out there gets triggered so hard they migrate to DB2.

large_cardinality_misestimate

Occurs when the smaller of the estimated or actual cardinality of an iterator is at least 1 million rows and if the estimated and actual cardinality differ by at least 1 order of magnitude.

This sounds good to me! We recently started doing something similar to this in sp_BlitzCache and sp_BlitzQueryStore. Our math is a little different, but that’s okay because more people use our scripts than Extended Events.

I’m guessing, anyway.

memory_grant_updated_by_feedback and spilling_report_to_memory_grant_feedback

memory_grant_updated_by_feedback will fire off when the memory grant of a query plan is updated after subsequent executions.

spilling_report_to_memory_grant_feedback will give a holler if batch mode queries spill data as part of the feedback process.

This is a pretty cool new feature, and you should all take a minute to thank Joe Sack and Co. for this one. This’ll be awesome.

If you ever upgrade…

Ahem.

operator_type_detected

I know, I know. What the heck? Is Microsoft in a secret arms race with us to tell Paul White if someone finds a Switch operator? I don’t know!

This one is a bit mysterious.

I’m interested! to see which operators this Event finds interesting!

Maybe I’ll find a new one in there!

potential_batch_mode_colocated_join

This one caught my eye because it makes me wonder if collocated is misspelled.

Anyway!

I’m more interested because I wonder if join collocation wasn’t possible with ColumnStore indexes before 2017, or if we just got an XE to detect it now. It’s been available for partitioned rowstore tables since 2008. I say that like we’re old pals, but I’ve never seen it happen out in the wild.

query_optimizer_cardinality_guess

This one is funny. Aren’t all cardinality estimates guesses?

Well, now you’ll know the type of guess, too. Lucky you.

subquery_shape_check

It’s like a Presidential Fitness Test for your subqueries. Or something. Maybe? Not sure where they’re going with this one.

udf_reordering_in_residual_expression

This sounds super fancy, and there’s a Trace Flag involved. That must mean it’s special, no?

One’s left to wonder what kind of UDFs will benefit from this optimization, and if this might be a subtle fix for the much maligned Scalar UDF.

Probably not, but hey.

Can’t fix everything in 25 years.

Thanks for reading!

Brent says: (sigh) it’s true, I’d still rather use Profiler than XE for most use cases (the exception being Jeremiah’s blocked queries & deadlocks script.) For me, the problem is that the behavior seems like it keeps changing between 2008, 2012, 2016, and now 2017. Since I have to keep jumping between boxes, I don’t really wanna adapt my techniques on the fly – I need something that works every time. XE is more powerful, and now that 2012+ seems to have at least 50% market share, I should probably revisit that.