Worse Than Mobile Browsing
I have a love/hate relationship with Extended Events. Yes, they’re powerful. Yes, you can track interesting things.
But they’re just not intuitive, like, 10 years on.
Part of the problem is that the narrative from Microsoft has been that it’s a full replacement for Profiler and Traces.
How many monitoring tool vendors do you see using Extended Events instead?
Trying To Do Something Simple
Tracking locks should be very easy, right? There’s locks everywhere.
But right away, there’s a problem.
|
1 2 3 4 5 6 7 8 9 |
SELECT mv.name, mv.map_value, xo.description, xp.description FROM sys.dm_xe_map_values AS mv JOIN sys.dm_xe_objects AS xo ON mv.object_package_guid = xo.package_guid AND mv.name = xo.name JOIN sys.dm_xe_packages AS xp ON xo.package_guid = xp.guid WHERE mv.name = 'lock_mode' ORDER BY mv.map_key; |
I have a User Thing issue about this open.
The issue is that LAST_MODE should map to RX_X, but it doesn’t.
Leaving aside the juvenile word searches one could explore here, if you were trying to figure out a bad locking problem, what would you think of LAST_MODE?
Probably not much, but the wait that it maps to is usually due to Serializable locking.
Not something you wanna ignore.
Speaking Of Locks…
If you were trying to figure out how to see which objects were locked, what of these would you look at?
There are five columns that purport to have an object ID in them. None of them ever seem to resolve when you use OBJECT_NAME.
In fact, most of them fail, because they’re populated with bigger ints than OBJECT_NAME can cope with.
If you were to create an XE session to look at locks, and then try to parse things out, you’d have to do something like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT CONVERT(XML, fxftrf.event_data) AS lock_data INTO #locks FROM sys.fn_xe_file_target_read_file('c:\temp\lock*.xel', NULL, NULL, NULL) AS fxftrf; WITH thing AS ( SELECT l.lock_data.value('(event/data[@name="mode"]/text)[1]', 'VARCHAR(256)') AS mode, l.lock_data.value('(event/data[@name="associated_object_id"]/value)[1]', 'BIGINT') AS associated_object_id FROM #locks AS l ) SELECT t.*, OBJECT_NAME(p.object_id) AS table_name FROM thing AS t JOIN sys.partitions AS p ON t.associated_object_id = p.hobt_id; |
The associated_object_id maps to the hobt_id column in sys.partitions.
Which is per-database. Context is everything.
I didn’t see a better way to do this when looking at the Global Fields you can collect.
Not exactly intuitive.
Holes
When you’re going to use Extended Events, there’s a heck of a lot of shruggery involved.
Which event(s) do I need? What do they actually capture? What really triggers them? What sort of target should I use? If I allow events to be lost, will I miss what I’m looking for?
None of this stuff is obvious, which puts anyone trying to use them to track down or solve a problem for the first time at a real disadvantage. I use them fairly frequently (compared to a lot of people I know), and I still get hung up on stuff.
At the beginning of the article, I said XE is worse than mobile browsing.
That sounds harsh, but hear me out: if you’re on your phone (server), and there’s an article (event) you’re interested in, what’s an easy way for you to lose interest quick?
Clicking on a link, getting a full screen pop up ad, then a request to allow notifications, then a pop up window, one of those scroll past ads, maybe some auto play video for good measure. If you’re really lucky, you’ll get a notification that you won a hot local single.
I’m reminded of all those annoyances every time I go to do something with Extended Events. There’s a lot standing in the way of me acquiring an interesting piece of information. They should have a lot more going for them by now, but even just figuring out microseconds or milliseconds is a pain.
My Friend From Slack, Steve Jones, said something along the lines of: Software should make people’s lives easier.
Right now, Extended Events often introduce a lot of complications just trying to answer relatively simple questions.
Thanks for reading!
8 Comments. Leave new
Great post Er… B… Erik!
Kudos to you for Raising Awareness on the many WTFs on Extended Events. I love how lightweight they are and all the stuff you can expose with them, but trying to figure out how to configure the session and then deconfrigulate the XML is worse than trying to get a squat rack at 5:30 on a Friday.
They’re on my short list of things to get less-awful at, what for the monitoring and performance tuning and all. As the saying goes; “Extended Events are like Crossfit; if you don’t know what you’re doing, just do everything and hope for the best.”
Brandon — yeah, that’s another sign that it’s flailing. It’s been around for 10 years, and it’s still on a lot of lists of “things I wanna check out someday”.
Like the 500mg of Test E I wanna take every time I fail a PR.
Dude your source must love you! Whatever it takes, am I right babe?
Still too soon?
> mfw no scoops
Thanks for bringing attention to this. I would add that I hate querying XML, so I only look at live data with XE. I have a saved script for XE that looks at what queries are being run and when they complete. Anything else I’d want to look at and I’m up a creek. At the very very least msoft could come up with some good canned choices, e.g. slow queries, wait times, etc.
Agreed here. I spend a lot of time working with XML for plan cache queries, and I always dread working with it for XE.
Seconded on the units thing – I love the instrumentation in SQL Server but why don’t the column names include the measurement units, is it seconds, milliseconds, microseconds or ticks. megabytes, extents, pages, kilobytes or just bytes.
Better yet, let people choose the granularity. When’s the last time bytes or microseconds helped anyone fix a problem?