CHAPTER 28 ■ PERFORMANCE TUNING
In addition to the estimated plan, you can also display the actual execution plan. This means the query itself is executed, returns results, and returns the execution plan as a separate tab. For example, to do this, select Query and Include Actual Execution Plan.
This time you’ll change the query to include a defined range of customers, ordering the results
by the ContactID: SELECT CustomerID, ContactID, Demographics
FROM Sales.Individual WHERE CustomerID BETWEEN 11000 AND 11020 ORDER BY ContactID
You then execute the query as normal in order to return the results, and the actual execution plan. After executing the query, three tabs are spawned: one for the results, one for any messages, and one containing the execution plan. Click the Execution plan tab to see the results (see Figure 28-9).
Figure 28-9. Actual execution plan graphical output
This time you’ll see three operations in the execution plan output. With the graphical execution plan output, you read the icons from right-to-left, and top-to-bottom. You’ll see that with the addition of the WHERE clause’s CustomerID search condition in the query, the data retrieval operation changed from a Clustered Index Scan to a Clustered Index Seek operation. A scan retrieves all rows from a heap or index. A seek operation is more selective, retrieving specific rows from an index. Also, notice that the cost of this operation was 32%. The remaining cost percentage of the query was due to the Sort operator (at 68% cost), which appeared in the execution plan results because of the addition of our query’s ORDER BY clause.
If you have multiple queries in a batch, for example when executing a stored procedure, you can use the graphical output to pinpoint which batch of the group has the highest cost %. For exam- ple, in this scenario you are going to execute the sp_help stored procedure with the Include Actual Execution Plan still enabled:
This returns two result sets (abridged):
view view view
vEmployee vEmployeeDepartment vEmployeeDepartmentHistory ...
dbo dbo dbo
U s e r _ t y p e S t o r a g e _ t y p e L e n g t h P r e c S c a l e N u l l a b l e D e f a u l t _ n a m e R u l e _ n a m e C o l l a t i AccountNumber nvarchar o n SQL_Latin1 General CP1 NULL 30 15 yes none none