X hits on this document

125 views

0 shares

0 downloads

0 comments

13 / 48

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

, ModifiedDate

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

Name

Owner

Object_type

vAdditionalContactInfo

dbo

view

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:

EXEC sp_help

This returns two result sets (abridged):

view view view

...

vEmployee vEmployeeDepartment vEmployeeDepartmentHistory ...

dbo dbo dbo

_CI_AS

_

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

647

Document info
Document views125
Page views125
Page last viewedSat Dec 10 11:06:42 UTC 2016
Pages48
Paragraphs987
Words15822

Comments