Range queries using covering indexes perform very well because:
The index is used to position the search at the first qualifying row on the index leaf level.
Each index page contains more rows than corresponding data rows, so fewer pages need to be read.
Index pages tend to remain in cache longer than data pages, so fewer physical I/Os are needed.
If the cache used by the index is configured for large I/O, up to 8 leaf-level pages can be read per I/O.
The data pages do not have to be accessed.
Both nonclustered indexes and clustered indexes on data-only-locked tables have a leaf level above the data level, so they can provide index covering.
The cost of using a covering index is determined by:
The number of non-leaf index levels
The number of rows that the query returns
The number of rows per page on the leaf level of the index
The number of leaf pages read per I/O
The index page cluster ratio, used to adjust large I/O estimates when the index pages are not stored consecutively on the extents
This formula shows the costs:
Leaf pages = Number of qualified rows / Leaf level rows per pageCovered scan cost = Number of index levels * 18 + (Leaf pages /Pages per IO) * Cluster adjustment * 18 + Number of index levels * 2 + Leaf pages * 2
For example, if a query needs to read 1,200 leaf pages, and there are 40 rows per leaf-level page, the query needs to read 30 leaf-level pages. If large I/O can be used, this requires 4 I/Os. If inserts have caused page splits on the index leaf-level, the cluster adjustment increases the estimated number of large I/Os.
