Trace Flag 302

Trace flag 302 prints the optimizer’s index costing and selection phase. It has been redesigned to provide information on the new statistics and costing in an easy-to-read format.

This trace flag is well-documented in New Functionality in 11.9.2 and the sections on query tuning in the Performance and Tuning Guide.

Syntax for Trace Flag 302

When using trace flag (302) to see optimizer information, use trace flag 3604 to send output to the client. (You can use trace flag 3605 to send output to the errorlog, but this is not recommended.) The syntax looks like this:

dbcc traceon (3604,302)

Trace flag 302 output has been divided into the following “blocks” of related information:

The Table Information Block
The Base Cost Block
The Clause Block
The Column Block
The Index Selection Block
The Best Access Block

The following is an example of trace flag 302 output:

*******************************
Beginning selection of qualifying indexes for table ’lineitem’,
varno = 0, objectid 240003886.
The table (Datarows) has 600572 rows, 44308 pages,
The table’s Data Page Cluster Ratio 0.999990Table scan cost is 600572 rows, 44489 pages,
using no data prefetch (size 2K I/O),
in data cache ’default data cache’ (cacheid 0) with MRU replacementSelecting best index for the SEARCH CLAUSE:
lineitem.l_partkey = 2000
lineitem.l_orderkey > 10000Estimated selectivity for l_orderkey,
selectivity = 0.983249, upper limit = 1.000000.Estimated selectivity for l_partkey,
selectivity = 0.000052, upper limit = 0.052686.
Estimating selectivity of index ’lineitem_lorder’, indid 2
scan selectivity 0.983249, filter selectivity 0.983249
590512 rows, 45991 pages, index height 2,
Data Row Cluster Ratio 1.000000,
Index Page Cluster Ratio 0.998609,
Data Page Cluster Ratio 1.000000The best qualifying index is ’lineitem_pskey’ (indid 4) costing 35 pages, with an estimate of 31 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

Search argument selectivity is 0.000051.
*******************************

See the Performance and Tuning Guide for information on reading trace flag 302 output.