Extended Events have the following key design aspects:

• The Extended Events engine is event agnostic, which enables the engine to bind any event to any target because the engine is not constrained by event content. For more information about the Extended Events engine, see SQL Server Extended Events Engine.
  
• Events are separated from event consumers, which are called targets in Extended Events. This means that any target can receive any event. In addition, any event that is raised can be automatically consumed by the target, which can log or provide additional event context. For more information, see SQL Server Extended Events Targets
  
• Events are distinct from the action to take when an event fires. As a result, any action can be associated with any event.
  
• Predicates can dynamically filter event firing, which adds to the flexibility of the Extended Events infrastructure. For more information, see SQL Server Extended Events Packages.
  

Extended Events can synchronously generate event data (and asynchronously process that data) which provides a flexible solution for event handling. In addition, Extended Events provides the following features:

• A unified approach to handling events across the server system, while at the same time enabling users to isolate specific events for troubleshooting purposes.
  
• Integration with, and support for existing ETW tools.
  
• A fully configurable event handling mechanism based on Transact-SQL.
  
• The ability to dynamically monitor active processes, while at the same time having minimal effect on those processes.
  

If you want to use Extended Events to correlate data from the operating system and database applications, we recommend that you first have a working knowledge of ETW. ETW can be used in conjunction with Extended Events or as an Extended Events event consumer. The following topics provide a starting point for obtaining background information about ETW:

• Windows Events
  
• Improve Debugging And Performance Tuning With ETW
  

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.
  

Troubleshooting the cause of working set trimming

You are experiencing severe performance issues with your production server, causing client applications to time out. These issues appear to be transient, and performance returns to normal after 10-15 minutes.

You examine the SQL Server errorlog and find the following error messages:

"A significant part of the server process has been paged out. This may result in performance degradation. Duration: 300 seconds. Current memory utilization 34%."

"Non-yielding IOCP Listener."

You suspect that that SQL Server is not responding quickly enough to memory pressure on the server. When you check the memory with Task Manager, there appears to be more than enough available memory on the server. You attempt to connect to the database from SQL Server Management Studio, but the connection attempt times out. You are able to connect to the server by issuing the SQLCMD - A command from the Windows command prompt. This opens a session on the dedicated administrator connection.

You decide to use Extended Events to obtain more information. You create an Extended Events session that:

• Adds events for system memory signal and total server memory change.
  
• Directs the output to ETW. This output will be written to a file that is created on a drive that is not used for either the page file or by SQL Server database files.
  

At the Windows command prompt you execute an instruction to enable a Windows kernel ETW trace with all memory events. You allow both traces to run for several minutes and then close the extended events session and the Windows Kernel trace.

You use tracerpt.exe to correlate both the Windows trace and SQL Server ETW trace. You look for an event indicating that the system memory low notification was set, but do not find it. You instead see a high number of page faults from all processes on the server. You examine the events just before the paging and find that the working sets of all processes were trimmed in response to a memory allocation request from a driver.

Troubleshooting excessive CPU usage

You are investigating excessive CPU usage on your production system. You use various Dynamic Management Views (DMVs) to determine if the CPU usage can be attributed to queries that were run on the system. This examination indicates that most of the queries are ad-hoc user queries. The information obtained from the DMVs is not enough to diagnose the problem.

You create an Extended Events session that:

• Enables events for statement completion with predicates that specify CPU threshold.
  
• Has an action to only collect the query plan when the event fires.
  
• Writes any data that is collected to a file. This file is on a drive that does not contain any log or database files.
  

After starting the Extended Events session you examine the output and are able to determine that the cause of the CPU problem is a data type conversion between two commonly joined tables.

Troubleshooting deadlocks

You have been receiving reports from users indicating that certain applications are returning deadlock errors. To maximize the effectiveness of troubleshooting these problems, you decide to focus on the deadlocks that are hit most frequently. You create an Extended Events session that:

• Configures deadlock event tracking for the session.
  
• Specifies a target that aggregates based on an identifier for the deadlock.
  

You run the Extended Events session, and after additional deadlocks are reported you are able to obtain aggregated deadlock information, along with the complete XML deadlock graph for each source. Using this information you are able to pin point the most common deadlocks and start working on a solution.

Correlating request activity with Windows ETW logs

You are troubleshooting an application slowdown on your production server and are able to narrow down the cause to long disk-read times. You create an Extended Events session that:

• Adds disk reads as a session event.
  
• Sends the data that is collected to ETW.
  

After starting the Extended Events session you run a Windows ETW kernel trace. After 10 minutes you stop both sessions.

You use tracerpt.exe to merge the Windows trace and SQL ETW trace. From this merged trace you are able to correlate and track the read requests from SQL Server to the Windows kernel. This analysis indicates a long delay before the I/O request is returned back to SQL Server. Using this information you can conclusively determine that the I/O problem lies in the physical I/O path.

Concepts

Using SQL Server Extended Events

Other Resources

SQL Server Extended Events Concepts

Help and Information

Getting SQL Server 2008 Assistance