Extended Events and I
(T-SQL Tuesday #166)

Foreword

My first public speaking session (and so far the only one) was on Troubleshoot Real-World Scenarios with Extended Events. Check the Tags section of this blog and Extended Events sits near the top. I am a fan.

Why am I a fan?

First, I won't get into the Profiler vs. Extended Events debate. 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 for live event capture, we, mere mortals, have access to only a limited number of tools. I can think of only Trace Flags, DBCC commands, 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 I could debug only with XE's help, including tracking down production errors. 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.

One can dream... Until then, Extended Events stays at the top of my diagnostic toolkit.

Thank you for reading