How many histogram steps?

The default value for number of histogram steps (20) may be adequate for columns with a small number of distinct values (that is, “low cardinality”), or for tables with a small number of rows. However, for tables with a large number of rows and that have columns with many distinct values, the default value may be insufficient, particularly when values for the column are not evenly distributed over the rows.

If you think the query processor is generating suboptimal query plans, try increasing the granularity of histograms by increasing their number of steps. Increasing their number of histogram steps can lead to higher resource consumption (particularly procedure cache usage) and longer optimization times before the query processor generates a query plan.

The optimization timeout limit configuration parameter may cause the query processor to generate suboptimal query plans when you increase the number of histogram steps.

You must determine whether a higher number of histogram steps results in better query plans and better overall performance. You may want to increase the value of number of histogram steps to 200; if this does not improve the query plans and performance, try a higher number, like 500.

Alternatively, use one histogram step for every 10,000 data pages. However, in general, you are unlikely to see any improvement by increasing the number of steps to more than 1000 – 2000. If you do not see an improvement in the query plans or performance after changing the number of histogram steps, you should look elsewhere for the bottleneck.

Adaptive Server determines the number of histogram steps when you execute update index statistics:

  1. number of histogram steps defines the default for all create index and update index statistics commands for new histograms. The default value for number of histogram steps is 20.

  2. You can explicitly set the number of histogram steps to the nnn value for update index statistics using:

    update index statistics table_name
    using nnn values
    
  3. update index statistics uses the current number of steps in the existing histogram when it creates a new histogram. The number of histogram steps configuration parameter does not apply to existing histograms. update [index] statistics overwrites an existing histogram only when you explicitly specify the number of steps with using nnn values.

    When this step completes, the value for the number of histogram steps configuration parameter is the target number of steps for the histogram. This is displayed in optdiag output as “Requested step count.”

  4. Adaptive Server multiplies the value for nnn (determined in step 2) by the value for histogram tuning factor to generate an internal, intermediate histogram. For example, if nnn is 100 and histogram tuning factor is 20, the intermediate histogram may have up to 2000 steps (20 * 100 = 2000). Generating this internal histogram allows Adaptive Server to increase the chance of identifying “frequency cells,” which include duplicate data values. If Adaptive Server finds no frequency cells, it reverts the histogram to the original number of steps. Adaptive Server keeps any frequency cells it finds.

    The original number of steps are displayed in optdiag output as “Requested step count.” optdiag displays the actual number of histogram steps in its output as “Actual step count.”

  5. In versions earlier than 15.0.1 ESD #1, Adaptive Server set histogram tuning factor to 1 by default. For version 15.0.1 ESD#1 and later, Adaptive Server uses a default value of 20 for histogram tuning factor. Sybase® recommends that you use the default of 20 unless directed otherwise by Sybase Technical Support.