Query Tuning Tools

This chapter provides a guide to the tools that can help you tune your queries.

The tools mentioned in this chapter are described in more detail in the chapters that follow.

Overview

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.
See Chapter 36, "Using set showplan."
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.
See Chapter 38, "Tuning with dbcc traceon."
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.

How tools may interact

showplan, statistics io, and other commands produce their output while stored procedures are being run. The system procedures that you might use for checking table structure or indexes as you test optimization strategies can produce voluminous output when diagnostic information is being printed. You may want to have hard copies of your table schemas and index information, or you can use separate windows for running system procedures such as sp_helpindex.

For lengthy queries and batches, you may want the save showplan and statistics io output in files. You can do so by using "echo input" flag to isql. The syntax is:

isql -P password -e -i input_file -o outputfile

Using showplan and noexec together

showplan is often used in conjunction with set noexec on, which prevents SQL statements from being executed. Issue showplan, or any other set commands, before you issue the noexec command. Once you issue set noexec on, the only command that Adaptive Server executes is set noexec off. This example shows the correct order:

set showplan on
set noexec on
go
select au_lname, au_fname
    from   authors
    where  au_id = "A137406537"
go

noexec and statistics io

While showplan and noexec make useful companions, noexec stops all the output of statistics io. The statistics io command reports actual disk I/O; while noexec is in effect, no I/O takes place, so the reports are not printed.

How tools relate to query processing

Many of the tools, for example, the set commands, affect the decisions made by the optimizer. showplan and dbcc traceon(302, 310) show you optimizer decision-making. dbcc traceon(302,310) shows intermediate information as analysis is performed, with dbcc traceon(310) printing the final plan statistics. showplan shows the final decision on access methods and join order.

statistics io and statistics time provide information about how the query was executed: statistics time measures time from the parse step until the query completes. statistics io prints actual I/O performed during query execution.

noexec allows you to obtain information such as showplan or dbcc traceon(302,310) output without actually executing the query.


	Expand Search