SQL Server 2008 Query Performance Tuning Distilled by Grant Fritchey and Sajal Dam approaches the task of maintaining and tuning an SQL Server in a methodical fashion, and I highly recommend it. The book might take a little while to get through, but it isn’t that it is a hard read, it is just that there is a lot of information. The book will best be read by a DBA who has at least some working knowledge of how to interact with the SQL Server (i.e. this might not be the best book for a true beginner).
The following is a quick overview of what the book has to offer:
Chapter 01 – SQL Query Performance Tuning
A general overview of the iterative performance tuning methodology. Also gives a description of the coming chapters and the information to which each will delve.
Chapter 02 – System Performance Analysis
In my mind, the best chapter in the book. This gives us a comprehensive guide for using the Perfmon tool as well as the system DMVs to understand how your system is performing.
Chapter 03 – SQL Query Performance Analysis
A quick look at profiler traces, execution plans and some of the statistics options.
Chapter 04 – Index Analysis
This chapter presents a fairly thorough look at SQL Server indexes, their benefits, their costs and how you can use them effectively.
Chapter 05 – Database Engine Tuning Advisor
A short chapter dedicated to using the Database Engine Tuning Advisor (DTA or DETA).
Chapter 06 – Bookmark Lookup Analysis
Another fairly short chapter discussing the Bookmark Lookup Operation, and ways in which they can be avoided.
Chapter 07 – Statistics Analysis
Discusses how SQL Server keeps track of statistics, how they are used, and the few options you have to keeping up with them.
Chapter 08 – Fragmentation Analysis
A look at index fragmentation, what causes it, some options of dealing with it and how you can maintain objects affected by it.
Chapter 09 – Execution Plan Cache Analysis
This chapter investigates the Execution Plan Cache, the costs associated with storing the plans in the cache, and some tips on how you can make the cache reusable. This chapter may have me rethinking how we currently execute the majority of our database loads.
Chapter 10 – Stored Procedure Recompilations
This chapter investigates the intentional and unintentional ways for recompiling stored procedures, the costs associated with these recompilations and tips on how to avoid the same.
Chapter 11 – Query Design Analysis
This chapter has a few general tips and tricks for working with query design.
Chapter 12 – Blocking Analysis
This chapter goes into the details for the SQL Engines locking mechanism, the isolation settings, the effects of each and how they can effect blocking within the database engine.
Chapter 13 – Deadlock Analysis
A short chapter on deadlocks, some quick tips for how you might avoid them, and information on how you can capture information that will enable you to triage the errors after they occur.
Chapter 14 – Cursor Cost Analysis
A chapter on the different types of cursors, the various pros and cons of each and some tips for defining them to be as efficient as possible. As always, lots of warnings on avoiding them entirely if at all possible.
Chapter 15 – Database Workload Optimization
A chapter on the iterative nature of capturing the overall workload of a server, analyzing it for problem queries, and then iteratively tuning them. This seems like the "Bringing it all together" type of chapter and does a good job of showing where each topic you have learned about will come in handy for helping to improve the overall health of the system.
Chapter 16 – SQL Server Optimization Checklist
This is almost like a summary chapter, in the guise of a quick checklist. It reviews the rest of the larger subjects which the book has covered as a reminder that you can quickly flip through.