Additional menu

SQL Consulting

SQL Consulting: SQL Server Is Our Only Business.

SQL Server Extended Events

posted on

I went to a SQLSaturday event last week and decided to attend Janis Griffith’s presentation on SQL Server Extended Events (XE). I thought this presentation would be a good opportunity to catch up on a SQL technology I did not know much about. Little did I know that I was headed for a panic attack.

The little I knew about XE was from versions SQL 2008 and SQL 2008r2. About all I knew was that it worked much the same as SQL Profiler and SQL Trace but gave you many more events to trace and more granular control over the tracing process.

I did not consider Extended Events ready for prime time when I first looked at it , so I hadn’t spent any time getting proficient in XE technology. For one thing, there was no GUI interface for XE in SQL 2008 or 2008r2 as there is for SQL Profiler. Every step in creating an XE session has to be coded by hand.

Here is an example of creating an XE session to capture a single event. If you think this looks fun and exciting, you are a much better and more eager programmer than I. When I saw the amount of work it was going to be to run a simple one-event trace, I dismissed XE as something I might want to learn in some distant future but I was not in a hurry to tackle it any time soon.

I was comforted somewhat when I learned that there is a GUI for XE embedded in Management Studio for 2012 that makes it a lot simpler to work with. However Janis casually mentioned something that froze my heart. SQL PROFILER HAS BEEN DEPRECATED IN SQL 2012!!. Janis told us that it will not exist in a future version of SQL Server.

I fought back the panic that possesses not-quite-so-young guys when they realize they have to learn something new. When my panic subsided a little I mentally rejoined the presentation. Listening to Janis explain a bit more about XE, I began to understand that XE was in fact a significant improvement over Profiler at least in terms of functionality and flexibility if not in user-friendliness.

SQL Profiler has been an essential tool in my toolbox for many years. I have used it to solve problems on thousands of SQL Servers. The thought of it disappearing gave me a sudden shock, but if the replacement is significantly better than the original, I am ready to learn what I need to know. Change is the name of this game and I am ready to move on.

Here are some of Extended Events’ advantages over SQL Profiler that helped reconcile me to learning more about using XE:

  • There are a HUGE number of events available in XE for your monitoring
  • XE can correlate SQL Server events with OS events using Event Tracing for Windows.
  • It can use asynchronous processing of events to minimize the performance impact of monitoring.
  • You can direct output to a number of different targets i.e files, the sql log, the OS event log,, ring buffers, etc.
  • You can assign actions to be performed based on a particular event firing.
  • The filtering capabilities are much more flexible than in SQL Profiler.
  • It has a GUI in SQL Server 2012 and is much easier to use.

Moving On

On the way home from the conference I remembered a problem I was having trouble solving using SQL Profiler. A client of mine was encountering infrequent but serious errors and we didn’t know what code was causing the error or even which of the several dozen databases it was coming from. Consequently, we were unable to filter out much of the tirrelevant traffic so SQL Profiler was generating about 10 gb/hour in the trace file. We could not leave it run without periodically stopping and deleting the trace files after we ascertained that they did not contain the error event we were looking for.

That led me to try solving the problem using Extended Events on SQL 2008r2. It took some reading, some experimenting and some borrowing of code from the internet, but Voila!. In an hour I had a solution that didn’t generate any appreciable amount of data, had no noticable impact on performance and directly linked the error with the code that caused the error.

  • CREATE
  • EVENT SESSION sqlconsulting_session ON SERVER
  • ADD EVENT sqlserver.error_reported (
  • ACTION (sqlserver.sql_text)
  • WHERE ERROR = 8134
  • )
  • ADD TARGET package0.asynchronous_file_target
  • (set filename = ‘c:\newfolder\sqlconsulting2.xel’ , metadatafile = ‘c:\newfolder\sqlconsulting2.xem’)
  • GO

In this example I am tracing a divide by zero error because it is easy to generate in a test environment. The XML output looks like this:

  • <event name=”error_reported” package=”sqlserver” id=”100″ version=”1″ timestamp=”2012-11-20T18:56:33.936Z”>
  • <data name=”error”>
  • <value>8134</value>
  • <text />
  • </data>
  • <data name=”severity”>
  • <value>16</value>
  • <text />
  • </data>
  • <data name=”state”>
  • <value>1</value>
  • <text />
  • </data>
  • <data name=”user_defined”>
  • <value>false</value>
  • <text />
  • </data>
  • <data name=”message”>
  • <value>Divide by zero error encountered.</value>
  • <text />
  • </data>
  • <action name=”sql_text” package=”sqlserver”>
  • <value>SELECT 200/0</value>
  • <text />
  • </action>
  • </event>

The TakeAway

Extended Events is part of Microsoft’s push to create a single model for all monitoring and event management.on the Windows platform, so it is not going away any time soon.

If you are someone who needs to use SQL Profiler on a regular basis, the hand is writing on the wall. Relatively soon you will need to know how to use XE in order to manage a SQL Server platform. It makes sense to learn XE now rather than later. You will be able to use its greater functionality to solve problems that can’t be solved using Profiler.