SQL Server Query Performance Tuning Distilled by Sajal Dam


SQL Server Query Performance Tuning Distilled

  • Author: Sajal Dam
  • Softcover: 624 pages
  • Publisher: APress(Oct 11 2004 12:00AM)
  • Book dimensions: 9.3 x 7.4 x 1.3 inches
  • ISBN: 1590594215
  • ITBookworm score: 5.0 /5

Score Reasoning

I don’t like to give 5s very often, but I just couldn’t help it this time. This book covers practically every aspect of query tuning, and gives lots of info for beginners and experienced admins alike.

Synopsis

Sure this book was written in 2004 for SQL2K, but it’s still very relevant to Yukon. So, don’t look past this just because it isn’t Yukon specific. This book covers SQL Server query tuning from all angles. There are really only two books on the topic that are useful enough to actually teach you what you need to know on the topic. This is one of them, and the other one is Ken England’s book. This book covers topics that no others do, or at least not to any degree. Topics like bookmark lookups, statistics, connection pooling, fragmentation analysis, cursor performance, and the like are all covered very thoroughly. This is just an excellent book and if you can only afford one book on query tuning, make it this one. I don’t believe in only one book for anything, so if you can afford more than one, then you should also get Ken England’s book to supplement this one, but for a very complete overall discussion on the topic, this book does a much better job than England’s. I think the discussion on blocking is thorough, but could stand to be explained a little better than it is, and for the most complete work on that, I suggest Kalen Delaney’s book. Specifics are discussed in the review of the blocking chapter. There are some minor things not mentioned like pad index and specifics of update locks, but they are relatively minor. The book really gives the most thorough discussion of query tuning I’ve seen for SQL Server. It is already part of my bookshelf. I only wish it came with a PDF so I could carry it around more easily.

Who is this book really for?

This book is for anyone, novice or experienced who wants to learn about query tuning. It has enough beginning discussion to be useful to the novice, and it has enough detail to be useful to experienced DBAs and developers. I would recommend this book to DBAs in charge of troubleshooting queries and system performance as well as developers writing large applications. This book is a must.

Writing Style

There’s not much in the way of cute, artsy explanations, or anecdotes. Sajal gets right to the heart of the matter. The thing is though he does it without talking down to his audience or boring you. I’ve gone through it a couple times now and I use it as a reference all the time and I always find exactly what I’m looking for with minimum effort. He explains things very clearly and gives lots of examples along the way. So I would have to say that this book is much like an epic movie… little action, but very interesting if you actually get involved in the plot.

Condition

APress just puts together sturdy books. I’ve abused this book on planes, backpacks, and under car seats. I’ve played catch with it and used it to prop up my coffee table. It’s just solid and I can’t find even the slightest hint of it falling apart. This book will be on my shelf for a very long time.

Table of Contents

Chapter 1: SQL Query Performance Tuning

Starts out by talking about the performance tuning process and performance vs. price. A very short section on baselining and where to focus efforts comes next and then common performance killers are discussed one at a time. It’s a short chapter with a high-level intro. Not bad, but not earth shattering.

Chapter 2: System Performance Analysis

Only the 2nd chapter and the book is already getting into some good performance tuning. So many books waste a lot of time, but there’s just no use for it. Anyway though… it starts out talking about perfmon and moves directly into identifying specific issues with the perfmon counters. It discusses specific counters, what they mean, what their values should be, and how to use them to troubleshoot performance. It also goes into a little memory management in SQL. It breaks the chapter down into sections of bottleneck issues like memory, disk, CPU, etc. It has a nice flowchart diagram for each section so you can tell which direction to take when deciding on a course of action. It also gives you different courses of action for fixing the bottlenecks. It goes into RAID configurations and how to calculate proper disk I/O. It also goes into disk placement of your SQL files and the RAID configs to put them on. Creating a baselines concludes the chapter. Not nearly enough books even mention baselines, so this is taking a very responsible position. This is a very good chapter that starts the book off well.

Chapter 3: SQL Query Performance Analysis

This one is all about Profiler. There’s lots of good info given especially for those who have never used it before. All of the classes are discussed, templates, etc. It also goes into using the trace SPs for server-side trace. It even goes into how to diagnose things like costly queries and look at query plans in the trace. It begins also the basic of graphic execution plan analysis, which is something that isn’t covered much in many other texts. ShowPlan_All vs. Statistics Profile is discussed as well as other SET options like Statistics IO. Overall, an excellent chapter that will really help beginners get up and running.

