Repository navigation
Azure SQL DB deadlocks live only in a 4 MB ring buffer we own, while Azure's durable telemetry_xevents is never read #2641
Description
Split out of #2640, whose third report is that Lite shows four deadlocks from Aug 20–21 while the reporter's own query returns deadlocks for Aug 22–26.
What the code guarantees today
On Azure SQL DB, deadlocks have exactly one source: a database-scoped Extended Events session this product creates, read from its ring buffer.
CREATE EVENT SESSION [PerformanceMonitor_Deadlock]
ON DATABASE
ADD EVENT sqlserver.database_xml_deadlock_report
ADD TARGET package0.ring_buffer ( SET max_memory = 4096 )
WITH ( MAX_DISPATCH_LATENCY = 5 SECONDS, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, STARTUP_STATE = ON );FROM sys.dm_xe_database_session_targets AS xet
JOIN sys.dm_xe_database_sessions AS xes ON xes.address = xet.event_session_address
WHERE xes.name = N'PerformanceMonitor_Deadlock' AND xet.target_name = N'ring_buffer'Four properties follow, and each is fine alone:
- A ring buffer is memory-resident. Any Azure SQL DB failover, scale operation or service restart empties it — and Azure performs those without notice.
STARTUP_STATE = ONrestarts the session; it does not restore its contents. max_memory = 4096is 4 MB, and the target discards oldest-first. A handful of large deadlock graphs is enough to roll events out.- Lite collects only while it is running. A deadlock that occurs while the app is closed exists only in the buffer, and only until (1) or (2) removes it.
EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSSpermits the engine to drop events under pressure, by design.
So on Azure SQL DB a deadlock is retained only until the next failover, the next 4 MB of graphs, or the next thing that drops it — whichever comes first — and it is captured only if the app happens to be running before then.
What we do not read, and should
telemetry_xevents — Azure SQL Database's own built-in session — is file-backed and readable through sys.fn_xe_telemetry_blob_target_read_file. It retains far more history than a 4 MB ring buffer and survives the failovers that empty ours. git grep for either name returns nothing: this product has never read it.
That is almost certainly where the reporter's Aug 22–26 deadlocks are. It is the Azure SQL DB analogue of system_health, which we already parse on every other platform — SystemHealthEventsCollector even documents that Azure SQL DB has no system_health session, and stops there rather than naming the session it does have.
Suggested shape
A read of sys.fn_xe_telemetry_blob_target_read_file for database_xml_deadlock_report, used in addition to the ring buffer rather than instead of it. The ring buffer is lower-latency and survives fine within a session; the telemetry blob is the durable backstop that makes a gap recoverable rather than permanent. Deduplicate on the existing (deadlock_time, victim_process_id) identity the collector already keys on.
Worth checking while there: the same argument applies to blocked_process_report on Azure SQL DB, which uses the same ring-buffer shape.
Why this is not just a retention setting
Raising max_memory narrows (2) and does nothing about (1) or (3). Only a durable source fixes the case the reporter actually hit — a gap that is discoverable from Azure and unrecoverable from us.
Activity
erikdarlingdata commentedon Aug 26, 2026
Not writing this blind.
sys.fn_xe_telemetry_blob_target_read_fileis undocumented by Microsoft in any detail I would want to build a collector on — its parameters, and the XML shape of what it returns fordatabase_xml_deadlock_report, both need to be seen rather than assumed.That is not caution for its own sake. The last two Azure/Aurora sources built from documentation both had the documentation wrong:
aurora_stat_wait_event()returns three columns where AWS documents four (#2609), andauto_explainneeded%Qinlog_line_prefixbefore its output could be joined to anything, which no document mentions (#2565). A query written from a reference and shipped untested against a platform none of our targets run is how a collector that returns nothing forever gets merged — which is exactly the class of defect this whole week has been spent removing.What unblocks it: any Azure SQL Database to point at, or the working query from someone who has one. I have asked the reporter on #2640 what they ran, since by their own account it returns the deadlocks ours cannot see.
The rest of the design is settled and recorded above: read it IN ADDITION to the ring buffer, not instead of, and deduplicate on the
(deadlock_time, victim_process_id)identity the collector already keys on.erikdarlingdata commentedon Aug 26, 2026
Tested against a real Azure SQL Database today. The premise above is wrong —
telemetry_xeventsis not a usable deadlock source here.sys.fn_xe_telemetry_blob_target_read_fileexists and has the signature(@prefix nvarchar, @mdpath nvarchar, @initial_file_name nvarchar, @initial_offset bigint), returningmodule_guid, package_guid, object_name, event_data, file_name, file_offset, timestamp_utc.It returns zero rows for every prefix I tried —
dl,deadlock,xe,errors,qds,sqldb,telemetry,blocking,bp, and empty string. No error, no rows.That is not a lag artefact. I induced a genuine deadlock on the database first (two sessions, reciprocal
UPDATEs, real victim), then read both sources:So the read path we already have works, and the durable source I proposed does not exist in the form I assumed. Anyone picking this up should not spend time on the prefix.
This is exactly why it was not written blind. A collector built from the reference would have shipped, degraded silently to zero rows forever, and looked like every other empty panel.
The reporter has since ruled out both remaining explanations
They confirm PMLite ran 24 hours a day on Aug 24, 25 and 26 — so "the app was closed" is out, and a 4 MB ring buffer does not silently drop three days on a quiet database while retaining the oldest four events. Collection is failing while the app is running, which is a different and worse bug than the retention gap this issue was opened for.
The strongest remaining hypothesis, and it is ours
On Azure SQL DB the deadlock session is database-scoped:
A session created in
mastercaptures onlymaster's deadlocks. The reporter's Database Sizes screenshot shows their entry is connected tomaster, and their deadlocks are almost certainly in one of their fifty user databases — which that session cannot see, by construction.If that holds it explains everything: the four old events, the silence since, and the fact that their own query finds deadlocks ours never had. Asked on #2640; the answer decides whether this issue becomes "read a durable source" or "the session is created in the wrong database".
Retitling is premature until they answer, but the durable-telemetry framing in the body above should be read as disproved.
erikdarlingdata commentedon Aug 26, 2026
I was wrong. The premise is correct — I tested from the wrong database.
sys.fn_xe_telemetry_blob_target_read_fileis master-scoped on Azure SQL DB. My harness defaulted to a user database, every prefix returned zero, and I reported it disproved. Same server, same prefix, same minute:That is the second confident negative claim I have made today from an empty result. @ZedZipDev's query was right and posting it is what caught me.
What it actually returns, from master
One row for the deadlock I induced earlier — verified end to end, not inferred:
Every field the collector needs is present, and the
.xelblob URL is the point: this is file-backed, not a ring buffer. It survives the failovers that empty ours, anddatabase_namemakes it server-wide — one read frommastercovers every database on the logical server, which the database-scoped session we create fundamentally cannot.So this is the fix, with the scope corrected
Read
sys.fn_xe_telemetry_blob_target_read_file('dl', NULL, NULL, NULL)from amasterconnection, in addition to the existing ring buffer:master, not onIsAzureSqlDb. From a user database it returns zero rows and must not be read as "no deadlocks" — which is precisely the mistake I just made by hand.(deadlock_time, victim_process_id), the identity the collector already keys on.ts_attrandts_colagree, so either works as the watermark;timestamp_utcis the cheaper one.database_namecomes from the EVENT, not from the connection — so unlike the ring-buffer path there is no per-database fan-out, and noCurrentDatabaseNameto thread.@initial_file_name/@initial_offsetare the resume parameters, which is how a watermark avoids re-reading the whole blob each cycle.file_nameandfile_offsetcome back on every row for exactly that.This also very likely explains the reporter's whole report: their entry is connected to
master, so our database-scoped session only ever sawmaster's deadlocks, while the durable source that sees all fifty databases sat unread.Re-opening the framing in the body above: it was right, and my correction to it was not.
erikdarlingdata commentedon Aug 26, 2026
Fixed in #2644. Azure deadlock capture now reads Azure's durable, file-backed telemetry alongside the ring buffer — one read from
mastercovers every database on the logical server, because the event carries the user database's name.Verified against a live Azure SQL Database using the shipped query text: from
masterthe deadlock returns withsource_database_name= the user database; from that user database the ring-buffer row returns unchanged.The master-scoping is the part worth remembering: called from a user database the identical statement returns zero rows with no error, which is why the arm is guarded on
DB_NAME()rather than on the engine — and why I reported the source unusable before @ZedZipDev posted their working query.Fix deadlocks collector 4104 on Azure SQL DB when CapturePlanXml is on (