SQL SERVER: Capture lock escalations for a server

CaptureLockEscalations.sql

-- sourced from claude.ai

-- Create Extended Event session to capture lock escalations
CREATE EVENT SESSION [CaptureEscalations] ON SERVER 
ADD EVENT sqlserver.lock_escalation(
    ACTION(
        sqlserver.client_app_name,
        sqlserver.client_hostname,
        sqlserver.database_name,
        sqlserver.query_hash,
        sqlserver.session_id,
        sqlserver.sql_text,
        sqlserver.username,
        sqlserver.transaction_id
    )
    WHERE (
        [database_id] > 4  -- Exclude system databases if desired
    )
)
ADD TARGET package0.event_file(
    SET filename = N'C:\XEventLogs\LockEscalations.xel',
        max_file_size = (50),  -- 50 MB per file
        max_rollover_files = (5)  -- Keep 5 files max
),
ADD TARGET package0.ring_buffer(
    SET max_memory = (4096)  -- 4 MB ring buffer
)
WITH (
    MAX_MEMORY = 4096 KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 30 SECONDS,
    MAX_EVENT_SIZE = 0 KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = ON,
    STARTUP_STATE = ON  -- Start automatically when SQL Server starts
);
GO

-- Start the Extended Event session
ALTER EVENT SESSION [CaptureEscalations] ON SERVER STATE = START;
GO

-- Query to view lock escalation events from the ring buffer (real-time)
SELECT 
    event_data.value('(@timestamp)[1]', 'datetime2') AS EventTime,
    event_data.value('(data[@name="database_name"]/value)[1]', 'nvarchar(128)') AS DatabaseName,
    event_data.value('(data[@name="escalation_cause"]/text)[1]', 'nvarchar(50)') AS EscalationCause,
    event_data.value('(data[@name="escalated_lock_count"]/value)[1]', 'bigint') AS EscalatedLockCount,
    event_data.value('(action[@name="session_id"]/value)[1]', 'int') AS SessionID,
    event_data.value('(action[@name="username"]/value)[1]', 'nvarchar(128)') AS Username,
    event_data.value('(action[@name="client_hostname"]/value)[1]', 'nvarchar(128)') AS ClientHost,
    event_data.value('(action[@name="client_app_name"]/value)[1]', 'nvarchar(128)') AS ApplicationName,
    event_data.value('(action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS SQLText
FROM (
    SELECT CAST(target_data AS XML) AS target_data
    FROM sys.dm_xe_session_targets xet
    INNER JOIN sys.dm_xe_sessions xes ON xes.address = xet.event_session_address
    WHERE xes.name = 'CaptureEscalations'
      AND xet.target_name = 'ring_buffer'
) AS ring_buffer_data
CROSS APPLY target_data.nodes('RingBufferTarget/event') AS n(event_data)
ORDER BY EventTime DESC;
GO

-- Query to view lock escalation events from the file target (historical)
-- Note: Adjust the file path to match your configuration
SELECT 
    CAST(event_data AS XML).value('(@timestamp)[1]', 'datetime2') AS EventTime,
    CAST(event_data AS XML).value('(data[@name="database_name"]/value)[1]', 'nvarchar(128)') AS DatabaseName,
    CAST(event_data AS XML).value('(data[@name="escalation_cause"]/text)[1]', 'nvarchar(50)') AS EscalationCause,
    CAST(event_data AS XML).value('(data[@name="escalated_lock_count"]/value)[1]', 'bigint') AS EscalatedLockCount,
    CAST(event_data AS XML).value('(action[@name="session_id"]/value)[1]', 'int') AS SessionID,
    CAST(event_data AS XML).value('(action[@name="username"]/value)[1]', 'nvarchar(128)') AS Username,
    CAST(event_data AS XML).value('(action[@name="client_hostname"]/value)[1]', 'nvarchar(128)') AS ClientHost,
    CAST(event_data AS XML).value('(action[@name="client_app_name"]/value)[1]', 'nvarchar(128)') AS ApplicationName,
    CAST(event_data AS XML).value('(action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS SQLText
FROM sys.fn_xe_file_target_read_file('C:\XEventLogs\LockEscalations*.xel', NULL, NULL, NULL)
ORDER BY EventTime DESC;
GO

-- To stop the session (when needed)
-- ALTER EVENT SESSION [CaptureEscalations] ON SERVER STATE = STOP;
-- GO

-- To drop the session (when no longer needed)
-- DROP EVENT SESSION [CaptureEscalations] ON SERVER;
-- GO