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

Foreword

This month’s invitation is from Grant Fritchey (t|b), aka the Scary DBA, about Extended Events (XEs).

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.

The only thing that comes is XESmartTarget by Spaghettidba. If MS would provide something like this out-of-the-box, the tool would be in a much better place. One can dream…

Thank you for reading!