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.
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;
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:
SELECT CONVERT(XML, fxftrf.event_data) AS lock_data
FROM sys.fn_xe_file_target_read_file('c:\temp\lock*.xel', NULL, NULL, NULL) AS fxftrf;
SELECT l.lock_data.value('(event/data[@name="mode"]/text)', 'VARCHAR(256)') AS mode,
l.lock_data.value('(event/data[@name="associated_object_id"]/value)', '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.
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!