What are the SQL Server Extended Events?
SQL Server Extended Events has a highly scalable and highly configurable architecture that allows users to collect as much or as little information as is necessary to troubleshoot or identify a performance problem.
The impact of collecting the data on SQL server will be minimal compared other available tools/components.
The extended events existed in SQL Server 2000 in perf monitor tool as Event Trace Sessions under data collection sets. It was introduced in SQL Server 2008 and more XEvents were added in higher SQL Server versions.
The extended events started with SQL Server 2008 and growth of XEvents given below
Extended Events Components
A package is a container for SQL Server Extended Events objects. There are three kinds of Extended Events packages, which include the following:
• package0 – Extended Events system objects. This is the default package.
• sqlserver – SQL Server related objects.
• sqlos – SQL Server Operating System (SQLOS) related objects.
The SQL Server Extended Events engine is a collection of services and objects that:
• Enable the definition of events.
• Enable processing event data.
• Manage Extended Events services and objects in the system.
• Maintain a list of Extended Events sessions and manage access to that list.
Extended Events introduces a much better system of filtering using Predicates that are applied at the individual Event level, allow for short-circuiting of evaluation, and provide the ability to create complex groups of independent criteria, ensuring only Events of interest are captured by the Event Session.
Additional information about the event
SQL Server Extended Events Targets are event consumers. Targets can write to a file, store event data in a memory buffer, or aggregate event data. Targets can process data synchronously or asynchronously.
The Extended Events design ensures that targets are guaranteed to receive events once and only once per session.
Data types for event columns, actions or global predicate sources
Lookup for specific values (i.e. WaitType99 = “Network_IO“)
Collection of Events, Actions, Predicates, Targets
Where can we use extended events
• Almost everything can be accomplished similar to SQL Trace & Profiler
• Using system-health (totally for free even historical data) !
• Find out lost connections (queries that did not end)
• Get Waits and Latches of specific queries/procedures
• Trace problematic page splits
• Latch & Spinlock-Analysis
• Capture the N‘th occurrence of a specified event
• Trace details of the new technologies that came into SQL Server since 2008 (!)
• Correlate OS and SQL Server Activity End-to-end (E2E) tracing using Client API
Correlation ID between client and server from SNAC11 / TDS 7.4 onwards
SQL Trace vs. XEvents
SQL Server Extended Events are not a replacement for Profiler/Server Side Trace. Extended Events are a lot more than that. Extended Events provide a set of methods for collecting different events from SQL Server and correlating those different events within a single tool. It’s possible to grab many sets
Deadlocks + waits
Waits + lock graph
TempDB spill + query plan
The number of events in Profiler has remained the same since SQL Server 2008. The number of events in Extended Events (XE) has more than tripled.
XE gives you multiple options to save and view the data.
XE sessions are easily scriptable & portable compared to SQL Trace
Database Access without Auditing
• A very common question in forums is how one can track database- or even object-access in Standard Edition
• Locking is the key
• Every database access requires an S-Lock
• Tables will be locked with SCH-S or Schema Modification-Locks SCH-M – even under Read Uncommitted J
Waits on database & procedure level
• sys.dm_os_wait_stats provides server wide statistics of the internal waits
• With the Event „wait_info“ + filtering one can do wait-analysis down to procedure-level
The default XEvent System_health session is available in SQL Server and collects the as given below.
• Errors with severity >=20
• Memory errors
• 17803, 701, 802, 8645, 8651, 8657, 8902
• Non-yielding issues
• Lock wait types held for > 30 seconds
• Latch wait types held for > 15 seconds
• Preemptive and external waits > 5 seconds
• Target: Ring Buffer + File (SQL Server 2012 onwards)