SQL Server, Troubleshooting

SQL Profiler vs XEvents: Quick Start

Overview

As mainstream support for SQL Server 2012 comes to an end this summer (2017), somewhere between 60% and 80% of us are still on SQL Server 2008 R2.  My bet is that means most of us are still using the tried and true SQL Server Profiler for troubleshooting.  Despite some amazing blog posts and speaker sessions, lots of us are still not using Extended Events (XEvents or XE) on our newer instances.  We know that Profiler is deprecated, and we know that there are a kajillion more XEvents than Profiler Trace events.  We know that Profiler puts a much bigger strain on the system, but Profiler is so fast and easy to setup!  Or is it?  Let’s compare them, assuming you want to use the GUI and don’t have templates saved on every server.  I’m also not going to use the XE New Session Wizard, which may have a different click count.

Say we get the dreaded “the system is slow” call, and we enter panic mode.  Maybe we’re fortunate enough to have an elaborate baseline; but, before we begin a time-consuming investigation of the baseline data versus what has been captured recently, we want to quickly see what is happening right now.

Setup Compare

I used a local copy of SQL Server 2016 Enterprise Edition for this comparison, and I could’ve sworn setting up an XE was much more tedious than setting up a Profiler trace.  I was wrong.  It was partly just perception, and it was partly learning how to select fields for multiple events at the same time with XE.

Profiler = About 24 Clicks

XEvents = About 26 Clicks

The details follow.  You may see additional ways to improve efficiency.

Profiler – Setup

In SQL Server Management Studio (SSMS), we start out with 4 clicks to get to the Trace Properties window.  Tools –> SQL Server Profiler –> Connect –> Events Selection:

TraceProp

It will take 6 more clicks to uncheck the unwanted events, add the TextData column to RPC:Completed, and “Show all columns”.  This narrows down the properties page to look like this:

Trace2Events

Next we filter on duration, which will take 5 clicks – not including the typing of the actual number.

TraceFilter

I like to include a handful of additional columns such as database name, host name, error and others.  This adds another 8 clicks.

TraceCols

Finally, a click of the run button brings us to a total of 24 clicks.

Extended Events – Setup

Now the setup of an Extended Event session.  In SSMS, there are 3 clicks to start off:  Management –> Extended Events –> New Session

XEStart

After typing in the name, it takes 3 clicks to filter down to the 2 events we want to capture.  Events –> Search on “completed”

XESearchEvents

Once we have the 2 events, we can select both of them and choose the fields we want in 12 clicks.

XEFields

It then takes 3 clicks to add the same duration filter to both events.

XEFilter

A target needs to be chosen next, and there are 3 clicks to point the session to the ring buffer.

XETarget

Once the target is set, it takes roughly 2 clicks to start the session.  There are options on the General page of the New Session dialog that allow for starting the session and watching live data as soon as the session is created.

XEStartSession

Results

Hopefully in a not-too-distant future post, I can compare what it’s like to watch the live results.  Profiler is very simple, but limited.  XEvents offer much more flexibilty when viewing the results of the session, but they aren’t nearly as straightforward.

Conclusion

This blog post is really about forcing myself to start using Extended Events.  If nothing else, this tedious counting of mouse clicks should prove that setting up an XEvent session is no more burdensome than getting a Profiler Trace running.

 

Advertisements

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