Book Review: SQL Server 2008 Query Performance Tuning Distilled

I recently finished reading the Apress book SQL Server 2008 Performance Tuning Distilled by Grant Fritchey and Sajal Dam.  I consider Grant to be a personal friend in addition to his being one of the top SQL Server experts.  I have not had the opportunity to meet Grant’s co-author, Sajal Dam, although the fact that he worked with Grant on this book makes me place him in high regard.  Unlike the other 2 book reviews (Murach’s JavaScript and DOM Scripting and Apress Pro SQL Server 2008 Administration I have done, this is a book I purchased. 

Overall Impression

This was a fantastic book.  I agree with what Brent Ozar (@BrentO) said in his review:

My gauge of an amazing book is simple: if I’ve got a question, and I reach for the book BEFORE I search the web, then it’s an amazing book.
Several times in the last two weeks, I reached for this book first.

I have also reached for this book several times since I purchased it, when I was baselining servers or looking for ways to improve performance.

Chapter Details

Chapter 1 – SQL Query Performance Tuning

A great introduction to the process of performance tuning and common performance killers.  I especially like the way the authors present the concept of “Good Enough” Tuning, where the performance may not have reached the theoretical optimum, but is “good enough” for your purposes.   It sets up the rest of the book very well.

Chapter 2 – System Performance Analysis

The authors present some of the tools you can use to measure the performance of your system including Performance Monitor and Dynamic Management Views.  They present a good list of performance monitor counters to watch and the general thresholds that indicate a potential bottleneck.  Not only are the thresholds presented, but potential resolutions as well.  I refer back to this chapter often.  They also present the best outline for creating a baseline that I have read. 

Chapter 3 – SQL Query Performance Analysis

From system analysis now to query analysis.  This chapter covers Profiler, or as Grant prefers to call it, SQL Trace, analyzing execution plans, and query cost (client statistics, execution time, statistics IO).  The authors present steps to use each of these tools to improve individual query performance.

Chapter 4 – Index Analysis

The authors do a great job of starting with an explanation of indexes to advanced indexing techniques including filtered indexes and special indexes on full-text, spatial and xml data types.  Experienced DBA’s will recognize the techniques described for designing and choosing indexes and for beginners it is like having an expert along side them.

Chapter 5 – Database Engine Tuning Advisor

A short look at the tool provided by Microsoft to analyze a workload and present index recommendations.  A good tool, but one to be used carefully.

Chapter 6 – Bookmark Lookup Analysis

A look at, what is in my opinion, a very common cause of poor performance, bookmark (key/RID) lookups.  The authors define and then show how to troubleshoot and resolve bookmark lookups.  A must read chapter.

Chapter 7 – Statistics Analysis

One of the longer chapters in the book and rightly so as Statistics are a poorly understood subject and, in some cases, hard to understand and analyze.  This had been one of my weaker areas in SQL Server and this chapter certainly helped me become more comfortable with it.  I know that there is at least one query I have used an index hint on that, were I to go back, I would probably remove because updating statistics would cause it to use that index.

Chapter 8 – Fragmentation Analysis

Great coverage of what  causes fragmentation and how to remove it.  One of the best explanations of Fill Factor and it’s significance

Chapter 9 – Execution Plan Cache Analysis

Another of the longer chapters as this is an important skill/art to understand.  The authors explain how all the steps involved in creation and re-use from generation to aging out.  Then they explain how to optimize queries for caching.  Again this is a learned skill, almost more of an art.

Chapter 10 – Stored Procedure Recompilation

A good companion to chapter 9 as execution plan caching and stored procedure recompilation are related.  The authors do a good job showing how to find what causes recompilation and then how to avoid it.

Chapter 11 – Query Design Analysis

The authors go into the art of designing queries optimally.  One of the key section talks about how to use indexes effectively.   Another section also discusses things that are part of good database design (Domain and Referential Integrity) help with query performance.  These are often neglected because they are “enforced” by the application, so they need to be emphasized.

Chapter 12 – Blocking Analysis

Blocking is, in my opinion, one of the most misunderstood aspects of the relational database as it is a core component to guarantee the ACID properties, yet it is often confused with Deadlocking (covered in chapter 13).  The authors do an excellent job covering all areas of locking, ways to capture blocking data, and ways to reduce it through indexes, isolation levels, and partitioning.

Chapter 13 -  Deadlock Analysis

Another example of good organization as it follows the Blocking Analysis chapter.  The authors do a good job providing an explanation and example of deadlocking.  I especially liked the section on Using Error Handling to Catch a Deadlock, so you can handle the deadlock in your T-SQL.  The authors also give the best ways to avoid deadlocks and the caveats with each.

Chapter 14 – Cursor Cost Analysis

A great chapter on the cost and benefits of using cursors.  Since SQL Server is designed to work with sets, using cursors is not usually the best solution, but the authors do a good job explaining how cursors work and how to write the best performing cursors in the cases you may need to use them.

Chapter 15 – Database Workload Optimization

The authors take you through the process of applying the information presented throughout the book.

Chapter 16 – SQL Server Optimization Checklist

The authors present a short summary of each of the optimization techniques presented in the book.  Once you’ve read the book you can just refer back to the final chapter to get guidance and, if needed, go deeper into the area you are using by referring back to an earlier chapter.

Conclusion

A really great book to learn how to make your SQL Server perform better.  It has information for every level of SQL Server professional from beginner to expert.  I particularly liked the summary chapters (15, 16) at the end of the book.  This is a book I think every SQL Server professional should have on their bookshelf.