If your table has 5000 rows, and one value in the column that has only one matching row, you may need to request 5000 steps to get a histogram that includes a frequency cell for every distinct value. The actual number of steps is not 5000; it is either the number of distinct values plus one (for dense frequency cells), or twice the number of values plus one (for sparse frequency cells).
The sp_configure option histogram tuning factor automatically chooses a larger number of steps, within parameters, when there are a large number of highly duplicated values.
The default value of the histogram tuning factor is 20 in Adaptive Server version 15.0 and later. If the requested step count is 50, then update statistics can create up to 20 * 50 = 1000 steps. This larger number of steps is used only if histogram distribution is skewed with a number of domain values that are highly duplicated. However, for a unique column, update statistics uses only 50 steps to represent the histogram. To most efficiently use histograms, specify a relatively low number of steps and allow the histogram tuning factor to determine whether more steps would be useful for optimization. For example, instead of specifying 1000 steps with a default step count of 1000 to be used by all histograms, it is better to specify 50 default steps and a histogram tuning factor of 20. This allows Adaptive Server to determine the best step count, within the range of 50 to 1000 steps, with which to represent the distribution.
