Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Dissecting SQL Server Execution Plans

By Jeff Moden,

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.

Total article views: 4731 | Views in the last 30 days: 9
 
Related Articles
ARTICLE

Understanding Graphical Execution Plans - Part 2: How to Create One

Learn how to create a SQL Server execution plan. This article explains the basics used in performanc...

ARTICLE

Understanding Graphical Execution Plans - Part 1: Explaining the Plan

Understanding execution plans is a good basic skill for all DBA's and SQL developers. Darren White g...

ARTICLE

Understanding Graphical Execution Plans - Part 3: Analyzing the Plan

Learn how to examine and read a SQL Server execution plan in this article. This is the third part of...

ARTICLE

Implementing Execution Log Reports on 32-bit SQL Server 2008 R2

This article covers how to implement the SQL Server 2008 Execution Log DB and package on SQL Server ...

SCRIPT

SQL Job Timeline (Graphical)

This script generates a graphical timeline of all SQL jobs with a variable zoom level

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones