SQL Server extended events
(Extended Events) is a general event-handling system for server systems. The Extended Events infrastructure supports the correlation of data from SQL Server, and under certain conditions, the correlation of data from the operating system and database applications. In the latter case, Extended Events output must be directed to Event Tracing for Windows (ETW) in order to correlate the event data with operating system or application event data.
You can use Extended Events for a wide range of monitoring and troubleshooting scenarios. The following scenarios illustrate a few of the situations where Extended Events can provide valuable data for resolving issues in such areas as:
- Troubleshooting the cause of working set trimming.
- Troubleshooting excessive CPU usage.
- Troubleshooting deadlocks.
- Correlating request activity with Windows ETW logs.
SQL Server 2008 actually ships with a pre-defined session that is set to run by default and is called the system_health session. The following article
has a few examples of accessing the system_health session sql 2008 extended events for deadlocks and can be useful in determining their cause.