What is the system_health extended events session?4
July 5, 2017 by Kenneth Fisher
Similar to the default trace the system_health session is automatically started up when the instance starts and collects information about what’s going on.
Per BOL you get the following information:
- Errors with a severity of >= 20.
- Memory related errors (Errors 17803, 701, 802, 8645, 8651, 8657 and 8902).
- Non-yielding scheduler problems (Error 17883).
- Sessions that have waited on locks for > 30 seconds.
- Sessions waiting for a long time on preemptive waits (waits on external API calls).
- CLR allocation & virtual allocation failures.
- 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.
Of that the most commonly used (at least in my experience) are the deadlocks and errors, although sessions waiting on locks could be very interesting information too. Note: this isn’t the same data that you see in the default trace.
Ok, so we have the data being collected but where is it being stored? If you look in SSMS you’ll see it has two targets.
And when I scripted mine out I was able to see that the file target is called system_health.xel and a quick directory search (on system_health*.xel) found that it’s not an easy file to locate. Under the Program Files\Microsoft SQL Server\130\Setup Bootstrap\Log\ directory there are date/time directories. Under those are various files including the system_health[big long number].xel
Ok, so now that we know what and where the system_health extended event session is/goes how do we see the data?
Well, I recommend going here. There is a ton of information in there. In general though:
You can open one or more of the files using SSMS:
View target data:
When you view the ring_buffer data it comes out as XML which may or may not be easier to work with depending on your needs.
Watch Live Data
This is going to be new data only, no history.
SELECT * FROM sys.fn_xe_file_target_read_file ('system_health*.xel',null,null,null)
Fair warning on this one, again, the event_data is in XML. You have to use XQuery to work with it. The big benefit here is that you can write queries to parse your data and only look at specific things (deadlocks for example).
Category: Microsoft SQL Server, SQLServerPedia Syndication, System Functions and Stored Procedures | Tags: default trace, microsoft sql server, system functions
4 thoughts on “What is the system_health extended events session?”
Leave a Reply to What queries are getting blocked? | SQL Studies Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
[…] Kenneth Fisher describes what is in the system_health Extended Events session: […]
[…] put up a great post EXTENDED EVENTS, THE SYSTEM_HEALTH SESSION, AND WAITS that talks about how the system health extended event session pulls, among other things, queries that have to wait for more than 30 […]
[…] make one more push. This month I’d like you to take a look at the default trace and the system health extended events session. Last month I had you looking at the built in reports in SSMS because they have a ton of great […]
[…] What is the system_health extended events session? […]