It is one thing to look at the execution plan for SQL Server queries, it is quite another to know what they mean. Grant Fritchey’s book, The Art of High Performance SQL Code: Part 1 Dissecting SQL Server Execution Plans, helps you get to that point.
The book starts with an overview of execution plans. While most books seem to fill their first chapter with useless fluff, this one jumps right into the useful information like when execution plans are recompiled or why some queries have two execution plans.
Chapter 2 is where the real gold is. In it you will find the difference between a hash map join and a nested loop join and which is better. There are few absolutes in this section; often you will hear that a certain operation is great under large tables but horrible for small ones or vice-versa. There is also plenty of advice on why certain operations may indicate problems with the query or table design.
Chapter 3 is about reading text and XML-based execution plans. Unless you like staring at pages upon pages of XML, you can probably skip this chapter. It is followed by another chapter on interpreting execution plans, this time addressing more complex ones like those using Common Table Expressions.
Query hints can cause quite a bit of damage when used improperly, but they can also solve major performance problems caused by bugs in the optimizer. So with reservation I recommend everyone read Fritchey’s chapter on query hints. While most developers will never use them, knowing they exist during those rare times they truly are needed.
The rest of the book covers advanced topics such as cursors, XML in queries, parallelism, and plan guides. Plan guides are a particularly nifty thing. They allow you to supply query hints to stored procs that you can change as well as ad-hock queries created by applications.
The Art of High Performance SQL Code can be downloaded for free or purchased for Kindle at SQL Server Central. Red Gate has also been known to give away printed editions at conferences.