Gonna pull together lots of stuff about cardinality estimates outside of the stats histogram here. It'll take me a while...
SQL Server keeps track of how many inserts and deletes since last stats update – when the number of inserts/deletes exceeds the stats update threshold the next time a query requests those stats it’ll qualify for an update. Trace flag 2371 alters the threshold function before SQL Server 2016. With 2016 compatibility mode, the T2371 function becomes default behavior. Auto-stats update and auto-stats update async settings of the database determine what happens once the stats qualify for an update. But whether an auto-stats update or a manual stats update, the density, histogram, etc are all updated.
Trace flags 2389, 2390, 4139, and the ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS hint operate outside the full stats framework, bringing in the quickstats update. They have slightly different scope in terms of which stats qualify for quickstats updates – but in each case its *only* stats for indexes, not stats for non-indexed columns that can qualify. After 3 consecutive stats updates on an index, SQL Server “brands” the stats type as ascending or static, until then it is branded ‘unknown’. The brand of a stat can be seen by setting trace flag 2388 at the session level and using dbcc show_statistics.
T2389 – Introduced in SQL Server 2005 SP1. quickstats update for stats branded ‘ascending’. Finds just the current max value, and density together with max value is used for estimate outside of histogram range. Histogram itself is not persistently amended.
T2390 – Introduced in SQL Server 2005 SP1. quickstats update for stats branded ‘unknown’. Finds current max value for stats branded ‘unknown’, as above.
T4139 – First available in SQL Server 2012 & 2014, released in the CU stream for each. A significant gap left with T2389 and T2390 is a case where an index stat has been branded ‘static’ based on its update pattern, even though over the longterm trend the highest value is ascending. In fact, if more than 10% of the edits to an index between stats updates are *within* the histogram range rather than 90%+ above the histogram range, it may be marked as static. So, with trace flag 4139 an index can qualify for a quickstats update regardless of its brand. Trace flag 4139 supersedes and exceeds T2389 & T2390 in that manner.
ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS – This hint was introduced in SQL Server 2016 SP1. It functions as trace flag 4139 does, but it can only be applied at the query level while trace flag 4139 can be enabled globally.
There are additional details, such as which indexes types are disqualified from quickstats updates, and when and how the new CE handles ascending keys and/or responds to the trace flags and hint. But this might already be more than you wanted
No comments:
Post a Comment