Search clause identification

For search clauses, the clause block prints each of the search clauses that the optimizer can use. The list should be compared carefully to the clauses that are included in your query. If query clauses are not listed, it means that the optimizer did not evaluate them because it cannot use them.

For example, this set of clauses on the titles table:

where type = "business" 
    and title like "B%"
    and total_sales > 12 * 1000 

produces this list of optimizable search clauses, with the table names preceding the column names:

Selecting best index for the SEARCH CLAUSE:
        titles.title < ’C’
        titles.title >= ’B’
        titles.type = ’business’
        titles.total_sales > 12000

Notice that the like has been expanded into a range query, searching for >= ‘B’ and <‘C’. All of the clauses in the SQL statement are included in the dbcc traceon(302) output, and can be used to help optimize the query.

If search argument transitive closure and predicate factoring have added optimizable search arguments, these are included in this costing block too.

See “Search arguments and useful indexes” for more information.