Optimization issues

Although the query optimizer can optimize most queries efficiently, there are some optimization issues:

Use the set option show_missing_stats on command before you execute a problem query to determine if there are any statistics that the optimizer could have used that were not available. Use update statistics, if possible, to eliminate the missing statistics warnings.

These situations highlight the need to follow some best practices that will allow the query optimizer to perform at its full potential. Some of the practices that you may choose to employ are discussed below.

Create search arguments

When you write search arguments for your queries:

Table 1-4: set commands

set command

Arguments

set show_sqltext

on | off

set showplan

on | off

set statistics io

on | off

set statistics time

on | off

set statistics plancost

on | off

Table 1-5: set options

set option

Arguments

set option show

normal | brief | long | on | off

set option show_lop

normal | brief | long | on | off

set option show_parallel

normal | brief | long | on | off

set option show_search_engine

normal | brief | long | on | off

set option show_counters

normal | brief | long | on | off

set option show_managers

normal | brief | long | on | off

set option show_histograms

normal | brief | long | on | off

set option show_abstract_plan

normal | brief | long | on | off

set option show_best_plan

normal | brief | long | on | off

set option show_code_gen

normal | brief | long | on | off

set option show_pio_costing

normal | brief | long | on | off

set option show_lio_costing

normal | brief | long | on | off

set option show_log_props

normal | brief | long | on | off

set option show_elimination

normal | brief | long | on | off

Use SQL-derived tables

Queries expressed as a single SQL statement make better use of the query processor than queries expressed in two or more SQL statements. SQL-derived tables enable you to express, in a single step, what might otherwise require several SQL statements and temporary tables, especially where intermediate aggregate results must be stored. For example:

select dt_1.* from 
   (select sum(total_sales) 
        from titles_west group by total_sales)
               dt_1(sales_sum),
   (select sum(total_sales) 
         from titles_east group by total_sales)
               dt_2(sales_sum)
where dt_1.sales_sum = dt_2.sales_sum

Here, aggregate results are obtained from the SQL-derived tables dt_1 and dt_2, and a join is computed between the two SQL-derived tables. Everything is accomplished in a single SQL statement.

For more information on SQL-derived tables, see the Transact-SQL User's Guide.

Tune according to object sizes

To understand query and system behavior, know the sizes of your tables and indexes. At several stages of tuning work, you need size data to:

See the System Administration Guide for more information on sizing.