Sql-server – How are the system health extended events files rolling over

Thanks to @Tom V for identifying this blog post that identifies the need for a temporary table.

Adapting the ideas in the blog post, this now works very quickly:

/***************************

shred the Event Data into readable form

***************************/

DECLARE @xml XML;

SELECT TOP(1) @xml = CONVERT(xml, xet.target_data)
FROM sys.dm_xe_session_targets AS xet
    INNER JOIN sys.dm_xe_sessions AS xe ON (xe.address = xet.event_session_address)
WHERE xe.name = 'queries';

IF OBJECT_ID('tempdb..#xmlResults') IS NOT NULL
DROP TABLE #xmlResults;

CREATE TABLE #xmlResults
(
    RowNum INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , xeXML XML NOT NULL
);

INSERT INTO #xmlResults (xeXML)
SELECT xm.s.query('.')
FROM @xml.nodes('/RingBufferTarget/event') AS xm(s)
OPTION (Optimize FOR (@xml = Null)); -- Useful for SQL 2008

SELECT *
FROM #xmlResults

SELECT t.EventName
    , DateStamp = DATEADD(HOUR, -6, t.EventDateStamp)
    , DatabaseName = d.name
    , t.ErrorNumber
    , t.ErrorSeverity
    , t.ErrorState
    , t.ErrorMessage
    , t.CollectSystemTime
    , t.ClientAppName
    , t.ClientHostName
    , t.PlanHandle
    , t.SqlText
    , t.UserName
FROM (
        SELECT EventName =          xeXML.value('(event/@name)[1]','varchar(500)')
            , EventDateStamp =      xeXML.value('(event/@timestamp)[1]','datetime')
            , DatabaseID =          xeXML.value('(event/data[(@name)[1] eq "source_database_id"]/value/text())[1]','varchar(255)')
            , ErrorNumber =         xeXML.value('(event/data[(@name)[1] eq "error"]/value/text())[1]','varchar(255)')
            , ErrorSeverity =       xeXML.value('(event/data[(@name)[1] eq "severity"]/value/text())[1]','varchar(255)')
            , ErrorState =          xeXML.value('(event/data[(@name)[1] eq "state"]/value/text())[1]','varchar(255)')
            , ErrorMessage =        xeXML.value('(event/data[(@name)[1] eq "message"]/value/text())[1]','varchar(255)')
            , CollectSystemTime =   xeXML.value('(event/action[(@name)[1] eq "collect_system_time"]/text/text())[1]','varchar(255)')
            , ClientAppName =       xeXML.value('(event/action[(@name)[1] eq "client_app_name"]/value/text())[1]','varchar(255)')
            , ClientHostName =      xeXML.value('(event/action[(@name)[1] eq "client_hostname"]/value/text())[1]','varchar(255)')
            , PlanHandle = CONVERT(xml, xeXML.value('(event/action[(@name)[1] eq "plan_handle"]/value/text())[1]','varchar(255)')).value('(plan/@handle)[1]', 'varchar(255)')
            , SqlText =             xeXML.value('(event/action[(@name)[1] eq "sql_text"]/value/text())[1]','nvarchar(max)')
            , UserName =            xeXML.value('(event/action[(@name)[1] eq "username"]/value/text())[1]','varchar(128)')
        FROM #xmlResults xm
    ) t
    LEFT JOIN sys.databases d ON t.DatabaseID = d.database_id
ORDER BY t.UserName
    , t.EventDateStamp;

A target is a place where events captured by an Extended Events (XE) session go to live. A histogram (previously synchronous_bucketizer or asynchronous_bucketizer) is a target only visible in the sys.dm_xe_session_targets DMV whilst the XE session is running. An .xel file is also a target persisted to a file. Although you can have multiple targets per session (eg ring buffer, histogram, event file, event counter) you cannot place a histogram in an event file, a target within a target. Nor can you use custom functions within the XE session to modify the attributes of actions and events you capture.

However what you could do, is create an XE session using the histogram target and the sqlserver.database_xml_deadlock_report event and poll it on a regular basis, eg 5 minutes to the hour in my example below. Run the code in the 'Setup' section first, then place the second bit of code in a SQL Agent task schedule to run each hour. This code groups the information from the XE session up into date, hour and database format and persists it to a table:

------------------------------------------------------------------------------------------------
-- Setup START
------------------------------------------------------------------------------------------------

-- Setup
--IF EXISTS( SELECT * FROM sys.server_event_sessions WHERE name = 'XE_DeadlockHistogram' )
--  DROP EVENT SESSION XE_DeadlockHistogram ON SERVER;
--GO


IF NOT EXISTS( SELECT * FROM sys.server_event_sessions WHERE name = 'XE_DeadlockHistogram' )
CREATE EVENT SESSION XE_DeadlockHistogram ON SERVER
ADD EVENT sqlserver.database_xml_deadlock_report
ADD TARGET package0.histogram
(
SET slots = 32                      --< this should be greater than number of databases on your server; gets rounded up nearest power of 2 anyway
  , filtering_event_name = 'sqlserver.database_xml_deadlock_report' --, 
  , source_type = 0                 -- < 0|1 = event|action
  , source = 'database_name'
)
WITH (
    MAX_MEMORY = 4096KB, 
      EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, 
      MAX_DISPATCH_LATENCY = 10 SECONDS, 
      MAX_EVENT_SIZE = 0KB,
      MEMORY_PARTITION_MODE = NONE, 
      TRACK_CAUSALITY = OFF, 
      STARTUP_STATE = OFF
    )
