Using SQL Server Profiler, you can measure CPU usage, logical reads, writes, duration, and frequency counts to find T-SQL code that is either taking too long or taking too many resources.  Once you’ve isolated such code, where do you go from there?  Without an in-depth knowledge of the estimated and actual execution plans, any tuning attempts are nothing more than guesses.  “Dissecting SQL Server Execution Plans” should be required reading for every DBA, SQL Developer, and GUI Developer that has to write some SQL because it teaches you, as the name implies, how to dissect SQL Server execution plans to find performance problems in code and what to do about them.  I’ve not seen a more complete collection of information related to the correct interpretation of execution plans to date.

Most books of such an advanced technical nature are intimidating to beginners.  Those that cater to beginners are usually boring to seasoned veterans.  Not so with this book.  Chapter 1 starts out with an introduction to execution plans and how the “optimizer” works to compile and execute SQL that is both easy to understand and captivating from an advanced technical point of view.  The author skillfully and effectively continues that same wonderful theme throughout the book… beginners will not be intimidated and seasoned veterans will not be easily bored.

Although the title and purpose of this book revolves around execution plans for SQL Server, it’s much more than that.  Sure, it covers everything from what each common symbol means, what the row counts of the connecting arrows mean, and how to read and interpret the “Tool Tip” pop-ups, but it also covers important tuning aspects, as well.  It covers commands such as SET STATISTICS IO and how to interpret its output, how to setup SQL Profiler to automatically capture execution plans as XML for later analysis as a graphical tool, and it explains how the 3 types of joins work, why the optimizer may choose them, and how to override the optimizer should it make a mistake.  It covers other operations such as how clustered indexes work vs. how non-clustered indexes work, demonstrates why cursors are slow and how to optimize them when nothing else will do, how to find the best of the XML commands to use, and even how to identify when parallelism is actually slowing code down rather than helping.  Ever wonder why a particular index isn’t being used or what to do about it?  This book will help you figure all of that out.

Contributing to the idea that one picture is worth a thousand words, “Dissecting SQL Server Execution Plans” is liberally and thoughtfully sprinkled with code examples and pertinent graphics every step of the way.  The code examples are simple enough for beginners to easily understand and complex enough to more than adequately demonstrate whatever point is being covered.  The graphics are perfectly timed to the text and each part of every graphic is thoroughly yet simply explained leaving absolutely no doubt as to what is going on.

It doesn’t end there, either.  There are a surprising number of seemingly unrelated but highly appropriate, incredibly useful tips throughout the book.  For example, do you know how to simulate a multi-processor system on a single CPU system so you can analyze the impact of parallelism without actually having to do the analysis on a multi-processor box?  Do you know how to share a “working” (including the “Tool Tip” pop-ups) graphical execution plan so that others can help you with your analysis?  Do you know how to quickly navigate very large execution plans?  Do you know why the row count of the output of a symbol might exceed the row count of the table it’s coming from and what the impact of that may be?

I’m totally amazed at the breadth of the information covered in this fine book by an incredible author and my review of it cannot do it full justice.  If you don’t have “Dissecting SQL Server Execution Plans” in your essential SQL programming library, then your library is simply not complete.

Title:             SQL Server Execution Plans

Author:        Grant Fritchey

Publisher:   SimpleTalk Publishing

ISBN:           978-1-906434-01-4

Copyright:   2008

Available from Amazon in print or from SQLServerCentral as an ebook.