What are extended events?
Extended Events first appeared with the release of SQL Server 2008.
As a successor to the bulky and somewhat resource intensive SQL Profiler and SQL Trace tool, Extended Events was a new lightweight tool which can be used as and when required to gather useful metrics and information in a much more lightweight and non intrusive manner due to its architecture.
Typical use cases for Extended Events would include:
- Troubleshooting deadlocks
- Identifying poor performing queries
- Capturing key wait events
- Capturing TempDB spills
- Collecting general system activity
Extended Events Architecture
Extended Events use an asynchronous model to minimise the impact put upon the SQL Server. This means that the capture of the metrics is a separate process to that of the filtering and processing of data. This is different to that of its predecessors SQL Profiler and SQL trace.
Extended events can even be integrated into the ETW (Event Tracing For Windows) logging functionality to correlate with data outside of SQL Server.At a high level, Extended Events will consist, or include the following:
- Package – Container for Extended Events
- Package0 – Extended Events system objects. This is the default package
- sqlos – SQL Server Operating System related objects
- sqlserver – SQL Server related objects
- Event – Events are monitoring points that trigger when a point is reached within the database engine’s code base. At the point in which the event fires, it can contain useful information regarding the system state which can be then used for tracing purposes and or further triggering actions. Events will have a specific categorisation, similar to the ETW logging events, defined by the following properties:
- Channel – The channel identifies the target audience for an event. (Admin, operational, Analytic and Debug)
- Keyword – Application specific categories that make it easier to specify and retrieve specific events
- Predicate – Predicates define the boolean conditions for when an event should fire
- Action – Actions allow us to collect additional information for when an event is triggered. Actions only occur after a predicate has been evaluated and an event is going to fire. Actions can bring a overhead to them as they function synchronously using the same thread of that of the event in question.
- Targets – Targets consume event data after the event has fired. Targets are used to store event data, analyse/aggregate it or start a task. The following targets exist:
- Event Bucketing
- Event File
- Event Paring
- Event Tracing for Windows
- Ring Buffer
- Synchronous event counter
- Maps – Maps are collectors that convert integer values from an internal map into readable text values
- Types – Data dictionary of data types used by Extended Events
Demo – Using Extended Events To Capture Deadlock Information
We will now go through a demonstration to show how we can use Extended Events to capture key deadlock information. Capturing detailed deadlock information using Extended Events would very much be a common use case. Lets give it a go ourselves.
The first step is to create ourselves a new session within Management > Extended Events > Sessions:
We are now presented with the new session window. Here, we have a few options which we need to be aware of. First, we need to give our session a relevant name. Additionally, we will select the options to start the session at server startup and to start the session immediately after the session has been created:
Next, we need to select Events from the left side menu options. This is where we will specify what events we wish to captured. Within the Event Library search field, we will enter the text “deadlock” to filter down the events accordingly:
In order to select the events, we need to highlight the required one and click the arrow to move it into the Select events window. For this demo, we will select the three following events:
- lock_deadlock – This event is when an attempt to acquire a lock is cancelled for the victim of a deadlock
- lock_deadlock_chain – This event is when an attempt to acquire a lock generates a deadlock. This event is raised for each participant in the deadlock
- xml_deadlock_report – Produces a deadlock report in XML format
Once we have our three events within the ‘Selected Events’ window, highlight all three and select the configure button. This will allow us to configure options for our events. On the Global fields (Action) tab, scroll down and tick the sql_text option:
We now need to select the Data Storage option on the left side menu. Within this section, we will specify our target(s) for the event data. For this example, we will select event_file from the drop down menu for the Type column. Additionally, we can browse to a file location to save the file to and give our file a relevant name:
We can now select the OK button to finalise the setup. Once we have done this, we will be able to see our newly created session listed under Sessions, along with event file which we chose as our target:
Now that we have our new session running, we need to generate a deadlock. We are not going to go into ‘how to create a deadlock’ here in this post, however, should you need an example of this, check out Brent Ozar’s post here which gives a great example.
After we have experienced (created in this instance) a deadlock, we can use our session to identify what happened. Lets open the session data by opening the event file:
Once we open the file, we will be presented with the window shown below. As you can, we have many events detailed:
So what information have we been able to capture? Lets take a look
lock_deadlock_chain – As you can see, we have two entries for this. Each entry details the sessions which were involved in the deadlock, in our example we can see that both sessions 59 and 60 were involved in the deadlock:
lock_deadlock – this event details information related to the victim chosen when the deadlock occurred:
xml_deadlock_report – this event allows us to capture the xml data relating to the deadlock. Of course, using the captured xml data will prove very useful when trying to diagnose the cause of such deadlocks. Here is the xml data represented as a deadlock graph:
Extended Events – Conclusions So Far
Of course, we have only briefly touched on Extended Events within this blog post. The sheer number of events which can be monitored and captured is breathtaking. Add in the long list of actions and targets to select from too and it can all get a bit daunting.However, with that said, its all very exciting too. Once i get well versed with Extended Events, im sure they will quickly become a daily staple in my SQL toolbox. I know for many DBAs, myself included here, they have been an area which has been ignored and almost forgot about at times. However, after researching some of the topics discussed in this post, i know how useful they will prove to be. So, bye bye SQL Profiler, there is a new guy in town (at least for me).
p.s. I didn’t really use SQL Profiler
