SQL Server 2008 Query Performance Tuning Distilled by Grant Fritchey and Sajal Dam

By

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

Totally.

Chapter 8: Fragmentation Analysis

Totally totally.

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

Tags: ,

One Response to SQL Server 2008 Query Performance Tuning Distilled by Grant Fritchey and Sajal Dam

Leave a Reply

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

*