dbcc traceon(310) and final query plan costs

The end of each search clause and join clause block prints the best index for the search or join clauses in that particular block. If you are concerned only about the optimization of the search arguments, dbcc traceon(302) output has probably provided the information you need.

The choice of the best query plan also depends on the join order for the tables, which is the next step in query optimization after the index costing step completes. dbcc traceon(310) provides information about the join order selection step.

It starts by showing the number of tables considered at a time during a join. This message shows three-at-a-time optimization, with the default for set table count, and a 32-table join:

QUERY IS CONNECTED
Number of tables in join: 32
Number of tables considered at a time: 3
Table count setting: 0 (default value used)

dbcc traceon(310) prints the first plan that the optimizer considers, and then each cheaper plan, with the heading “NEW PLAN.”

To see all of the plans, use dbcc traceon(317). It prints each plan considered, with the heading “WORK PLAN.” This may produce an extremely large amount of output, especially for queries with many tables, many indexes, and numerous query clauses.

If you use dbcc traceon(317), also use dbcc traceon(3604) and direct the output to a file, rather than to the server’s error log to avoid filling up the error log device.

dbcc traceon(310) or (317) prints the join orders being considered as the optimizer analyzes each of the permutations. It uses the varno, representing the order of the tables in the from clause. For example, for the first permutation, it prints:

 0 - 1 - 2 -

This is followed by the cost of joining the tables in this order. The permutation order for subsequent join orders follows, with “NEW PLAN” and the analysis of each table for the plan appearing whenever a cheaper plan is found. Once all plans have been examined, the final plan is repeated, with the heading “FINAL PLAN”. This is the plan that Adaptive Server uses for the query.