If you look at the Tags section of my blog, you can see that Extended Events is one of the top tags. Also, my first (and currently only) public speaking session was on Troubleshoot Real-World Scenarios with Extended Events. So you could say I am a fan.
Why am I a fan?
First, I won’t get into the argument Profiler vs. Extended Events. Profiler was already deprecated when I got myself into the database business, so I never had to work with it. People say it has a better GUI, but there is no more development. It’s time to move on.
Once you reach a certain level of problem complexity, you’ll want to look under the SQL Server’s hood. And we, mere mortals, have access to only a limited number of tools. I can think of only Trace Flags, DBCC and Extended Events.
And if you have to debug something in production, Extended Events is the safest choice.
What can it do?
I’ll again refer to the Extended Events tag; there are some interesting problems that I could debug only with the XE’s help. I also have a GitHub repo sharing a few useful code snippets.
There are several thousand XEs, and most of them are situational. You’ll regularly use about 20 of them (and I’m being generous). But you’ll know where to look when the real tricky problem rears its head.
I’m using this snippet to look for new events. For example, if I want to track why Query Store is switching to read-only - I’ll search for Query Store related XEs.
SELECT dxp.name AS packageName , dxo.name AS eventName , dxo.description AS eventDescription , dxoc.name AS columnName , dxoc.column_id , dxoc.type_name , dxoc.description AS columnDescription , ca.map_agg , dxoc.column_type , dxoc.capabilities_desc , sum (IIF(dxoc.description IS NULL, 1, 0)) OVER () AS NullDescriptionCount , sum (IIF(dxoc.description IS NOT NULL, 1, 0)) OVER () AS NotNullDescriptionCount FROM sys.dm_xe_objects dxo JOIN sys.dm_xe_packages dxp ON dxo.package_guid = dxp.guid JOIN sys.dm_xe_object_columns AS dxoc ON dxoc.object_name = dxo.name AND dxoc.object_package_guid = dxo.package_guid AND dxoc.column_type <> N'readonly' CROSS APPLY ( SELECT STRING_AGG(CAST(dxmv.map_value AS nvarchar(MAX)), N', ') WITHIN GROUP (ORDER BY dxmv.map_key) AS map_agg FROM sys.dm_xe_map_values AS dxmv WHERE dxmv.object_package_guid = dxoc.object_package_guid AND dxmv.name = dxoc.type_name ) AS ca WHERE dxo.object_type = 'event' AND dxo.name LIKE '%query_store%' --AND dxoc.name LIKE '%query_hash%' --AND ca.map_agg LIKE '%abort%' --AND dxp.name = 'sqlserver'
You can also uncomment and search in column names, description, map values, etc.
Every silver lining has a cloud
The tool isn’t perfect. The GUI is clunky. You have to get good at TSQL xml parsing, and I could go on. In fact I did and sent MS an A4 with common pain points and suggestions for improvements, but I digress.
Often, I have to write my own procedure to read from the target .xel files, and it usually involves looping through databases, string parsing and trying to get resource names from their IDs.
Ideally, I’d love a built-in, reliable way of collecting the XE data into a central monitoring storage with pre-processing similar to Query Store’s aggregation.
Thank you for reading!