The Art of SQL Server Database Administration, Development, and Career Skills for the Technically Minded
When Extended Events first came out in 2008, I – like many people – took a look and sheepishly walked away. I knew what was being communicated was important and cool, but it was just too much to wrap my head around. A couple years later I tried again. It started to make sense. Once I broke through the first couple barriers I was in.
Microsoft is confident enough in the collection of events and features they provide in 2012 to announce deprecation of trace and profiler. In other words… now is the time to learn it. The tools are better, the documentation more accessible, and it’s much less intrusive than your other options.
The purpose of this post is to demystify the topic – this is pure 101 stuff.
If you want an excellent white paper on the topic front to back I’d recommend the white paper written by Jonathan Kehayias on MSDN and of course books online and the myriad of samples there (for example this little gem on what events line up with the sql trace classes you might already be used to).
What is an event?
Most popular apps are event driven. In other words, much of the logic flow is centered around events such as mouse clicks, button pushes, touch screen swipes etc. It’s easy to recognize these user actions as “events” but the concept in computer science goes deeper into the code as well.
Consider a progress bar. Internally significant milestones (events) are sent as notifications between the thread doing the work and the thread updating the UI. Who decides what milestones are significant? The answer is the application designer.
The same thing is going on inside the SQL engine. The designers of the product have identified a collection of “events” that they deem significant. Extended Events is simply the name of the framework that has been designed by the SQL Server product team to provide, consume, and organize the data related to these events.
A sample event:
In SQL 2012 you can open up the Extended Event New Session GUI to see what events the product team has made available. Extended Events can be found under the management node in SSMS. Right click the node and select ‘New Session”.
Deprecation_final_support – shown below – is an event we recently used to analyze our 2008 production server for a planned upgrade to 2012. We wanted to capture every time SQL Server noticed something being run that the product wouldn’t support in the next version.
We didn’t have the luxury of the GUI as we were working with SQL Server 2008 but the theory is all the same.
You’ll notice as you browse that each event carries it’s own set of meta-data. The fields available for this event are feature, feature_id, and suggested workarounds (message). In other words… when you capture this event – these fields and their related information automatically come with it. Most events have more… a lot more.
Additional data and “actions”:
You can specify additional actions to be taken when an event occurs. You could, for example, request data about the CPU when the event occurred. This information isn’t contained within the event like the meta-data above but can be captured by the engine for correlation with the event.
In our case we wanted to collect the sql statement (sql_text) that caused the event. This additional action allowed us to assist the dev team in identifying and replacing the deprecated syntax reported by XEvents.
The list of available actions is pretty impressive. Check my example statement below to see how you add actions to your session.
Saving to targets:
So you have an event your interested in and you’ve selected a couple additional actions for correlated data. How do you consume it?
The target is where you want the engine to place the data the engine has collected. You have some decisions to make as there are tradeoffs in your target options.
Books online has the complete list of available targets including scope and purpose of each. There are a few more than what I’ve mentioned above. One thing to keep in mind when selecting a target is if it is a synchronous or asynchronous target.
Asynchronous vs Synchronous:
My wife establishes a list of chores for me to accomplish most weekends. She might want to be notified when milestones are reached or interesting events are encountered. There are two ways my wife can be notified? I can either tell her myself. “Hey babe I’m almost done – how ’bout some sugar”. Or, one of my kids might tell her. “Mom, dad fell off the ladder – he’s saying a lot of naughty words”.
The first example is synchronous. In other words, I have stopped what I am doing to report on my progress. The second example is asynchronous. I’m still working on my task – injured though I may be – while my kids report back to my wife.
The targets consume event data in similar fashion, either on the same thread the work is being done by or a separate one. Books online identifies which are which. Since synchronous targets require resources from the same thread the work is being done on there can be a small amount of overhead.
Setting up a session:
The event session is where it all comes together.
CREATE EVENT SESSION [Deprecated Features] ON SERVER ADD EVENT sqlserver.deprecation_final_support ( ACTION(sqlserver.sql_text) ) ADD TARGET package0.event_file ( SET filename=N'C:\Temp\Deprecated Features.xel' )
This will store the events in the event_file target along with the sql_text of the causing statement.
(Hint: If your working with 2008 try the asynchronous_file_target instead).
All that is left is to start the session:
ALTER EVENT SESSION [Deprecated Features] ON SERVER STATE = start
Once you’ve collected all you are interested in you can stop it with the same alter statement but STATE = stop.
If you’re working with 2012 you will see under the session node in SSMS that Microsoft has a few running by default. A lot of really useful information is already being captured. The system_health event for example is a tremendous resource of internal information.
Looking at my create statement above you might be wondering what package0 is. SQL Server groups events, actions, targets and other XEvent objects such as predicates ( a way to filter specific events captured ) into packages. I’ll point you to books online for a thorough dive into why SQL organizes them this way and what packages are available.