Microsoft SQL Server 2012 Bible

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

An 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 devising 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 find 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 ...