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 final histogram in sysstatistics:

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).