The use of SQL Server Extended Events (XEs) are a critical component to any successful SQL diagnostics effort, especially when deep dive SQL analytics and tracing are needed and the use of a native SQL Server tool is preferred.
XEs provide a nonintrusive way for DBAs to collect event-driven data which contains the key metrics necessary to determine the cause of performance issues within a SQL Server instance and its databases. The simplest way to work with XEs is to create Extended Event Sessions through SQL Server Management Studio (SQL Server 2012 and up)
Object Explorer-->Management-->Extended Events-->Sessions
Initialization of an Extended Event Session to identify long running queries
|
Extended Event Sessions drive the collection and output of Extended Events and are configured with the following settings:
|
Before delving into the configuration of Extended Event Sessions let’s explore why Extended Events should be part of a DBA’s repertoire.
Extended Events support newer versions of SQL Server and Azure SQL
Although SQL Server Profiler has been a mainstay for DBAs, its days are numbered, especially with each subsequent release of SQL Server and Azure SQL Database.
DBA’s have relied on SQL Profiler since SQL Server 7 (some 20 years ago!) and while it certainly has been a good run, Microsoft announced Profiler’s deprecation with SQL Server 2016, which means it is going to be removed “eventually”.
Even if we assume that SQL Profiler sticks around for a while longer, the reality is that Microsoft hasn’t enhanced the technology since 2008.
|
If you’re interested in natively diagnosing problems related to any of the following technologies your only option is to leverage Extended Events.
|
Keep in mind the number of Extended Events have also been steadily increasing with each version of SQL Server. There are more metrics available and with finer levels of granularity with each release. At last count SQL Server 2016 has some 1,031 Extended Events.
Extended Events use minimal SQL Server resources
XEs exhibit low overhead because they are built natively into SQL Server. The advantage of including the XE code as part of SQL Server Engine is that XE data can be efficiently accessed and delivered. The traditionally method of externally connect to the SQL Server to gain access the data (i.e. SQL Profiler) is simply too resource intensive.
In addition, XE data is processed much more efficiently. If we take a step back and again look at SQL Profiler, one of the knocks against the technology’s is its high overhead - which is due in part to the sheer volume of data that it collects. DBAs have limited control over the data collection behavior, therefore SQL Profiler is typically run only long enough to get what is perceived to be a valid data set. The DBA is then tasked with slogging through quite a bit of data in order to hunt down the problem.
On the other hand, XEs provide an efficient mechanism to limit the data collected to only that which is necessary to diagnose the problem. DBAs can target the data to be collected using filtering (also known as Predicates) which is done at run-time .
Let’s take a simple example where a DBA is targeting queries that are taking too long (i.e. longer than 1/3 of a second). They can either collect and archive all queries for a given database and then later look through the data (SQL Profiler) or they can collect data only for those queries that meet the 1/3 second criteria at run-time (Extended Events). Limiting the collection to only the data that meets a specific criterion allows the diagnostics to run for a longer period of time. In addition, Extended Event Sessions are more likely to be run in a production environment because the technology is less likely to interfere with operations.
Extended Events Self Document
One of the more helpful characteristics of XEs is that they self-document, meaning not only does the event name and description tell you what the Extended Event’s purpose is, but the Extended Event Sessions interface shows in plain English the metrics that are collected as part of the event.
The entire Extended Event library is also held in Dynamic Management Views (DMVs), which of can be sorted and searched. This is invaluable, especially when you have some 1,000 + Extended Events in SQL Server 2016 from which to choose from.
|
To return a list of all Extended Events related to SQL packages, query: |
Configuring a Session to collect Extended Events
The purpose of a Session is to collect and output user selected XEs. The simplest way to create a Session in SQL Server Management Studio
Object Explorer-->Management-->Extended Events-->Sessions
You will most definitley need to do a bit of homework to determine which Extended Events you would like to go after. Extended Events are analogous to having a giant toolbox, however, you still need to figure out which tools are appropriate to diagnose a problem.
Selecting Extended Events for capture
At the heart of Extended Event Sessions is identifying the events for capture. This does require a working knowledge of the available events. So knowing how to sort and search events is a must.
| Configuring a Session with Extended Events to diagnose long running queries |
In the sample above we are selecting Events for long running queries
|
Optimizing Extended Events
The sql_batch_completed event returns Event Fields batch_text, cpu_time, duration, logical_reads, physical_reads, and more. However we may want to append additional detail as part of the event (i.e. session id, database name, username, and query hash) as part of the diagnostic
Global Fields or (Actions) - the "action" is to append additional fields to the event.
| Global Field (Actions) allow users to append additional information to an event |
Here we can see that additional Global Fields (selected from the right pane) have been appended to both the rpc_completed and sql_batch_completed events. The additional Global Fields include the database id, database name, nt username, query hash, server principle name, session id, and sql text. This additional detail will be quite helpful as part of the diagnosis.
Filter (Predicate) allow users to limit the evaluation and capture of events based on the values of the Event Fields.
| Filter allows user to granular control as to when an event is collected |
|
Order is extremely important when configuring the Filter (Predicate).
Note: Reversing the order would be more resource intensive because we would be evaluating Batch Completed and the RPC Completed Events > 1/3 seconds for all databases first and then filtering for database id 9. |
Output and Aggregation of Extended Event Session
Once we've configured what events to collect and when to collect them the next step is to configure the aggregation and output of XE data. This is accomplished via “Targets”. There are several types of Targets and a session can output to multiple targets simultaneously 
| Targets determine where Session data is written and how it is aggregated |
|
etw_classic_sync_target - integrates XEs into Event Tracing for Windows (ETW). ETW is used here to correlate data between the operating system and database. It is strongly recommended that you have a good working knowledge of ETW before configuring this target. event_counter – is very helpful in situations where your main concern is returning the number of times a specified event has occurred. (i.e. return the total number of long duration queries) event_file - writes the event session output to a file for later review and analysis histogram – a more robust option to event_counter, it provided more aggregation by providing counts based several items including Event Fields and Global Fields. (i.e. return the total number of long duration queries by database) pair_matching – works by creating pairs of events that go together (i.e. a lock acquisition followed by a lock release). When events are “paired” they are then discarded. pair_matching is invaluable in helping to detect start events that have no corresponding end event. (i.e. a lock_acquired event occurs but a matching lock_released event doesn’t occur on a timely basis) ring_buffer - writes the event session output to an in memory data structure. It is useful for simple event testing as only a limited amount of data can be maintained in memory and the data disappears once the session is stopped. Note: |
Putting it all together
So you've gone thought the due diligence of researching and selecting the events, appended data to the events, and filtered the events. What is the end result?
| Display sql_batch_completed event |
For demonstration purposes we are going to keep things simple and display from the ring_buffer.
We can see the entire event with all its fields. The red fields are part of the generic sql_batch_completed event, while the remaining Global Fields were appended.
Conclusion:
SQL Server Extended Events offer a powerful mechanism to diagnose SQL performance issues. There is most definitely a learning curve associated with the technology, but it is well worth a closer look.
Microsoft continues to advance the Extended Events Session interface to make the leveraging of Extended Events easier, but it most definitely is not an out-of-the-box solution. If you’re willing to invest the time and effort you can reap significant benefit!
Want learn more?
Learn how an improved monitoring strategy can help optimize database performance and reduce the amount of dedicated resources needed for database monitoring and management
