Microsoft® SQL Server® 2012 Bible

995

CHAPTER

44

Interpreting Query Execution

Plans

IN THIS CHAPTER

Learning the Language of Performance Tuning and Optimization

Viewing, Saving, and Working with Query Execution Plans

Reading Query Execution Operators

A

n execution plan is a set of instructions for how to process a SQL statement. Sounds easy,

right? It’s a relatively simple task to write a query to pull data from three different tables

using INNER JOIN statements and summarize the data using a GROUP BY and a SUM()

function. However, taking a query from its logical form, such as that SELECT statement, and devis-

ing a plan as to how to best execute that query is exponentially more complex. There are so many

factors to consider that the Query Optimizer won’t try to fi nd the “best” plan — a “good enough”

plan is often the best you can hope for.

This is where you come in. The SQL Server Query Optimizer is the best in the business at doing its

job, but it is not always perfect because it relies heavily on a lot of factors outside of its control —

statistics is one great example. Spotting these imperfections in plans is paramount to performance

tuning a SQL Server system.

The ability to interpret and adjust execution plans is one of the base skills that all great

performance-tuning artists possess. Yes, performance tuning is as much an art as it is a technical

skill. Sure, it is not the ONLY skill you need to become great at performance tuning SQL Server;

you need to understand the engine internals, how SQL Server uses memory and IO, along with

a score of other things, but being great at reading execution plans and knowing how to adjust

them is a great start.

c44.indd 995c44.indd 995 7/31/2012 10:19:26 AM7/31/2012 10:19:26 AM

http://www.it-ebooks.info