10 Minute Book Review of SQL Server 2008 Query Performance Tuning Distilled
- Author: Grant Fritchey (Twitter, blog) and Sajal Dam
- 600 pages
- Publisher: Apress (March 2009)
- Buy on Amazon: Paperback, Kindle
- ITBookworm Score: 5/5
I’ve been meaning to review this book forever, because it’s my go-to book for all things performance tuning. Let’s just take a quick runthrough, starting with the basic info:
I love a book with context and insight, and this is such a book.
Table of Contents
Chapter 1: SQL Query Performance Tuning
Covers the performance tuning process, and explains everything in context. When I read this short chapter, I feel like I’m talking to a mentor who is explaining the reasons and common sense of tuning: how to examine performance conceptually, where to focus your efforts, and what you should look for – performance killers – right off the bat.
Chapter 2: System Performing Analysis
Using Performance Monitor to get a baseline. Covers resource bottlenecks and DMVs.
Chapter 3: SQL Query Performance Analysis
Using SQL Profiler, analyzing costly queries. Covers DMVs, index and join strategies, execution plans. This chapter should be REQUIRE READING for every SQL DBA and DB developer.
Check out page 70 on for one of my favorite topics, server side traces (under the heading “Trace Automation”).
Page 83 begins the section on execution plans. This section (in the 05 version of the book) finally made me understand the difference between merge joins, nested loop joins, and hash joins in an execution plan.
Chapter 4: Index Analysis
Basics of indexes, the benefits and costs, recommendations, and advanced techniques. This, too, should be required reading.
Chapter 5: Database Engine Tuning Advisor
Basics, how to use, and limitations.
Chapter 6: Bookmark Lookup Analysis
I like that bookmarks get their own chapter. Talks about what they are, why they’re bad, and what to do about them. I’m trying not to say “required reading” with every chapter, so we’ll hop over a few…just know that you should totally read them.
Chapter 7: Statistics Analysis
Chapter 8: Fragmentation Analysis
Chapter 9: Execution Plan Cache Analysis
Do I even need to say it? By the way, this chapter has one of Sean’s favorite topics, parameter sniffing.
Chapter 10: Stored Procedure Recompilation
It’s not always a bad thing. FREEPROCCACHE!!
Chapter 11: Query Design Analysis
Chapter 11 is the one my book just falls open to, because I’m in there all the time. You need good fast queries to get at your data…design well, code once.
Chapter 12: Blocking Analysis
Fundamentals, database locks, isolation levels…this chapter also cleared up a lot for me after I’d been stuck for years on some concepts.
Chapter 13: Deadlock Analysis
I can’t help it, I have to say it again: REQUIRED READING. Deadlocks are widely misunderstood, or not understood at all. Chapter 13 lays it all out: fundamentals, catching, analyzing, and resolving.
Chapter 14: Cursor Cost Analysis
General discussion, benefits and drawbacks to different types of cursors, analyzing overhead, and recommendations.
Chapter 15: Database Workload Optimization
This is a good comprehensive chapter that touches on defragmentation and other specifics, within the context of a workload.
Chapter 16: SQL Server Optimization Checklist
I open to this chapter at the start of every new major performance tuning effort. Every one.
Go, read, learn, enjoy.
-Jen McCown, http://www.ITBookworm.com