2

I'd like to get a histogram through extended events that shows the count of deadlocks per databasename by date, but I'm hitting two walls:

  1. How get a histogram to include two actions
  2. How to get the collect_system_time rounded to just the date -- I don't see a way to use a function on an action.

The (relatively) easy answer would be to query and shred the xel files and produce the result set I want (and then copy/paste to Excel if I want a chart), but I was curious if there was a way to do it directly within an XE session histogram.

  • have you looked at system_health_session - that is the default XEvent running and does collect deadlock info ? – Kin Shah Feb 18 '16 at 15:16
  • So you want the session data to only store the date and not the time? I think this is generally where we do such rounding / granularity changes at query time, not in the source... what if you later need to find events that happened at a specific time? Having the session discard that information could be costly. – Aaron Bertrand Feb 18 '16 at 15:39
  • @Kin: system_health_session does collect deadlock info, but I want to capture more data, and include the histogram. – Mark Freeman Feb 18 '16 at 15:51
  • @AaronBertrand: I'd still be capturing the full collect_system_time and the xml_deadlock_report to an event_file target, but I want to have the histogram roll up by date. – Mark Freeman Feb 18 '16 at 15:54
3

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: Aggregated deadlock history

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.