Is your system_health session missing data?

~ franklinyamamoto

It is fairly common knowledge that the system_health session is a useful tool for resolving issues such as deadlocks.  I see a lot of posts on other websites where people report a discrepancy in the number of deadlocks monitored by perfmon or tracing vs what shows up in the system health session. This is the result of a bug in the ring buffer which renders it unreliable. The ring buffer should be a FIFO buffer for extended events, but it seems somewhat random what gets retained.

Most of the queries I see posted on websites about the system_health session use the ring buffer target.  If you are on SQL Server 2012 or later, you should query the file target instead of the ring buffer target. Unfortunately sql server 2008 and 2008 R2 only have a ring buffer target configured.

In this post I will show how to create a file target in SQL Server 2008 and SQL Server 2008 R2 for the system_health session.   I will also show how to query the newly created file target.  I will also show how to query the pre-existing file target in SQL 2012 and later.  Last I will show how to drop the file target we created in SQL 2008 and SQL 2008 R2.

Creating a file target for SQL 2008, SQL 2008 R2

You can create a file target for SQL 2008 and 2008 R2.  The @LogLocation is where the log files will be written.  We query the registry via sys.dm_server_registry to find where SQL Server writes the ERRORLOG.  We want to place the system_health file target output in this same location (this is where SQL 2012 and later writes its file target).  After we determine the @LogLocation, we alter the system_health event session and add the file target.

DECLARE @LogLocation NVARCHAR(MAX)
DECLARE @EtlFile NVARCHAR(MAX)
DECLARE @MtaFile NVARCHAR(MAX)
SELECT @LogLocation = CONVERT(NVARCHAR(MAX), value_data)
FROM sys.dm_server_registry
WHERE registry_key LIKE N'%Parameters' AND CONVERT(NVARCHAR(MAX), value_data) LIKE N'-e%'
SET @LogLocation = LTRIM(RTRIM(REPLACE(@LogLocation, N'-e', N'')))
SET @LogLocation = SUBSTRING(@LogLocation, 1, LEN(@LogLocation) - 1)
SET @LogLocation = REPLACE(@LogLocation, N'\ERRORLOG', N'')
SET @EtlFile = @LogLocation + N'\system_health.etl';
SET @MtaFile = @LogLocation + N'\system_health.mta';
DECLARE @Command NVARCHAR(MAX)
SET @Command = N'ALTER EVENT SESSION [system_health] ON SERVER
ADD TARGET package0.asynchronous_file_target(
SET filename=''' + @EtlFile + N''',
metadatafile='''+@MtaFile+N''',
max_file_size=(5),max_rollover_files=(4));'

How do I query my newly created file target?

For SQL Server 2008 and SQL Server 2008 R2, you will need to get the @LogLocation again in order to query the session (this is not necessary in SQL 2012 and later).

DECLARE @LogLocation NVARCHAR(MAX)
SELECT @LogLocation = CONVERT(NVARCHAR(MAX), value_data)
FROM sys.dm_server_registry
WHERE registry_key LIKE N'%Parameters' AND CONVERT(NVARCHAR(MAX), value_data) LIKE N'-e%'
SET @LogLocation = LTRIM(RTRIM(REPLACE(@LogLocation, N'-e', N'')))
SET @LogLocation = SUBSTRING(@LogLocation, 1, LEN(@LogLocation) - 1)
SET @LogLocation = REPLACE(@LogLocation, N'\ERRORLOG', N'')
SELECT
object_name, event_data, file_name, file_offset
FROM sys.fn_xe_file_target_read_file(@LogLocation + N'\system_health*.etl',
@LogLocation+N'\system_health*.mta',NULL,NULL)
where object_name='xml_deadlock_report'

How do I query the file target in SQL Server 2012 and later?

Querying the file target in SQL Server 2012 and later is much simpler.  It is not necessary to fully qualify the path to the file target when you call sys.fn_xe_file_target_read_file.  By default, SQL Server 2012 and later looks in the same directory as the ERRORLOG if you do not fully qualify the path.

SELECT
object_name, event_data, file_name, file_offset
FROM sys.fn_xe_file_target_read_file(N'system_health*.xel',
NULL,NULL,NULL)
where object_name='xml_deadlock_report'

It should also be noted, in SQL 2012 and later the format of the file target changed and there is no longer a metadata (mta) file.

How do I drop the newly created file target?

Easy:

alter event session [system_health] on server
drop target package0.asynchronous_file_target

Conclusion

If you are using the ring buffer when querying the system_health session you should really consider using a file target instead.  This is set up by default in SQL 2012 and later, but you can get around these limitations in SQL 2008 and 2008 R2 with the information I included in this post.

 

Watch us diagnose a query timeout with SQLGrease:

See how you can get query level wait events without Query Store:

Free you say?  How can that be?