Mining the system_health Session You Already Have
The first three posts in this series all started the same way: create a session, capture an event, schedule a job to drain it. That is the right approach when you need something SQL Server is not already recording. But before you build anything, it is worth knowing that SQL Server has been quietly keeping a detailed diagnostic log since the day you installed it, and almost nobody reads it. That log is the system_health session, it is on by default, and it has probably already captured the last deadlock, the last severe error, and the last long wait on your instance.
This is the fourth post in the Extended Events for the working DBA series, and it is the one that requires no setup at all. Instead of creating a session, we are going to read the one Microsoft already created for you, pull its event file off disk, and shred the XML into something you can query.[1]
What system_health Already Records
The system_health session starts automatically with the instance and captures a broad set of diagnostics: every deadlock graph, errors of severity 20 and above, errors from a handful of specific high-value numbers (such as 17803 and 701, the memory errors), sessions that have waited on a lock for more than 30 seconds, sessions waiting on long latches, and the periodic output of sp_server_diagnostics, which is the same component health data the availability group health model uses. It writes all of this to a ring_buffer target and, more usefully for history, to an event_file target: a set of rolling .xel files on disk.
Because it writes to files, system_health keeps far more history than a ring buffer alone, and that history survives a restart. The default is four files of 5 MB each, rolling over, so on a busy instance you might have only a few hours, but on most you have days. The data is sitting there. The only trick is reading it.
Finding and Reading the Event File
You do not hardcode the file path, because it includes a rollover suffix that changes. Instead, ask the running session where its file target is pointing, then hand that path to sys.fn_xe_file_target_read_file, which returns one row per event with the event payload as XML in the event_data column.[2]
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
SET NOCOUNT ON; DECLARE @event_file_name nvarchar(260); DECLARE @target_data xml; /* Ask the running session where its event_file target writes. */ SELECT @target_data = CONVERT(xml, [t].[target_data]) FROM [sys].[dm_xe_sessions] AS [s] INNER JOIN [sys].[dm_xe_session_targets] AS [t] ON [t].[event_session_address] = [s].[address] WHERE [s].[name] = N'system_health' AND [t].[target_name] = N'event_file'; SET @event_file_name = @target_data.value('(/EventFileTarget/File/@name)[1]', 'nvarchar(260)'); /* Read every event from the file set into a working table as XML. */ DROP TABLE IF EXISTS [#shredded]; SELECT [event_data] = CONVERT(xml, [xeft].[event_data]) INTO [#shredded] FROM [sys].[fn_xe_file_target_read_file](@event_file_name, NULL, NULL, NULL) AS [xeft]; |
Passing NULL for the file name to fn_xe_file_target_read_file would read every .xel in the default location, but pulling the path from the session is more precise and works regardless of where the files live. At this point #shredded holds the entire captured history as one XML document per row, ready to query.
Knowing What Is in There
Before you go looking for a specific event, it helps to see what the file actually contains and in what volume. Every event carries its name as an attribute, so a single grouping query gives you an inventory.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
;WITH [src] AS ( SELECT [event_name] = [n].[x].value('(/event/@name)[1]', 'nvarchar(128)') FROM [#shredded] AS [s] CROSS APPLY [s].[event_data].nodes('/') AS [n]([x]) ) SELECT [src].[event_name] , [count] = COUNT_BIG(1) FROM [src] GROUP BY [src].[event_name] ORDER BY [count] DESC; |
The CROSS APPLY ... nodes('/') is the pattern that turns each stored XML document into a row you can shred with .value(). The result is a map of your instance’s recent pain:
|
1 2 3 4 5 6 7 8 |
event_name count ------------------------------------------------------ ----- sp_server_diagnostics_component_result 1,440 wait_info 312 scheduler_monitor_system_health_ring_buffer_recorded 96 error_reported 47 xml_deadlock_report 9 security_error_ring_buffer_recorded 3 |
That xml_deadlock_report row is the same deadlock data the first post built a dedicated session to capture, except system_health already has the last nine for free. For a one-off “what deadlocked last night,” this file is faster than any session you could build. The dedicated session still earns its place when you need longer retention than the rolling files give you, but for a quick look, start here.
Shredding a Specific Event
Once you know what is in the file, pull a single event type into columns. Waits are a good example: system_health records any session that waited on a single resource longer than its threshold, which is exactly the kind of stall a user notices. Filter the shredded rows to wait_info and extract the wait type, the duration, and the SQL text that was waiting.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
DECLARE @timezone nvarchar(128) = N'Central Standard Time'; SELECT [timestamp] = [n].[x].value('(/event/@timestamp)[1]', 'datetimeoffset(3)') AT TIME ZONE @timezone , [wait_type] = [n].[x].value('(/event/data[@name="wait_type"]/text)[1]', 'nvarchar(128)') , [duration_ms] = [n].[x].value('(/event/data[@name="duration"]/value)[1]', 'bigint') , [signal_ms] = [n].[x].value('(/event/data[@name="signal_duration"]/value)[1]', 'bigint') , [session_id] = [n].[x].value('(/event/data[@name="session_id"]/value)[1]', 'int') , [sql_text] = [n].[x].value('(/event/data[@name="sql_text"]/text)[1]', 'nvarchar(max)') FROM [#shredded] AS [s] CROSS APPLY [s].[event_data].nodes('/') AS [n]([x]) WHERE [n].[x].value('(/event/@name)[1]', 'nvarchar(128)') = N'wait_info' ORDER BY [duration_ms] DESC; |
The same shape works for any event in the file. Change the event name in the WHERE clause and the data[@name="..."] paths to match the columns that event carries, and you have a query for errors, security failures, or the sp_server_diagnostics component results that tell you whether the IO, memory, or CPU subsystems flagged themselves unhealthy.
The Timestamp Gotcha
Every timestamp in the event file is stored in UTC. If you filter by a local time without converting, you will quietly select the wrong window and conclude, wrongly, that nothing was captured. The clean fix is to do the comparison in UTC and only convert to local time for display, which is what AT TIME ZONE does in the query above. If you want to filter to a window, compute the bounds in UTC:
|
1 2 |
DECLARE @StartUtc datetimeoffset = DATEADD(HOUR, -4, SYSUTCDATETIME()); DECLARE @EndUtc datetimeoffset = SYSUTCDATETIME(); |
then compare the event timestamp against those. Do the math in UTC, present in local; never the other way around.[3]
Caveats
- The files roll over. The default is four 5 MB files. On a busy instance that can be only a few hours of history, so do not treat
system_healthas a long-term archive. If you need more, that is exactly when a dedicated session like the ones in the earlier posts pays off. - Do not over-modify it.
system_healthis used by the availability group and Failover Cluster Instance health models. Read it freely, but think twice before changing what it captures. - Reading large files is not free.
fn_xe_file_target_read_filereads and parses the whole file set. On a large, busy instance this is real work; run it deliberately, not in a tight loop. AT TIME ZONEneeds SQL Server 2016 or later. On older versions, convert with a manual UTC offset as the original wider script does, or simply report in UTC.
The Takeaway
Before you build a custom session, read the one you already have. system_health has been recording deadlocks, severe errors, long waits, and component health since installation, and fn_xe_file_target_read_file plus a CROSS APPLY nodes('/') turns that file into a queryable table. An inventory query tells you what is in there, a per-event shred turns it into columns, and the only real trap is remembering that the timestamps are UTC. The custom sessions from the earlier posts are still worth building when you need retention or events system_health does not capture, but for a fast answer to “what just happened,” the data is already on disk.
What do you reach for first when an instance misbehaves, system_health, a custom session, or the DMVs? I would like to hear how you use it. Find me on Bluesky or LinkedIn.
If you want to go deeper on reading a target’s XML, I walked through the ring buffer specifically in Reading the ring buffer target, which pairs well with the event-file approach here.
More in this series
- Part 1: From deadlock graph to repeat offender
- Part 2: Blocked process reports, from capture to root cause
- Part 3: A lightweight error audit with Extended Events
- Part 4: Mining the system_health session you already have (this post)
- Part 5: Catching slow file I/O with a filtered session
References
- Use the system_health session – Microsoft Learn. What the default session captures and how it is configured. ↩
- sys.fn_xe_file_target_read_file – Microsoft Learn. Reading event_file targets from disk and the columns it returns. ↩
- AT TIME ZONE (Transact-SQL) – Microsoft Learn. Converting datetimeoffset values to a named time zone, available from SQL Server 2016. ↩