Matthew McGiffen walks us through what the system_health Extended Events session gives us:
When Microsoft introduced Extended Events (XE) in 2008, they also gave us a built-in XE session called system_health.
This is a great little tool. I mainly use it for troubleshooting deadlocks as it logs all the information for any deadlocks that occur. No more having to mess about making sure specific trace flags are enabled to ensure deadlock information is captured in the error log.
It also captures the SQL text and Session Id (along with other relevant data) in a number of other scenarios you may need to troubleshoot:
-
Where an error over severity 20 is encountered
-
Where a session has waited on a latch for over 15 seconds
-
Where a session has waited on a lock for over 30 seconds
-
Sessions that have encountered other long waits (the threshold varies by wait type)
Simply knowing what this session includes can give you a leg up on troubleshooting, especially when it’s a machine you haven’t seen before.