GO


-- Tracking table
IF OBJECT_ID('dbo.deadlockHistogram') IS NULL
    CREATE TABLE dbo.deadlockHistogram ( 
        deadlockDate        DATE NOT NULL, 
        deadlockHour        TINYINT NOT NULL,
        dbName              SYSNAME NOT NULL,
        sessionStartTime    DATETIME NOT NULL,
        deadlockCount       SMALLINT NOT NULL,

        CONSTRAINT PK_deadlockHistogram PRIMARY KEY ( deadlockDate, deadlockHour, dbName )
        )
GO


-- Setup END
------------------------------------------------------------------------------------------------





------------------------------------------------------------------------------------------------
-- SQL Agent Job Code START
-- Place this code in a SQL Agent job and schedule to be run every hour at 5 minutes to the
-- hour, eg 6.55, 7.55 etc
-- !!TODO Wrap in a stored proc; add to Utilities database
------------------------------------------------------------------------------------------------

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
GO

SELECT IDENTITY( INT, 1, 1 ) rowId, CAST( target_data AS XML ) AS target_data, s.create_time AS sessionStartTime, SYSDATETIME() AS dateAdded, CAST( SYSDATETIME() AS DATE ) AS deadlockDate, DATEPART( hour, SYSDATETIME() ) AS deadlockHour
INTO #tmp
FROM sys.dm_xe_session_targets st 
    INNER JOIN sys.dm_xe_sessions s on s.address = st.event_session_address
WHERE s.name = 'XE_DeadlockHistogram'
  AND st.target_name = 'histogram'
GO


-- Collect current deadlock info from dm_xe_session_targets and tag with current date/hour
;WITH cte AS
(
SELECT
    CAST( dateAdded AS DATE ) AS deadlockDate,
    DATEPART( hour, dateAdded ) AS deadlockHour,
    v.c.value( '.', 'SYSNAME' ) dbName,
    sessionStartTime,
    s.c.value( '@count', 'INT' ) deadlockCount
FROM #tmp t
    CROSS APPLY t.target_data.nodes('HistogramTarget') td(c)
        CROSS APPLY td.c.nodes('Slot') s(c)
            CROSS APPLY s.c.nodes('value') v(c)
)
INSERT INTO dbo.deadlockHistogram ( deadlockDate, deadlockHour, dbName, sessionStartTime, deadlockCount )
SELECT deadlockDate, deadlockHour, dbName, sessionStartTime, deadlockCount
FROM cte t
WHERE NOT EXISTS
    (
    SELECT *
    FROM dbo.deadlockHistogram h
    WHERE t.deadlockDate = h.deadlockDate
      AND t.deadlockHour = h.deadlockHour
      AND t.dbName = h.dbName
    )
GO


-- Stop and restart the session to reset the historgram counts
IF EXISTS( SELECT * FROM sys.dm_xe_sessions WHERE name = 'XE_DeadlockHistogram' )
BEGIN
    ALTER EVENT SESSION XE_DeadlockHistogram ON SERVER STATE = STOP
    RAISERROR( 'XE session %s stopped successfully.', 10, 1, 'XE_DeadlockHistogram' )
END
GO

IF EXISTS( SELECT * FROM sys.server_event_sessions WHERE name = 'XE_DeadlockHistogram' )
BEGIN
    ALTER EVENT SESSION XE_DeadlockHistogram ON SERVER STATE = START
    RAISERROR( 'XE session %s started successfully.', 10, 1, 'XE_DeadlockHistogram' )
END
GO

IF NOT EXISTS( SELECT * FROM sys.server_event_sessions WHERE name = 'XE_DeadlockHistogram' )
RAISERROR( 'XE session %s does not exist.', 16, 1, 'XE_DeadlockHistogram' )
GO

-- SQL Agent Job Code END
------------------------------------------------------------------------------------------------

Stopping and restarting the session during the Agent job is important to reset the histogram. When deadlocks occur, the session will record XML that looks like this:

<HistogramTarget truncated="0" buckets="32">
  <Slot count="2">
    <value>tempdb</value>
  </Slot>
  <Slot count="1">
    <value>testCS</value>
  </Slot>
</HistogramTarget>

The code above shreds it and over time it will build up into data that looks like this:

This is a simple example, your business requirements may be more complex, but have a look through the code and let me know what you think. You could probably also achieve something similar polling the system_health session (as mentioned by @Kin) but you would require some more heavy-weight XQuery which I can help you with if required. The system_health session also targets to ring buffer which may wrap around / cycle, so this tightly scoped histogram session isn't a bad idea.

NB I am not using the sqlserver.xml_deadlock_report event as database name was always master, I guess where deadlock monitor runs.