Microsoft® SQL Server® 2012 Bible

1016

Part VII: Performance Tuning and Optimization

Path

No Path Desc Execution Plan Rows Cost Reads Missing Index

Duration

(ms)

Rows

per ms

Filter by

Include

Column

NC Ix Seek

(Seek

Predicate +

Predicate)

1.0032 51 .02

7 Filter by 2 x

NC Indexes

2 x NC Ix

Seek

(Predicate

Æ Merge

Join

1.012 4 63 .02

8 Filter by

Ordered NC

Composite

Index

NC Ix Seek

(Seek

Predicate w/

2 prefi xes)

1.0032 56 .02

9 Filter by

Unordered

NC

Composite

Index

NC Ix Scan 118 .209 173 NC by

missing

key,

include C

Key

72 1.64

10 Filter by

Expression

N C I x S c a n 9. 2 0 9 173 111. 0 8

The key performance indicators are the query execution plan optimizer costs (Cost), and

the number of logical reads (Reads).

For the duration column, each query path was executed multiple times with the results

averaged. You should run the script on your own SQL Server instance, take your own per-

formance measurements, and study the query execution plans.

The rows-per-ms column is calculated from the number of rows returned and the average

duration. Before executing each query path, the following code clears the buffers:

DBCC FREEPROCCACHE;

DBCC DROPCLEANBUFFERS;

Query Path 1 — Fetch All

The fi rst query path sets a baseline for performance by simply requesting all the data from

the base table.

SELECT *

FROM Production.WorkOrder;

TABLE 45 -1 (continued)

c45.indd 1016c45.indd 1016 7/31/2012 10:16:39 AM7/31/2012 10:16:39 AM

http://www.it-ebooks.info