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:
-
SQL Server 2008 Query Performance Tuning Distilled by Grant Fritchey (Twitter, blog) and Sajal Dam
-
$34.64 on Amazon right now, $37.11 on B&N for members
-
Published March 09 by APress, 600 pages
I love a book with context and insight, and this is such a book.
Chapters, with a few notes:
-
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.
-
System Performing Analysis – Using Performance Monitor to get a baseline. Covers resource bottlenecks and DMVs.
-
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.
-
-
Index Analysis – Basics of indexes, the benefits and costs, recommendations, and advanced techniques. This, too, should be required reading.
-
Database Engine Tuning Advisor – Basics, how to use, and limitations.
-
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.
-
Statistics Analysis – Totally.
-
Fragmentation Analysis – Totally totally.
-
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.
-
Stored Procedure Recompilation – It's not always a bad thing. FREEPROCCACHE!!
-
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.
-
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.
-
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.
-
Cursor Cost Analysis – General discussion, benefits and drawbacks to different types of cursors, analyzing overhead, and recommendations.
-
Database Workload Optimization – This is a good comprehensive chapter that touches on defragmentation and other specifics, within the context of a workload.
-
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.MidnightDBA.com