SQL Server sample Update of Statistics misses highest RANGE_HI_KEY on ascending key column
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 usingFULLSCAN
?
It's a limitation of the current implementation of sampled statistics. As it stands, sampled statistics collection uses TABLESAMPLE SYSTEM
, which uses an allocation-order scan and chooses pages from the scan to sample. Only chosen pages contribute to the histogram.
Since the scan is allocation-ordered (rather than index-ordered), there is no way to give preference to the first and last pages in key order.
For more information see this related question:
How does sampling work when updating statistics?
and my article, Allocation Order Scans
For workarounds, see Statistics on Ascending Columns by Fabiano Amorim
Quick guess: enable trace flag 4139.
You are already running SP2-CU1 so this should work
Note the guidelines about TF 2389 ad 2390 too and also see http://sql-sasquatch.blogspot.com.mt/2013/06/mssql-plan-guides-to-address-ascending.html
(Note, I've not really hit this problem and have not dived in that deep)