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 releases 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:
min (max (400, requested_steps), histogram_tuning_factor X requested_steps)
which reduces the procedure cache usage.
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
histogram 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).
