Summary information |
|
|---|---|
Default value |
20 |
Range of values |
1 – 100 |
Status |
Dynamic |
Display level |
Intermediate |
Required role |
System administrator |
Configuration group |
SQL Server Administration |
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. A value of 1 disables the parameter.
For Adaptive Server 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, 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 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).
