Sybase Technical Library - Product Manuals Home
[Search Forms] [Previous Section with Hits] [Next Section with Hits] [Clear Search] Expand Search
Chapter 18: Tuning with dbcc traceon [Table of Contents] The Table Information Block

Performance and Tuning Guide

[-] Chapter 18: Tuning with dbcc traceon
[-] Tuning with dbcc traceon(302)

Tuning with dbcc traceon(302)

showplan tells you the final decisions that the optimizer makes about your queries. dbcc traceon(302) can often help you understand why the optimizer makes choices that seem incorrect. It can help you debug queries and decide whether to use certain options, like specifying an index or a join order for a particular query. It can also help you choose better indexes for your tables.

When you turn on dbcc traceon(302), you eavesdrop on the optimizer as it examines query clauses and applies statistics for tables, search arguments, and join columns.

The output from this trace facility is more detailed than showplan and statistics io output, but it provides information about why the optimizer made certain query plan decisions. The query cost statistics printed by dbcc traceon(302) can help to explain, for example, why a table scan is chosen rather than an indexed access, why index1 is chosen rather than index2, and so on.

dbcc traceon(310)

dbcc traceon(310) output can be extremely lengthy and is hard to understand without a thorough understanding of the optimizer. You often need to have your showplan output available as well to understand the join order, join type, and the join columns and indexes used. The most relevant parts of dbcc traceon(310) output, however, are the per-table total I/O estimates.

Invoking the dbcc Trace Facility

To start the dbcc traceon(302) trace facility, execute the following command from an isql batch, followed by the query or stored procedure that you want to examine:

dbcc traceon(3604, 302)

This is what the trace flags mean:

Trace Flag

Explanation

3604

Directs trace output to the client, rather than to the error log.

302

Prints trace information on index selection.

To turn off the output, use:

dbcc traceoff(3604, 302)

dbcc traceon(302) is often used in conjunction with dbcc traceon(310), which provides more detail on the optimizer's join order decisions and final cost estimates. dbcc traceon(310) also prints a "Final plan" block at the end of query optimization. To enable this trace option also, use:

dbcc traceon(3604, 302, 310)

To turn off the output, use:

dbcc traceoff(3604, 302, 310)

See "dbcc traceon(310) and Final Query Plan Costs" for information on dbcc traceon(310).

General Tips for Tuning with dbcc traceon(302)

To get helpful output from dbcc traceon(302), be sure that your tests cause the optimizer to make the same decisions that it would make while optimizing queries in your application. You must supply the same parameters and values to your stored procedures or where clauses. If the application uses cursors, use cursors in your tuning work. If you are using stored procedures, make sure that they are actually being optimized during the trial by executing them with recompile.

Checking for Join Columns and Search Arguments

In most cases, Adaptive Server uses only one index per table in a query. This means that the optimizer must often choose between indexes when there are multiple where clauses supporting both search arguments and join clauses. The optimizer first matches the search arguments to available indexes and statistics and estimates the number of rows and pages that qualify for each available index.

The most important item that you can verify using dbcc traceon(302) is that the optimizer is evaluating all possible where clauses included in the query. If a SARG clause is not included in the output, then the optimizer has determined it is not a valid search argument. If you believe your query should benefit from the optimizer evaluating this clause, find out why the clause was excluded, and correct it if possible.

Once all of the search arguments have been examined, each join combination is analyzed. If the optimizer is not choosing a join order that you expect, one of the first checks you should perform is to look for the sections of dbcc traceon(302) output that show join order costing: there should be two blocks of output for each join. If there is only one output for a given join, it means that the optimizer cannot consider using an index for the missing join order.

The most common reasons for "non-optimizable" clauses include:

See "Search Arguments and Useful Indexes" for more information on requirements for search arguments.

Determining How the Optimizer Estimates I/O Costs

Identifying how the optimizer estimates I/O often leads to the root of the problems and to solutions. You can to see when the optimizer uses actual statistics and when it uses default values for your search arguments.

Structure of dbcc traceon(302) Output

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:

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:

Figure 18-1 shows the ordering and basic structure of the search and join blocks in dbcc traceon(302) output.

Figure 18-1: Structure of blocks of output in dbcc traceon(302)
raster

Additional Blocks and Messages

Some queries generate additional blocks or messages in dbcc traceon(302) output, as follows:

Chapter 18: Tuning with dbcc traceon [Table of Contents] The Table Information Block