Microsoft® SQL Server® 2012 Bible

1015

Chapter 45: Indexing Strategies

45

Performance data for each path, listed in Table 45-1, was captured by watching the

T-SQL ➪ SQL:StmtCompleted and Performance ➪ Showplan XML Statistics Profi le events in

Profi ler and examining the Query Execution Plan.

TABLE 45 -1 Query Path Performance

Path

No Path Desc Execution Plan Rows Cost Reads Missing Index

Duration

(ms)

Rows

per ms

1 Fetch All C Ix Scan 72,591 .485 526 1,196 60.71

2Clustered

Index Seek

C Ix Seek 1 .003 2 7 .14

3 Range Seek

Query

(narrow)

C Ix Seek

(Seek keys

start-end)

11. 0 0 3 3 13. 8 5

Range Seek

Query (wide)

C Ix Seek

(Seek keys

start-end)

72,591 .485 526 1,257 57.73

4 Filter by

Non-Key

Column

C Ix Scan Æ

fi lter

(predicate)

85 .519 526 NC

(include all

columns)

170 .32

5Bookmark

Lookup

(Select *)

NC Ix Seek

Æ BML

9.03729 226 .04

Bookmark

Lookup

(Select clus-

tered key,

non-key col)

NC Ix Seek

Æ BML

9 .037 29 128 .07

6Covering

Index

(narrow)

NC Ix Seek

(Seek

Predicate)

9.0032 30 .30

Covering

Index (wide)

NC Ix Seek

(Seek

Predicate)

1,105 .005 6 106 10.46

NC Seek

Selecting

Clustered

Key (narrow)

NC Ix Seek

(Seek

Predicate)

9.0032 46 .20

NC Seek

Selecting

Clustered

Key (wide)

NC Ix Seek

(Seek

Predicate)

1,105 .0 0 4 4 4 6 24.02

Continues

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

http://www.it-ebooks.info