SQL Server has this general event-handling system well known as Extended Events, which supports correlation of data from server. It also supports correlation of data from OS and database software applications where the output has to be directed to Event Tracing for Windows for acquiring correlation of event data.
An application has execution points, which helps inside and outside an application. Outside the application, implementation points provide checking utilities with information regarding the behavior and performance characteristics of the monitored application. Extended Events in SQL Server enables server to check and respond to specific engine events occurring.
Extended Events use event data outside an application and the data can be;
- Tracing tools like SQL Trace & System Monitor.
- Users administrating a product.
- Logging tools like Windows event log or SQL Server error log.
Extended Events Design & Features
Extended Events in SQL Server are integrated with below mentioned key design parts;
- This SQL Extended Events engine enables engine for binding any event to target, as engine is not constrained by event content.
- These are separated from event consumers known as targets in Extended Events. Hence, any target can receive any event. Moreover, any event, which is raised, can be automatically used by target providing event context.
- When the event is fired, they can be different from action to be taken. Any action can be related with any event.
- Events can be filtered making Extended Events infrastructure flexible.
Extended events in SQL Server synchronously create event data providing solution for event handling. Extended Events deliver below mentioned features;
- It is an efficient approach for handling events on the server system.
- It also enables users to divide particular events for troubleshooting.
- It supports the ETW tools and implements configurable event handling mechanism.
- It does not affect the processor speed or other activities.
Where Are Extended Events Used?
Extended services can be utilized in various scenarios especially for monitoring and troubleshooting. Some of the scenarios are mentioned below where these Extended Events provides valuable data for rectifying issues:
- Troubleshooting the reason behind working set trimming.
- Monitoring and rectifying deadlocks.
- Troubleshooting blocking and tracking DDL operations.
- Logging missing column statistics.
- Troubleshooting excessive CPU usage.
- Correlating request activities associated with Windows ETW logs.
Management of SQL Extended Events
SQL Server 2012 integrates GUI in SQL Server Management Studio for creating and managing these Extended Events. Older versions than SQL 2012, Extended Events were only created using T-SQL. Extended Events in SQL Server were introduced with the release of SQL Server 2008. SSMS interface for these Events will not be available unless you connect to an instance of SQL Server 2012. New UI implementation has boosted its usage in new versions as compared to older versions. It has been expanded positively and SQL Trace has been modified. Analysis services were also added to Extended Events allowing data collected from any database engine to be correlated with data collected from Analysis Services in an easy-to-manage architecture.