The final section for each SARG or join block for a table shows the best qualifying index for the clauses examined in the block.
When search arguments are being analyzed, the best access block looks like:
The best qualifying index is ’pub_id_ix’ (indid 5) costing 153 pages, with an estimate of 168 rows to be returned per scan of the table, using index prefetch (size 16K I/O) on leaf pages, in index cache ’default data cache’ (cacheid 0) with LRU replacement using no data prefetch (size 2K I/O), in data cache ’default data cache’ (cacheid 0) with LRU replacement Search argument selectivity is 0.033539.
If no useful index is found, the final block looks like:
The best qualifying access is a table scan, costing 621 pages, with an estimate of 1650 rows to be returned per scan of the table, using data prefetch (size 16K I/O), in data cache ’default data cache’ (cacheid 0) with LRU replacement Search argument selectivity is 0.330000.
For joins, there are two best access blocks when a merge join is considered during the join-costing phase, one for nested-loop join cost, and one for merge-join cost:
The best qualifying Nested Loop join index is ’au_city_ix’ (indid 4) costing 6 pages, with an estimate of 4 rows to be returned per scan of the table, using index prefetch (size 16K I/O) on leaf pages, in index cache ’default data cache’ (cacheid 0) with LRU replacement using no data prefetch (size 2K I/O), in data cache ’default data cache’ (cacheid 0) with LRU replacement Join selectivity is 0.000728. The best qualifying Merge join index is ’au_city_ix’ (indid 4) costing 6 pages, with an estimate of 4 rows to be returned per scan of the table, using no index prefetch (size 2K I/O) on leaf pages, in index cache ’default data cache’ (cacheid 0) with LRU replacement using no data prefetch (size 2K I/O), in data cache ’default data cache’ (cacheid 0) with LRU replacement Join selectivity is 0.000728.
Note that the output in this block estimates the number of “rows to be returned per scan of the table.” At this point in query optimization, the join order has not yet been chosen.
If this table is the outer table, the total cost of accessing the table is 6 pages, and it is estimated to return 4 rows.
If this query is an inner table of a nested-loop join, with a cost of 6 pages each time, each access is estimated to return 4 rows. The number of times the table will be scanned depends on the number of estimated qualifying rows for the other table in the join.
If no index qualifies as a possible merge-join index, dbcc traceon(302) prints:
If this access path is selected for merge join, it will be sorted
