histogram tuning factor

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

Summary Information
Default value	20
Range of values	1 – 100
Status	Dynamic
Display level	Intermediate
Required role	System administrator
Configuration group	SQL Server Administration

A value of 1 disables histogram tuning factor.

Note: For SAP ASE versions 15.0.2 ESD #2 and later, if you set histogram tuning factor to the default value of 20 and a large number of steps are requested for the histogram, the actual step count used for the histogram is limited to the value that reduces the procedure cache usage:

min (max (400, requested_steps), histogram_tuning_factor X requested_steps)

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

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

SAP ASE 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 is no 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 can achieve the same result by increasing the number of histogram steps to 600, but this uses more resources in the buffer and procedure cache

histogram tuning factor minimizes the resources histograms consume, and increases resource usage only when it is in the best interest for optimization, for example, when there is uneven 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, histogram tuning factor uses the default value (30 in the example above).