Problem

The deadlock and blocked process report collectors in Lite use Extended Events ring buffer queries that can timeout when the buffer contains a large number of events (e.g., during sustained load testing with HammerDB).

The root cause is that the query casts the entire target_data column to XML inline within a subquery, then shreds it with CROSS APPLY ... .nodes(). When the ring buffer is large (4MB+), this forces SQL Server to repeatedly parse the full XML blob, causing execution times of 2+ minutes and eventual timeout at the 30-second CommandTimeout.

Affected queries

• RemoteCollectorService.Deadlocks.cs — on-prem and Azure SQL DB deadlock collection
• RemoteCollectorService.BlockedProcessReport.cs — on-prem and Azure SQL DB blocked process collection

Observed behavior

• Deadlock collector shows "Execution Timeout Expired" error in Lite status bar
• Execution plan shows a costly Sort operator on the inline XML parse

Fix

Materialize the XML cast into a table variable first, then SELECT/CROSS APPLY from the table variable. This separates the XML parsing from the shredding step.

Before (inline subquery):

SELECT ...
FROM (
    SELECT TRY_CAST(xet.target_data AS xml) AS ring_xml
    FROM sys.dm_xe_session_targets ...
) AS rb
CROSS APPLY rb.ring_xml.nodes(...) AS q(evt)

After (table variable):

DECLARE @PerformanceMonitor_Deadlock TABLE (ring_buffer xml NOT NULL);

INSERT @PerformanceMonitor_Deadlock (ring_buffer)
SELECT TRY_CAST(xet.target_data AS xml)
FROM sys.dm_xe_session_targets ...
OPTION(RECOMPILE);

SELECT ...
FROM (
    SELECT pmd.ring_buffer
    FROM @PerformanceMonitor_Deadlock AS pmd
) AS rb
CROSS APPLY rb.ring_buffer.nodes(...) AS q(evt)
OPTION(RECOMPILE);

Performance improvement

• Before: 2:44 execution time with costly Sort operator
• After: 0.136s INSERT + 0.324s SELECT (~0.46s total)

~350x faster.