histogram tuning factor

histogram tuning factor controls the number of steps Adaptive Server analyzes per histogram for update statistics, update index statistics, update all statistics, and create index.

In the following example, Adaptive Server generates an intermediate 20-step histogram with 30 values:

sp_configure 'histogram tuning factor',20
update statistics tab using 30 values

Adaptive Server analyzes the histogram and compresses it into the resulting histogram according to the following parameters:

• The first step is copied unchanged.

• The high-frequency steps are copied unchanged.

• The consecutive range steps are collapsed into the resulting step, so the total weight of the collapsed step would not be bigger than one-thirtieth of the value.

The final histogram in sysstatistics:

• Has range steps generated in a way similar for a 30-step update statistics, and high frequency ranges are isolated as if the histogram were created with 600 steps.

• The total number of steps in the resulting histogram may differ between 30 and 600 values.

• For equally distributed data, the value should be very close to 30.

• More “frequent” values in the table means more steps in the histogram.

• If a column has few different values, all those values may appear as high-frequency cells.

You could achieve the same result by increasing the number of steps to 600 as using histogram tuning factor, but this would use more resources in the buffer and procedure cache

historgram tuning factor minimizes the resources histograms consume, and only increases resource usage when it is in the best interest for optimization. For example, when there is non-uniform distribution of data in a column, or highly duplicated values within a column. In this situation, up to 600 histogram steps are used. However, in most cases, it uses the default value (30 in the example above).

Copyright © 2004. Sybase Inc. All rights reserved.

View this book as PDF