I'm trying to understand how Statistics sampling works and whether or not the below is expected behaviour on sampled statistics updates.
We have a large table partitioned by date with a couple of billion rows. The partition date is the prior business date and so is an ascending key. We only load data into this table for the prior day.
The data load runs overnight, so on Friday 8th April we loaded data for the 7th.
After each run we update statistics, although take a sample, rather than a
Maybe I am being naïve, but I would have expected SQL Server identify the highest key and lowest key in the range to ensure it got an accurate range sample. According to this article:
For the first bucket, the lower boundary is the smallest value of the column on which the histogram is generated.
However, it doesn't mention the last bucket/largest value.
With the sampled Statistics update on the morning of the 8th, the sample missed the highest value in the table (the 7th).
As we do a lot of querying on data from the prior day, this resulted in inaccurate cardinality estimation and a number of queries timing out.
Should SQL Server not identify the highest value for that key and use that as the maximum
RANGE_HI_KEY? Or is this just one of the limits of update without using
Version SQL Server 2012 SP2-CU7. We cannot currently upgrade due to a change in
OPENQUERY behaviour in SP3 that was rounding down numbers in a linked server query between SQL Server and Oracle.