Reactive Index Design - Relational Database Index Design and the Optimizers - page 128

SHOWPLAN
-----------------------------------------------------
|--Sort(ORDER BY([Cust].[Fname]ASC))
|--Clustered Index
Scan(OBJECT:([Invoices].[dbo].[Cust].[Cust PK]),
WHERE:( [Cust].[LNAME] = 'Adams'
AND [CUST].[CITY]='BigCity').
This SELECT is certainly not a victim (ET/PR = 34 s/50 , 002 = 0 . 7ms,
which is much less than 10 ms); it appears to be a promising culprit (PR/F =
50 , 002/1000 = 50, which is considerably greater than 5).
The size of the CUST table is 400 MB (about 50,000 8K pages). The speed
of the sequential read would therefore be 12 MB/s (400 MB in 34 s). The small
server had only one slow disk drive; no striping.
With a fat index, the worst input performance improvement was almost as
expected: Physical reads 3
Read-ahead reads 175
Logical reads 176
Elapsed time 244 (ms)—down from 34 s
CPU time 40 (ms)—down from 1.9 s
This SELECT would still be reported as a slow one (ET > 200 ms), and it is
certainly not a victim (ET/PR = 244 ms/176 = 1 . 4ms,whichismuchlessthan
10 ms), but neither is it a promising culprit (PR/F = 176/1000 = 0 . 2, which is
much less than 5).
The worst input QUBE for the fat index is the same as in the Oracle example:
1 × 10 ms + 1000 × 0 . 01 ms + 1000 × 0 . 1ms = 0 . 1s:measured0.2s
Note: All the measurements above were made with a cold system (no pages
in the database pool or the disk cache). Because of the warm-up effects, the
service times, both CPU and I/O, are longer than they would be in a normal
production environment. On the other hand, there would be more contention,
queuing time, in a production environment.
It might be useful to see how an access path using a nonfat index is reported
by SQL Server:
SHOWPLAN
-------------------------------
|--Filter(WHERE:( [CUST].[CITY]= 'Sunderland'))
|--Bookmark Lookup(BOOKMARK:([Bmk1000]),
OBJECT([Invoices].[dbo].[Cust]))
|--Index
Seek(OBJECT([Invoices].[dbo].[Cust X1]),
SEEK: ([Cust].[Lname]='Lname265')
ORDERED FORWARD)
The SELECT and the index used are the same as we had before the index
improvement, although the input corresponds to average filter factors. The table
Cust is stored in the clustered index (CNO).