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.
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:
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:
Next we filter on duration, which will take 5 clicks – not including the typing of the actual number.
I like to include a handful of additional columns such as database name, host name, error and others. This adds another 8 clicks.
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
After typing in the name, it takes 3 clicks to filter down to the 2 events we want to capture. Events –> Search on “completed”
Once we have the 2 events, we can select both of them and choose the fields we want in 12 clicks.
It then takes 3 clicks to add the same duration filter to both events.
A target needs to be chosen next, and there are 3 clicks to point the session to the ring buffer.
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.
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.
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.