Blog

Why Extended Events for SQL Monitoring?

January 09, 2018 | Ken Leoni

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

Session Summary - Extended Events.png
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:

  • Events - Which XEs are designated for capture

  • Predicates – Filtering that determines which XEs are actually collected (i.e. the criteria could be to collect XEs for a specific database or based on a metric with an XE exceeding a threshold)

  • Targets – Where to aggregate and output the collected data. (i.e. to a file or an in memory structure)

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.

 

Listing Extended Event library using DMVs

Query the sys.dm_xe_packages and  sys.dm_xe_objects DMVs to return a list of all Extended Events related to SQL packages.

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

Configuring a Session with Extended Events to diagnose long running queries

In the sample above we are selecting Events for long running queries
  1. Search for all events with  _completed as part of the event name

  2. The rpc_completed event has already been selected

  3. We are also interested in the sql_batch_completed event.  

    Note the detailed description of the Event Fields returned as part of the sql_batch_completed event.

  4. Now that we’ve identified the Extended Events we want to collect; the next step is to further optimize the event data that is to be returned. For this we will need to further Configure


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.

Session Properties -Global Fields.png
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.

Session Properties - predicate.png
Filter allows user to granular control as to when an event is collected

 

Order is extremely important when configuring the Filter (Predicate).

  1. First, we want to limit our Extended Events only to those for a specific database, in this case database id = 9

  2. Second, we want only SQL Batch Completed and the RPC Completed Events where the duration of the query is greater than or equal to 300,000 microseconds as we are interested in queries taking longer than 1/3 of a second.

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 are where Session data is written and aggregated
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: When creating a Session you have the option of using “New Session Wizard” or “New Session…”, the Wizard is limited to two targets – ring_buffer and event_file.

 

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?

 

sql batch completed extended event.png
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

 Learn More About  Monitoring SQL Environments

 

 

We value your privacy and will not display or share your email address

Sign Up for the Blog

Heroix will never sell or redistribute your email address.