Bye Bye SQL Profiler – Welcome Extended Events

SQL Profiler for long has been THE go to tool for tracing the queries and if you are a SQL Developer it would be a miracle if you haven’t used it at all. In every facet of BI stack, this comes into play be it a SSIS package that is currently run, or in understanding a blank SSRS report or a Cube that is getting processed at the background one can hook up a trace as the first line of debugging.

The biggest problem with this though is that it ALWAYS had to be used within a limited time. You extend it longer than intended and all the activities on the SQL Server tend to slow down as it is a resource-intensive operation.

Sensing this I believe, Microsoft first came out with Extended Events with SQL Server 2008 version. It was horrendous to say the least. At that time this had to be entirely done through bunch of scripts, joining multiple tables with addition of XQuery to grab the actual data that we need. The learning curve to get this done was huge. I admit I had read various tutorials, did some practice but when I really wanted to use it I used to get cold feet. Without googling at least twice, this was a no-go and I used to fall back on SQL Profiler.

SQL Server 2012 onward, Microsoft has introduced GUI for Extended Events making it now a real easy breeze to work with. This has now been the de-facto tool that I use for tracing the queries now.

Here is how one can go about setting one up and using it to trace your SQL queries –

Log on to SQL Server and go to Management->Extended Events->Sessions. Right-Click on ‘New Session Wizard’ as shown below –

1_Opening_NewSessionWizard.png

Click ‘Next>’ on the Introduction screen. In the ‘Set Session Properties’ tab give a name to the Session say – All SQL Queries as shown below and click on ‘Next>’

2_SetSessionProperties.png

In ‘Choose Templates’ tab click on ‘Use this event session template:’ and in the drop down select ‘Query Batch Tracking’ as shown below and then click on ‘Next >’ –

3_ChooseTemplate.png

In the ‘Select Events to Capture’ tab across the ‘Selected events’: you can remove error_reported, rpc_completed and only have sql_batch_completed in it as shown below and click on ‘Next >’. You can remove the other two that come by default by just clicking on them and using the ‘<‘ arrow button –

4_SelectEventsToCapture

Keep the defaults as they are for the subsequent screens and click on ‘Finish’ in the ‘Summary’ page. In the ‘Create Event Session’ page that pops-up, to start seeing your results immediately, you are provided with two options. Enable them and click on ‘Close’ as shown below –

5_CreateEventSession.png

Upon doing that the trace is now up and running waiting for the queries to start. Here is the output of showing one query that was run against one database in the server –

6_QueryResult

As can be seen this one is more cleaner and more easier to read without the unnecessary redundant information that used to come along from SQL profiler.

This is just a tip of the iceberg from what we can achieve using Extended Events. There is an ocean out there to explore but for those who are seeking to trace out a query this should be a good start.

 

 

 

 

3 thoughts on “Bye Bye SQL Profiler – Welcome Extended Events

  1. Nice article Karthik. I guess the extended events GUI was improved keeping DBA’s pain in mind rather than developers πŸ™‚

  2. That was a neat alternative to the complex Profiler way to analyse the queries. Thanks for the tutorial. πŸ‘

  3. @Krishna – Agreed, Totally hassle free experience of using GUI. πŸ™‚
    @Phani – Thanks. Keep look out for future posts on many more benefits that this Extended Events can bring. πŸ™‚

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s