Ascending Keys and Auto Quick Corrected Statistics
A common problem for some SQL Server applications are cases where data typically ascends. For example, datetime columns where the column represents a current date. SQL Server builds statistics with the assumption that the data will by in large be similar in the future. However, when data typically ascends, most new insertions are out of the previously found range. This can lead to poorly performing plans as filters selecting recent data seem to exclude the entire relation when in fact a significant number of rows are included.
Trace flag 2389 and 2390, both new in SQL Server 2005 SP1, can help to address this problem. SQL Server 2005 SP1 begins to track the nature of columns via subsequent operations of updating statistics. When the statistics are seen to increase three times the column is branded ascending. If trace flag 2389 is set, and a column is branded ascending, and a covering index exists with the ascending column as the leading key, then the statistics will be updated automatically at query compile time. A statement is compiled to find the highest value and a new step is added at the end of the existing histogram to model the recently added data.
Trace flag 2390 enables the same behavior even if the ascending nature of the column is not known. As long as the column is a leading column in an index, then the optimizer will refresh the statisitc (with respect to the highest value) at query compile time. Never use 2390 alone since this would mean that this logic would be disabled as soon as the ascending nature of the column was known.
-- enable auto-quick-statistics update for known ascending keys
dbcc traceon( 2389 )
-- neable auto-quick-statistics update for all columns, known ascending or unknown
dbcc traceon( 2389, 2390 ) -- never enable 2390 alone