Statistics and the Ascending Key Problem

This article examines the Ascending Key Problem, which can affect query performance due to outdated statistics, and provides ways to mitigate it through SQL Server. Solutions discussed include updating statistics more regularly than the built-in thresholds, and using Traceflag 2371 to reduce the threshold for statistics updates.

Visit the link to read more clearly about it.

Summary:

This article looks at the Ascending Key Problem, which can cause problems in query performance due to outdated statistics, and provides mitigations to deal with it in SQL Server.

Key takeaways:

1. The Ascending Key Problem relates to the most recently inserted data in a table, which may not have been sampled and included in the statistics histograms.
2. The Optimizer will guess that there are zero rows if the range you are looking for falls beyond the top of the histogram captured in the statistics object.
3. Scans aren't always bad for performance, as they can bail out once they are satisfied.

Counter arguments:

1. Updating statistics more regularly than the built-in automatic thresholds may not be necessary.
2. Traceflag 2371 reduces the threshold for statistics updates.