Edit

Use the system_health Session

  • Article

Applies to: SQL Server Azure SQL Managed Instance

The system_health session is an Extended Events session that is included by default with SQL Server. This session starts automatically when the SQL Server Database Engine starts, and runs without any noticeable performance effects. The session collects system data that you can use to help troubleshoot performance issues in the Database Engine.

Important

We recommend that you do not stop, alter, or delete the system_health session. Any changes made to the system_health session settings may be overwritten by a future product update.

The session collects information that includes the following:

  • The sql_text and session_id for any sessions that encounter an error that has a severity >= 20.

  • The sql_text and session_id for any sessions that encounter a memory-related error. The errors include 17803, 701, 802, 8645, 8651, 8657 and 8902.

  • A record of any non-yielding scheduler problems. These appear in the SQL Server error log as error 17883.

  • Any deadlocks that are detected, including the deadlock graph.

  • The callstack, sql_text, and session_id for any sessions that have waited on latches (or other interesting resources) for > 15 seconds.

  • The callstack, sql_text, and session_id for any sessions that have waited on locks for > 30 seconds.

  • The callstack, sql_text, and session_id for any sessions that have waited for a long time for preemptive waits. The duration varies by wait type. A preemptive wait is where SQL Server is waiting for external API calls.

  • The callstack and session_id for CLR allocation and virtual allocation failures.

  • The ring buffer events for the memory broker, scheduler monitor, memory node OOM, security, and connectivity.

  • System component results from sp_server_diagnostics.

  • Instance health collected by scheduler_monitor_system_health_ring_buffer_recorded.

  • CLR Allocation failures.

  • Connectivity errors using connectivity_ring_buffer_recorded.

  • Security errors using security_error_ring_buffer_recorded.

Note

For more information on deadlocks, see the Deadlocks guide.
For more information on SQL error messages, see Database Engine Errors.

View the session data

The session uses the ring buffer target and event file target to store the data. The event file target is configured with a maximum size of 5 MB and a file retention policy of 4 files.

To view the session data from the ring buffer target with the Extended Events user interface available in SQL Server Management Studio, see Advanced Viewing of Target Data from Extended Events in SQL Server - Watch live data.

To view the session data from the ring buffer target with Transact-SQL, use the following query:

SELECT CAST(xet.target_data as xml) 
FROM sys.dm_xe_session_targets xet  
JOIN sys.dm_xe_sessions xe ON (xe.address = xet.event_session_address)  
WHERE xe.name = 'system_health'

To view the session data from the event file, use the Extended Events user interface available in SQL Server Management Studio. For more information, see Advanced Viewing of Target Data from Extended Events in SQL Server.

Restore the system_health session

If you delete the system_health session, you can restore it by executing the u_tables.sql file in Query Editor. This file is located in the following folder, where C: represents the drive where you installed the SQL Server program files, and MSSQL1x the major version of SQL Server:

C:\Program Files\Microsoft SQL Server\MSSQL1x.\<*instanceid*>\MSSQL\Install

Be aware that after you restore the session, you must start the session by using the ALTER EVENT SESSION statement or by using the Extended Events node in Object Explorer. Otherwise, the session starts automatically the next time that you restart the SQL Server service.

The system_health session in Azure SQL

There is no built-in system_health Extended Event session in Azure SQL Database, but you can use sys.fn_xe_file_target_read_file to read from Extended Event sessions you create yourself and store in Azure Blob Storage. For an example walkthrough, review Event File target code for Extended Events in Azure SQL Database.

In Azure SQL Managed Instance, the system_health Extended Events session XEL target is not accessible, but system_health can be accessed via the ring buffer target. For more information, see Targets for Extended Events in SQL Server.

See also

Next steps