THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

Book Review: Tribal SQL,Performance Tuning With SQL Trace and Extended Events

Tara Kizer wrote this chapter, and it is relevant for us developers.

Tara briefly explains how to use the GUI, the Profiler, and states that it can heavily impact the performance on the server. Then she explains how to set up a server-side trace which should incur less overhead, and I noticed that the working example which she provided is quite similar to the one our team is using.

There is one minor thing, however, that we do differently: our T-SQL is self-documenting. For example, instead of the following:

EXEC sp_trace_setevent @traceid , 10, 1, @on

We use this:

EXEC sp_trace_setevent @traceid =@traceid
@eventid=@RpcCompleted, @columnid=@Cpu, @on=@on;

After describing how to set up a trace, Tara demonstrates several real life ways to use its output, such as finding missing indexes, stale statistics, and parameter sniffing causing problems.

 I have found this section of the chapter practical and useful for developers. We need to be proficient with this tool, we need to practice using it, so that we can quickly use it whenever the need arises. This chapter provides a good, short and useful, practical introduction to becoming proficient. I would recommend developers to read it.

Later Tara proceeds to describe Extended Events. I am not qualified to review that, since I do not have enough real life experience with them.

This completes the chapter review, which is followed by my personal opinion on deprecating of the Profiler.

I am disappointed that that the familiar and convenient interface of the Profiler and server side traces is going to disappear, rendering all the experience using it useless, and forcing lots of people to spend precious time to master the next thing that replaces it.

I understand that the old implementation might have to go, but I also think that it might be feasible to have the old interface invoke the new implementation. 

In my narrow experience, even if some application has just ten or twenty users, it may be cheaper to keep the old interface unchanged, even when we completely replace the implementation, such as migrating from SQL Server to PostgreSql. As a result our users do not have to relearn how to do the same thing with the new tool, and can do something more useful instead. The math is simple: a day of developer's time spent on backward compatibility is cheaper than two hours of user's time spent on relearning multiplied by the number of users.

Because the Profiler and server side traces have a huge number of customers, I think that SQL Server would be a more useful product if it kept providing the old interface even if the old implementation needs to be replaced with a new one. 

The time and resources spent on keeping the old interface would probably be just a tiny fraction of the time and resources spent by the huge amount of users relearning how to do the same thing with the new tool.
Published Tuesday, February 18, 2014 9:51 AM by Alexander Kuznetsov
Filed under: ,



Uri Dimant said:

>>>I am disappointed that that the familiar and convenient interface of the >>>Profiler and server side traces is going to disappear, rendering all the >>>experience using it useless, and forcing lots of people to spend precious >>>time to master the next thing that replaces it.

Oh Sasha , how the above statement is true :-(

February 19, 2014 1:44 AM

Jonathan Kehayias said:

Hey Alex,

I think that once you get into Extended Events in SQL Server 2012 or higher, you'll begin to see why maintaining the old UI just wasn't feasible.  Specifically, Trace has a fixed number of columns it outputs, whereas Extended Events does not, so the existing UI layout, specifically for event selection just wouldn't work.  In 2012 alone there are 1174 possible output data elements across all of the events, available actions, and configurable options for an Event Session. There is no way that would work in the UI layout that Profiler used for Trace. Once you use the 2012 UI, or see a demonstration of how to use it, it really makes a lot of sense and isn't a big learning curve for someone.

February 19, 2014 9:01 AM

Alexander Kuznetsov said:

Hi Jonathan,

I did some learning. I attended a presentation by Erin Stellato. I also read some of your contributions on the topic. This was a really interesting read - that you for writing all these posts!

Still, let us agree to disagree, As an accidental user, I have little practical need to "get into Extended Events in SQL Server 2012 or higher" any more than necessary to solve my problems - I have lots of other things to do.

I do not see why one tool/GUI should have to support all 1174 possible output data elements. I am sure I would be much more productive with several tightly focused smaller tools dealing with subsets of all this complexity - it is unlikely I would ever need 1174 possible output data elements all at once.

For typical simple troubleshooting I am more likely to need 10 columns than 1174. Most of my usage is totally trivial: I just select a template and have it up and running. I typically need a small and simple tool for one specific task.

I am well aware of the many advanced capabilities of the new thing, but my practical needs are quite pedestrian, and as such I am not sure I am more productive with a new more complicated tool with more features.

This reminds me of a great article by Joel Spolski, entitled "Fire and Motion":

February 19, 2014 3:41 PM
New Comments to this post are disabled

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on and Currently he works as an agile developer.

This Blog


Privacy Statement