First introduced in SQL Server 2008, Extended Events is an event handling system, deeply embedded within the SQL Server database engine. The Extended Events feature is the next generation of tools to help troubleshoot and diagnose problems in SQL Server (previously the tool of choice was SQL Server Profiler). Here we will discuss the reasons for utilising the Extended Events feature in SQL Server 2012 and beyond.
Elements of Extended Events
When you wish to diagnose a particular problem in SQL Server using Extended Events you will use the following elements:
Session: Equivalent to a trace in profiler.
Package: A container for a collection of objects, SQL Server equates to the set of profiler events that could be captured.
Event: Equates to an event being captured in profiler e.g. RPC: Completed.
Action: Equivalent to the columns selected in profiler e.g. Textdata or Duration.
Predicates: Equivalent to filters in profiler e.g. databasename like ‘master’.
Extended Events advantages
Now that Extended Events are present in SQL 2012 in the GUI they are more convenient tool to use when troubleshooting. Extended Events take a reactive approach to collecting data, only sending data to the target when predicates are true. In contrast Profiler is less efficient because even with filters the event is processed.
Example of starting an Extended Event session in SQL Server 2012
Starting a new session will be achieved in SQL Management Studio by right clicking “Sessions” under Management/Extended Events.
Then you can choose the “New session wizard” and this will start as below:
For this example I used the query batch template and it recorded the main query competed events as below (rpc_completed and sql_batch_completed):
Then you can choose the equivalent of the columns you wish to collect data for:
Then you can apply filters as below:
And finally choose a location to save the session (if desired):
This will then start the session and capture the data:
The events captured will be much the same as when a Profiler trace is made you can click on one of the events to see the SQL Statement. And you can choose what columns to capture:
Once you stop this session, if you chose to save it, you can then open the file within SQL Server Management Studio and analyse and sort by for example query duration to find the longest running queries.
Advantages of using Extended Events
There are four main advantages of starting to utilize Extended Events:
1. Extended Events are lightweight. Microsoft has stated that running 20,000 events a second on a 2Ghz Pentium with 1GB ram will utilize less than 2% CPU.
2. Extended Events include events outside the SQL server engine, for example it’s possible to monitor resource usage such as CPU.
3. Extended Events are available to run within SQL Server Management Studio.
4. SQL Server Profiler is currently deprecated, and has been since SQL Server 2012, so it’s best to become familiar with Extended Events before profiler is discontinued.
Conclusion
Extended Events was first introduced in SQL Server 2008, however the new GUI introduced in SQL Server 2012 makes it far more accessible and intuitive to use. As the SQL Server versions move on, it may become more of a requirement to start using Extended Events, and so discovering some of the extra features it contains to troubleshoot and diagnose issues in SQL Server is well worth the effort.
By Alistair Bartrop, Senior SQL Consultant
Have a comment? Let us know below, or send an email to [email protected]
