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 11, “Determining Sizes of Tables and Indexes.” in the book Performance and Tuning: Basics.
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 4, “Using the set statistics Commands,” in the book Performance and Tuning: Monitoring and Analyzing for Performance.
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.
See Chapter 5, “Using set showplan,” in the book Performance and Tuning: Monitoring and Analyzing for Performance.
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” on page 62 in the book Performance and Tuning: Monitoring and Analyzing for Performance 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.
See Chapter 7, “Tuning with dbcc traceon,” in the Performance and Tuning: Monitoring and Analyzing for Performance book.
The optdiag utility command displays statistics for tables, indexes, and columns.
See Chapter 6, “Statistics Tables and Displaying Statistics with optdiag,” Performance and Tuning: Monitoring and Analyzing for Performance book.
Chapter 3, “Advanced Optimizing Tools,” in Performance and Tuning: Optimizer 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.