Chapter 4: Index Analysis

There’s some good stuff here. All topics of indexing are covered. It starts out with indexing basics, and moves into more advanced topics using execution plans to show examples along the way. It not only gives index recommendations, but also discusses overhead, which is a topic left out by most works. This is an excellent chapter that gies a full discussion on indexing that will leave a beginner with a solid understanding, and there’s even some stuff here for experienced admins. In all, very thorough.

Chapter 5: Index Tuning Wizard

A fairly small chapter dealing with how to use the tuning wizard. Personally, I don’t use it because I find it to be far too limited in helping me create indexes. However, since it’s part of the SQL indexing arsenal, then it’s responsible of the author to cover it. He gives it enough coverage to be thorough, but then leaves it at that. Good for him. Now, let’s move on.

Chapter 6: Bookmark Lookup Analysis

When I saw this chapter, I knew this book was going to be worth every dime. I’ve read almost every book out there on tuning SQL Server, and I’ve never seen an entire chapter dedicated to bookmarks, and it’s a sad, sad state. Understanding bookmarks is extremely important and I’ve interviewed plenty of very experienced DBAs who couldn’t tell me the first thing about bookmark lookups. So, if you want to really learn about analyzing execution plans, get this book, and study this chapter… you’ll know more than most DBAs out there. It talks about what causes them, and how to resolve them, and shows examples with execution plans all along the way. Good stuff.

Chapter 7: Statistics Analysis

What can I say about this chapter except that it’s a full discussion on stats analysis. It goes into sufficient depth on creating stats on indexed and non-index columns as well as maintenance, benefits, etc. It has plenty of execution plans showing cost with stats vs without stats, as well as showing what missing stats looks like in a plan. It discusses the formula for calculating density since you can’t talk about stats very long without discussing density. It shows trace flags for working with stats, and automatic and manual maintenance. Pretty much all aspects of stats are discussed. If you want to know what stats are, and how they’re used, and how you work with them, you can’t do much better than this chapter. You may need to go somewhere else if you want to know some of the internals behind stats, but reading this chapter alone will give you a lot of information that even experience admins could get something out of.

Chapter 8: Fragmentation Analysis

This is another topic that just doesn’t get enough ink in most texts. I guess people are supposed to be born knowing all the ins and outs of fragmentation because this is the first work I’ve seen that covers it to any real degree. The exception of course is SQL Server Magazine where Kalen Delaney’s column has discussed this topic on a number of occasions and it’s always excellent. So if you’re really looking to get into fragmentation analysis, start with this chapter, and then go read anything Kalen has written on the subject. But if you never go past this book, you will do much better than most of your peers on this topic. It talks about causes of fragmentation and the different types you’ll find. It discusses fragmentation overhead and working with DBCC ShowContig. Every piece of showcontig is discussed in good detail, and I’ve been doing this for a long time now, and I’ll admit that even I learned something. It takes you through some exercises on analyzing and resolution options. It discusses these options in very nice detail. Fill factor is given some good ink along with creating automated maintenance routines. I don’t remember seeing much on pad index though. It doesn’t show up in the index either, so I guess for some reason the author didn’t find it a note-worthy discussion. I find that interesting. All the same, it’s an excellent chapter.

Chapter 9: Execution Plan Cache Analysis

To find a book that discusses plan cache much at all is difficult, but to dedicate a chapter to it is just awesome. This is a pretty lengthy chapter so the topic is covered very well. OK, I suppose the length alone doesn’t mean it’s covered well, but still… it is. It starts out with plan generation and the steps taken for normal plans and trivial plans. It discusses each step in detail. It then goes into the different components of the execution plan. It then moves directly into analyzing plan cache. I like a book that doesn’t waste time. Many aspects are covered and I’m not going to bother going into them all here, but it does reuse, ad hoc, and many others. It even brings in some Profiler analysis here to help tie in the complete picture of how to use more than one tool for all the jobs you’ll want to accomplish. It then talks about recommendations for caching plans. It’s very sound advice and a lot of DBAs don’t know a lot of these things, so again, you can’t go wrong here. And again, there are too many topics covered to cover here. Again, an excellent chapter.

