skip to Main Content

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 [email protected]

About the Author

BC Technical Team

Our technical team contribute with blogs from their respective specialities, be that Azure, SQL, BizTalk, SharePoint, Xamarin and more. From the lead architect to developers and testers, each person provides content straight from their experience.

Our Technologies

HAVE YOU SEEN OUR NEWSLETTER?

ABOUT US

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

01342 410223

[email protected]

 

CONTACT US






This form collects your name and details so that we may answer you. Check out our Privacy Policy for full details on how we protect and manage your data.

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

© 2020 Ballard Chalmers Ltd. All Rights Reserved

Back To Top