You have taken a number of steps to get information about your optimizer problem. Here is an explanation of each of these steps:
select @@version displays the version of Adaptive Server you are running, including the SWR level and platform.
sp_help provides more accurate information about a table than the script you used to create the table and its indexes. In the event that indexes have been added or changed or that columns have been added via alter table, sp_help will show the present state of the table(s).
The set showplan on command shows which query plan the optimizer has chosen for your query. Use set showplan on before running any query or procedure you will be analyzing.
In some cases you may need to issue the set noexec on command to save time when you are running a very long query. The order of these commands is important:
set showplan on set noexec on go <query text...> go
There are several important items of information to look for when reading showplan output:
Cache Utilization
Adaptive Server uses two major strategies, named LRU and MRU respectively, for its data cache. The type of strategy used in a given query depends on whether a cached page needs to be accessed more than once. showplan’s "Buffer Replacement Strategy" messages show the cache strategy used for data pages and index leaf pages. See “Caches and Object Bindings” in the Performance and Tuning Guide for more information about cache strategies.
If you want to investigate your caches, for example to learn whether a cache is under- or over-utilized, you can use sp_sysmon. See “Data Cache Management” in the Performance and Tuning Guide.
Index Utilization
Was an index used? Which one? Was a table scan done? To answer these questions, check the portion of showplan output following FROM TABLE for messages like "Table Scan" or "Using Clustered Index".
Join Information
When evaluating joins, look for
the order of tables in a join, also known as join order; knowing the order that the optimizer chose for joins is critical to your analysis. When your query joins two or more tables, showplan’s FROM TABLE messages show the order in which the optimizer will join the tables.
Whether it is a nested-loop join or a sort-merge join (applies to 12.0 and later).
Refer to “Using set showplan” in the Performance and Tuning Guide for more information on interpreting showplan results.
Since any analysis of a performance problem will require knowledge of the number and types of I/Os performed for the query, the set statistics io on command is critical.
If your query is taking very long to complete, using statistics
io and statistics time may not be feasible.
If you analyze your long-running query using set noexec
on, you cannot obtain I/O information since noexec
on stops all the output of statistics io.
The set statistics io on command provides you with the following information:
Physical reads
This is the number of times Adaptive Server accesses the disk. The first time a query is run, the number of physical reads will generally be high. This happens because the required pages are not usually in cache. Subsequent runs of the query can access the pages in cache, and physical reads are minimized, if not avoided. If the number of physical reads remains high during subsequent executions of a query, you will need to take a close look at how the query executes.
In some instances, the size of the data cache may also be a problem. If it is too small, pages have to be read from disk more often. Likewise, configuration of named caches and use of large I/O buffer pools can have an impact on performance. See “Memory Use and Performance” in the Performance and Tuning Guide for details on configuring the data cache to improve performance.
Logical reads
Logical reads are a combination of physical reads and "cache hits" - reads from pages in cache. If your statistics show a number of logical reads and no physical reads, it means that all required pages are in cache, which is the ideal situation. To determine the cache hit ratio (the percentage of pages that were found in cache) for your query, use the formula:
Logical reads - (Physical reads * Pages per I/O) Cache hit ratio = ----------------------------------------------- Logical reads
Use set showplan on to see the I/O size used by the query. With 2K pages, a query using 4K I/O reads 2 pages with each I/O.
Scan count
This is the number of times the table was read (using either a table scan or an index) in order to find rows to satisfy the query or join. In nearly all simple single table queries, the scan count will be 1. When an OR clause is present there will be one scan count for each OR in the query. In the case of a join, the scan count can be crucial.
If the optimizer chose a bad join order, you are likely to see a very high number of scan counts on a large table, causing a very high number of logical reads. However, you should take the table size into account when interpreting scan counts. A high scan count on a small table is preferable to a moderate scan count on a large table. Although the scan count of the small table is high, the physical reads should be low. A 1000-scan count for a 1-page table is better than a 100-scan count of a 1000-page table.
The following example demonstrates how join order and scan count affect the number of reads (on 12.0 and later, the example represents a nested-loop join):
Table A has 1 page and 10 rows that qualify for the join. Table B has 1000 pages and 10 rows that qualify for the join.
If Table B is the outer table of the join Adaptive Server will only need to read through it once in order to find all qualifying rows. The single scan totals 1000 reads. Adaptive Server then reads Table A for each qualifying row found in B. The single page in A is scanned 10 times, equaling 10 reads, with a total of 1010 reads for the query. If A were the outer table Adaptive Server would have to read B once for each of the ten qualifying rows on A: 1000 pages multiplied by 10 scans equals 10,000 reads.
This example assumes that there is no useful index available.
Total writes for this command
This is the total number of writes Adaptive Server did for the query. This count includes inserts, updates and deletes on user tables, temporary tables and work tables. Even queries that do not include data manipulation statements may require writes to work tables or temporary tables, which are counted here.
set statistics time on provides the following information:
Adaptive Server elapsed time
This is the total accumulated elapsed time that is recorded for the query or command. This can seem long if, for example, a query was blocked by a lock, network traffic or other resource contention. The time the query must wait for the blockage to clear is added to the elapsed time.
Adaptive Server CPU time
This is the amount of time for which the query had exclusive use of the CPU. It reflects the time taken to parse, compile, and execute the query. Functions add to the CPU time. For example, a convert statement will increase the CPU time slightly. Also, compute-intensive queries and queries that perform a large amount of I/O take more CPU time.
The output of set statistics time on may be useful, but it is not usually a significant factor in most optimizer analyses.
This trace flag sends the output of dbcc traceon (302) and dbcc traceon (310) to the screen.
This trace flag returns the optimizer's cost estimates for each SARG and join clause in the query. Trace flag 302 is documented in greater detail in “Tuning with dbcc traceon” in the Performance and Tuning Guide.
Here is the information to watch for in dbcc traceon (302) output:
All SARGs and join clauses in the query should be shown in the optimizer's cost estimates. If not, determine why.
Check that row and page counts are accurate, since these counts are important for optimization.
If you think that the page and row counts are off, check the counts. Run optdiag statistics. To improve performance, counts and other statistics are changed in memory and flushed to systabstats periodically by the housekeeper task. You can also flush in-memory statistics to systabstats by running optdiag statistics or executing sp_flushstats table_name.
dbcc traceon (310) gives the optimizer cost estimates for permutations of a join or joins. Examine the dbcc traceon (310) output to determine whether the query is “connected.” If so, it indicates that the join will not result in a cartesian product. The statement “query is connected” will appear after the optimizer has performed cost estimates on all possible indexes, as indicated in the output of dbcc traceon (302) .
