ballardchalmers - home

Contact us 01342 410223

Blog

Extended Events in SQL Server 2012

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:

Image 1 Extended events in SQL Server 2012 Blog.jpg

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):

Image 2 Extended events in SQL Server 2012 Blog.jpg

Then you can choose the equivalent of the columns you wish to collect data for:

 Image 3 Extended events in SQL Server 2012 Blog.jpg

Then you can apply filters as below:

 Image 4 Extended events in SQL Server 2012 Blog.jpg

And finally choose a location to save the session (if desired):

 Image 5 Extended events in SQL Server 2012 Blog.jpg

This will then start the session and capture the data:

 Image 6 Extended events in SQL Server 2012 Blog.jpg

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:

Image 7 Extended events in SQL Server 2012 Blog.jpg 

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 enquiries@ballardchalmers.com

Comment on this article

About

Ballard Chalmers is one of the UK’s leading enterprise software engineering companies, specialising in development for the Microsoft Enterprise Application Platform. We are principally dedicated to software development and integration for the Microsoft Cloud, as well as on-premises and hybrid systems. Our expertise is in Microsoft .NET, SQL Server, BizTalk Server and SharePoint Server.

Let's talk

  01342 410223

Find out more

Thank you for contacting us.

We will be in touch with you shortly.