Chapter 10: Stored Procedure Recompilation

All aspects of recompilation are discussed. Again, I’m not gonna go into a lot of detail because it starts to sound like a broken record after a while, but you can count on hearing most of what you need to know in this chapter. The aspects discusses are benefits, analysis, identification, etc. It’s very thorough. I like it.

Chapter 11: Query Design Analysis

This is a significantly large chapter that discusses the topic of query design fairly well. It discusses most aspects of query design but I’m a little disappointed that the topic could be discussed so well and still have temp tables and table variables left completely out of the discussion as large resultset alternatives. Even as solutions themselves, they should be contrasted so you would know which one to use should you decide you needed one. However, most topics are covered quite well from selectivity, avoiding functions, different conditions, joins, where clauses, to DRI and the like. Like I said though… it’s thorough enough, but it could be more so by including a discussion on #tables and @tables.

Chapter 12: Blocking Analysis

OK, here you get into the basics of blocking and concurrency. There’s really nothing here that you can’t get anywhere else though. Don’t get me wrong… it’s a good chapter that discusses the topic thoroughly, but there’s just not much new here. Still, good stuff. One thing I will say though is that Kalen Delaney’s e-book on locking and blocking is much better as a start to finish text. She discusses all aspects and even explains some of the things much better. One thing I find wrong with this is that a novice looking for solid advice here will be mislead because in the section on recommendations for avoiding blocking, using update locks isn’t discussed at all, and this method has solved my problem dozens of times and is even part of the MS exams. I think it’s something that should be discussed as a viable solution, and Kalen’s book delves into this very nicely. Her book is called ‘Troubleshooting Locking and Blocking’. By all means, start with this chapter for the basics, and then move to her book if you want to go really deep into the topic. But this chapter will definitely get you started in the right direction and if you really study it, you can start thinking for yourself and take yourself pretty far. Still, a good chapter, but I can’t help comparing it to other works that are numerous.

Chapter 13: Deadlock Analysis

Deadlocks are a slightly different animal than blocks, but still, a lot of the basics were covered in the last chapter. However, it does cover analysis with Profiler, and trace flags. It then quickly gets into resolutions of many different kinds. In all, not bad. I know most of what the chapter had to offer, but if I had a deadlock I couldn’t resolve I would gladly start here to see if there were anything I forgot.

Chapter 14: Cursor Cost Analysis

Again, you just don’t see any works out there with such dedication to cursors and their performance. The different kinds of cursors are discussed here with their aspects given plenty of detail. I would even go so far as to say that most developers should read this to know the full impact of their cursors. There are far too many needless cursors out there, so I urge all developers to study this chapter.

Chapter 15: Database Connection Performance Issues

Now, when discussing query tuning or any other type of tuning, connection performance itself is so often overlooked. In fact, this is the only book I’ve ever seen that dedicates any more than just a perfunctory word count on the subject. That said, this chapter starts off with connection cost and pooling. ODBC connection pooling is discussed along with OLE DB pooling and configuration. It then discusses the effects of pooling on performance and even shows you how to monitor it in perfmon. It then moves into ADO/ADO.NET threading and how to select the right access library. Next it moves into network protocols and connection recommendations. A great chapter that covers a little-used topic that most DBAs and developers will get something out of.

Chapter 16: Database Workload Optimization

This chapter pretty much pulls all the other chapters together into a single troubleshooting scenario. It discusses how to analyze and troubleshoot log running queries etc. It’s ok, but nothing earth shattering. I find it a little redundant, but beginners and even intermediates will probably find it more useful than I did.

Chapter 17: Scalability Scenarios

Here you get into some more perfmon counters that go left out before, and how to get into moving large pieces of data and possibly even migrating to a larger server. It teaches you how to size a table for capacity planning. There are other topics covered as well. It’s ok… again though, it didn’t really teach me anything, but beginners will find it completes the discussion of the entire book.

Chapter 18: SQL Server Optimization Checklist

It’s a checklist that gives a brief discussion on each point. It’s good to both summarize the topics of the book, and to give you a single place to go to just make sure you’ve covered all your bases.
-Sean McCown, ITBookworm.com

Tags:

Leave a Reply

Your email address will not be published. Required fields are marked *

*