Adaptive Server provides the following diagnostic and informational tools to help you understand query optimization and improve the performance of your queries:
A choice of tools to check or estimate the size of tables and indexes. These tools are described in Chapter 16, “Determining Sizes of Tables and Indexes.”
set statistics io on displays the number of logical and physical reads and writes required for each table in a query. If resource limits are enabled, it also displays the total actual I/O cost. set statistics io is described in Chapter 35, “Using the set statistics Commands.”
set showplan on displays the steps performed for each query in a batch. It is often used with set noexec on, especially for queries that return large numbers of rows.
set statistics subquerycache on displays the number of cache hits and misses and the number of rows in the cache for each subquery.
See “Subquery results caching” for examples.
set statistics time on displays the time it takes to parse and compile each command.
See “Checking compile and execute time” for more information.
dbcc traceon (302) and dbcc traceon(310) provide additional information about why particular plans were chosen and is often used when the optimizer chooses a plan that seems incorrect.
The optdiag utility command displays statistics for tables, indexes, and columns.
See Chapter 37, “Statistics Tables and Displaying Statistics with optdiag.”
Chapter 20, “Advanced Optimizing Tools,” explains tools you can use to enforce index choice, join order, and other query optimization choices. These tools include:
set forceplan – forces the query to use the tables in the order specified in the from clause.
set table count – increases the number of tables that the optimizer considers at one time while determining join order.
select, delete, update clauses with (index...prefetch...mru_lru...parallel) –specifies the index, I/O size, or cache strategy to use for the query.
set prefetch –toggles prefetch for query tuning experimentation.
set sort_merge – disallows sort-merge joins.
set parallel_degree – specifies the degree of parallelism for a query.
sp_cachestrategy – sets status bits to enable or disable prefetch and fetch-and-discard cache strategies.
