CHAPTER 28 ■ PERFORMANCE TUNING
Figure 28-7. SQL Server Management Studio
Next, I’m going to display the estimated query execution plan. This returns what operations SQL Server thinks it will perform without actually executing the query. To do this in SQL Server Management Studio, select Query and Display Estimated Execution Plan. This returns the query plan in the lower pane beneath the query (see Figure 28-8). The execution plan is represented by a tree that contains one or more operators.
Figure 28-8. Estimated Execution Plan graphical output
The icons in Figure 28-8 represent each operator that SQL Server performs in order to produce the result set. An operator is an action SQL Server takes to execute a query. There are two types of operators: logical and physical. Logical operators describe an operation in conceptual terms, while physical operators are used to perform the actual implementation of the logical operator. For exam- ple, a logical operator may be INNER JOIN, but the physical method to perform an INNER JOIN is a Hash Match (one of the physical methods for joining two result sets).
Note For a complete list of all available operators and their descriptions, see the SQL Server 2005 Books Online
topic “Graphical Execution Plan Icons (SQL Server Management Studio).”
One of the operations returned in this example’s execution plan is the Clustered Index Scan. This tells us that the entire clustered index was read in order to return the result set, which makes sense because the query doesn’t use a WHERE clause to filter the result set.
The execution plan includes two different Cost % areas. The first Cost % can be found in the header of each query, where you see “Query 1: Query cost (relative to the batch).” This tells you what percentage of the query’s duration was due to the specific query in the batch of queries. You’ll also see percentage of cost underneath each of the icons in the execution plan results. In this example, the Clustered Index Scan accounted for 100% of the query’s cost.