dbcc traceon(302) prints its output as the optimizer examines the clauses for each table involved in a query. The optimizer first examines all search clauses and determines the cost for each possible access method for the search clauses for each table in the query. It then examines each join clause and the cost of available indexes for the joins.
dbcc traceon(302) output prints each search and join analysis as a block of output, delimited with a line of asterisks.
The search and join blocks each contain smaller blocks of information:
A table information block, giving basic information on the table
A block that shows the cost of a table scan
A block that displays the clauses being analyzed
A block for each index analyzed
A block that shows the best index for the clauses in this section
For joins, each join order is represented by a separate block. For example, for these joins on titles, titleauthor, and authors:
where titles.title_id = titleauthor.title_id
and authors.au_id = titleauthor.au_id
there is a block for each join, as follows:
titles, titleauthor
titleauthor, titles
titleauthor, authors
authors, titleauthor
