Overview
Along with your own extended events sessions that you can create, there are built-in extended events sessions that you can use to help monitor SQL Server.
Explanation
SQL Server System_health Extended Events Session
This extended events session is installed by default during the installation of SQL Server and starts automatically when the SQL Server instance is started. It collects system information that can help you monitor the overall health of your SQL Server system.
Some of the events that this session monitors are:
- errors with severity >= 20
- deadlocks
- latch waits > 15 sec
- lock waits > 30 sec
- connectivity errors
- security errors
SQL Server Alwayson_health Extended Events Session
This extended events session is created when you create an availability group. It captures a subset of the availability group events that are related to the AlwaysOn feature.
Some of the events that this session monitors are:
- replica state changes
- errors in connectivity
- ddl_executed
- errors – replica role change
Additional Information
- Use the system_health Session
- Steps to Recover the System Health Extended Events Session in SQL Server
- Monitoring SQL Server Availability Groups with Alwayson Extended Events Health Session
- Using Extended Events to Monitor SQL Server Availability Groups
- Always On Extended Events
Ben Snaidero has been a Database Administrator for just over 10 years. Starting out working mainly with Oracle he got into SQL Server in 2005 and has worked primarily with SQL Server for the last 3 years. His main focus with both Oracle and SQL Server is in the area of performance tuning.
- MSSQLTips Awards: Achiever (75+ tips) – 2018 | Author of the Year Contender – 2016-2017