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

Comments

• Anonymous
  September 25, 2006
  Since we’re starting a new blog for the whole query processing team, we think it’s appropriate to start...

• Anonymous
  October 13, 2006
  We have seen this problem on SQL Server 2000 with datetime columns.  Are there any plans to add these trace flags in a future hotfix for 2000?

• Anonymous
  December 15, 2006
  I have been running into resource_semaphore_query_compile wait types. After several months of troubleshooting, our workaround is toggling forced paramaterization off/on, freeing the proc cache and updating stats. I am pretty sure that just updating stats would work but freeing the proc cahce provides some immediate temporary relief. The problem happens first thing in the morning when inserts are the highest. I think we may be running into this but will this type on stats updates cause recompiles of procs and parameterized queries?

• Anonymous
  June 16, 2014
  Through a support ticket with the Microsoft Linked Server support team, we found that this trace flag does not play well over linked servers, causing them to fail and severity 24 errors to be thrown. Although the flags did make a big difference in plan qualities, we had to remove them from all of our servers.