Statistics and Cardinality Estimation

Flags in this category fall into the below groupings:

  1. Cardinality Estimation Behavior –> Flags that affect the way that cardinality estimation is done by the query optimizer
  2. Informational, Stats Update, and Other –> Return info about the cardinality estimation process during optimization, or manipulate or provide information about statistics objects
  3. Fixes and Past Relevance –> Fix flags and flags which are no longer relevant.

TODO: add a SeeAlso in the security section to here for 9485


Short Descriptions

Flag Short Description
See Also  
205 Reports to error log when recompile occurs due to auto-update of stats.
8612 Adds cardinality info to query opt trees that are output by by 8605/8606/8607.
8666 Returns hidden nodes in query plan XML including stats usage and thresholds.
9348 Applies a row limit (based on card est) to whether bulk insert is attempted or not.
 
CE Behav  
2301 Old CE: use a different set of mathematical assumptions during estimation.
* 2312 Forces the use of the new cardinality estimator during optimization.
2324 Disables the creation of “implied predicates”.
2328 Reverts const/const comparison (incl. parms/vars) estimations to SQL2K guesses.
* 2389 Enables optimizer tracking of ascending keys and subsequent recompilation behavior.
* 2390 Similar to 2390 and the ascending key problem. See description below for more.
2453 Applies recompile thresholds for temp tables to table variables
4137 Adjusts calculation used for multiple AND predicates; effective w/correlation.
4138 Disables rowgoal logic for queries that contain TOP, OPTION(FAST N), IN, or EXISTS.
* 4139 Enables “quick stats” histogram amendments even if key col isn’t branded ascending.
9471 New CE: Use min selectivity for both conjunctive (AND) and disjunctive (OR) preds.
9472 New CE: Assume independence for multiple WHERE predicates.
9476 New CE: Use simple containment (old CE default) instead of base containment.
9479 New CE: Force use of “simple join” estimation algorithm
* 9481 New CE: Force use of the old cardinality estimation model.
9482 New CE: Turn off “overpopulated primary key” CE adjustment
9483 New CE: Forces QO to create non-persisted filtered stat objects.
9488 New CE: Reverts the estimation for multi statement TVFs back to 1 row.
9489 New CE: turn off new CE ascending key logic.
 
Other  
2309 Adjusts DBCC SHOW_STATISTICS output, showing info for partial statistics.
* 2363 New CE: Outputs info about stats used and resulting cardinality estimates.
* 2371 Lowers relative threshold for stats-based recompilation as table gets larger.
2388 Adjusts DBCC SHOW_STATISTICS output to show (not-)ascending status of stats obj.
7471 Allows multiple statistics objects on the same table to be updated concurrently.
8721 Prints info to error log when AutoStat occurs.
9204 Prints which stats objects the QO found interesting and then fully loaded.
9292 Prints which stats objects the QO found interesting and loaded the header.
9485 Disables SELECT permission for DBCC SHOW_STATISTICS.
 
Fix/PastRel  

Cardinality Estimation Behavior

2301 Doc2014

BOL 2014: “Enable advanced decision support optimizations”. 920093 adds: “Makes your optimizer work harder by enabling advanced optimizations that are specific to decision support queries, applies to processing of large data sets.” IJose_1 lists 5 fundamental mathematical assumptions the optimizer makes when using this flag, and contrasts these assumptions with behavior without this flag.

  • Integer Modelling for values falling between histogram steps. Helps with inequality filters.
  • Comprehensive Histogram Usage even when the cardinality estimate for a relation drops below the number of steps in the histogram.
  • Base Containment Assumption (see blog post)
  • Comprehensive Density Remapping changes how estimation is done when a CONVERT function call is present
  • Comprehensive Density Matching allows equi-joined columns to be considered equivalent for certain estimation operations.

Database-Wiki adds that the flag “discourages order-preserving parallelism”, especially parallel merge join. It also claims that the density remapping logic is also relevant to UPPER and LOWER (and CAST, of course) function calls.

Warning

IJose adds: “…compile times will increase, and in some cases memory consumption can increase dramatically.”

920093 | IJose_1 | Dima_1 | Connect_1

2312 Doc2014

BOL 2014: “Enables you to set the query optimizer cardinality estimation model to the SQL Server 2014 through SQL Server 2016 versions, dependent of the compatibility level of the database.” AKA force use of the new CE.

New CE Whitepaper | 2801413 | Nevarez_1

2324

Disables the creation of “implied predicates”. Implied predicates can be safely, mathematically inferred by other criteria in the query and added to the internal representation of the query to assist in cardinality estimation and various other optimizer transforms.

SQLPerf_1

2328

Reverts back to SQL 2000 behavior when comparing 2 constants (“constants” here includes parameters or variables whose values are known at compile time). The SQL 2000 behavior uses a guess formula, whereas the SQL 2005+ behavior uses the compile-time values and the statistics object(s) to generate a cardinality estimate. Dima demonstrates this selectivity guess (a call to CScaop_Comp::GuessSelect)

IJose_2 | Dima_2

2389 Doc2014

BOL 2014: “Enable automatically generated quick statistics for ascending keys (histogram amendment). If trace flag 2389 is set, and a leading statistics column is marked as ascending, then the histogram used to estimate cardinality will be adjusted at query compile time.” Itzik has very insightful comments about this flag and the ascending key problem under both the old and new CE. Kejser notes that (at the time of his posts) this TF doesn’t work with partitioned tables, and has his own solution. See also 9489 (Dima). (See also 2388, 2390, and 4139 )

Hotfixes: 922063, 929278, 2952101 | Itzik_1 | IJose_3 | Stellato_2 | Kejser Part 1 | Kejser Part 2 | Nevarez_2

2390 Doc2014

BOL 2014: “Enable automatically generated quick statistics for ascending or unknown keys (histogram amendment). If trace flag 2390 is set, and a leading statistics column is marked as ascending or unknown, then the histogram used to estimate cardinality will be adjusted at query compile time.” Closely tied to 2389, 4139, and the ascending key problem.

Warning

Read Ian Jose’s blog entry carefully before using 2390.

IJose_3

2453 Doc2014

BOL 2014: “Allows a table variable to trigger recompile when enough number of rows are changed.” Applies familiar temp table rowcount recompilation thresholds to table variables. Bertrand points out important caveats.

2952444 | 2012SP2 | CSS_1 | Bertrand

4136 Doc2014

BOL 2014: “Disables parameter sniffing unless OPTION(RECOMPILE), WITH RECOMPILE or OPTIMIZE FOR value is used.” There are now a number of ways for disabling parameter sniffing:

  • This flag
  • The USE HINT option DISABLE_PARAMETER_SNIFFING
  • The database-scoped option PARAMETER_SNIFFING
  • The OPTIMIZE FOR UNKNOWN query hint

Dima notes that 4136 has no effect on parameter sniffing’s “cousin”, runtime constant sniffing.

980653 | USE HINT | DB SCOPED CONFIG | Dima_10

4137 Doc2014

BOL 2014: “Causes SQL Server to generate a plan using minimum selectivity when estimating AND predicates for filters to account for correlation, under the query optimizer cardinality estimation model of SQL Server 2012 and earlier versions. Beginning with SQL Server 2016 SP1, to accomplish this at the query level, add the ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES. USE HINT” The BOL USE HINT link notes that this hint is equivalent to 4137 (old CE) and similar to 9471 (new CE).

Paul White covers “Minimum Selectivity” well, and also points out that 4137 only applies min selectivity to conjunctive (AND) predicates, while 9471 applies it to both conjunctive and disjunctive (OR) predicates.

2658214 | USE HINT | PWhite_1 | Dima_3 | Connect_2

4138 Doc2014

BOL 2014: “Causes SQL Server to generate a plan that does not use row goal adjustments with queries that contain TOP, OPTION (FAST N), IN, or EXISTS keywords. Beginning with SQL Server 2016 SP1, to accomplish this at the query level, add the DISABLE_OPTIMIZER_ROWGOAL USE HINT.”

The 2 interesting StackExch links show Martin Smith at work.

2667211 | USE HINT | PWhite_2 | PWhite_3 | StackExch_1 | StackExch_2

4139 Doc2014

BOL 2014: “Enable automatically generated quick statistics (histogram amendment) regardless of key column status. If trace flag 4139 is set, regardless of the leading statistics column status (ascending, descending, or stationary), the histogram used to estimate cardinality will be adjusted at query compile time. Beginning with SQL Server 2016 SP1, to accomplish this at the query level, add the ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS USE HINT.”

The KB introduces the flag as a fix flag for a case where 90% of newly-inserted values were NOT higher than the highest key value. However, the BOL description indicates an intent for the flag to be used in any situation where out-of-bounds values are too costly.

Warning

3192117 notes this flag can cause access violations on certain SQL 2016 builds.

2952101 | 3192117 | USE HINT

9471 Doc2014

BOL 2014: “Causes SQL Server to generate a plan using minimum selectivity for single-table filters, under the query optimizer cardinality estimation model of SQL Server 2014 through SQL Server 2016 versions. Beginning with SQL Server 2016 SP1, to accomplish this at the query level, add the ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES USE HINT.” The BOL USE HINT link notes that this hint is equivalent to 4137 (old CE) and similar to 9471 (new CE).

Paul White covers “Minimum Selectivity” well, and also points out that 4137 only applies min selectivity to conjunctive (AND) predicates, while 9471 applies it to both conjunctive and disjunctive (OR) predicates.

USE HINT | PWhite_1 | Milo_1 | Connect_2

9472

Assumes independence for multiple WHERE predicates in the SQL 2014 CE. Predicate independence was the default before SQL 2014, and thus this flag can be used to emulate pre-SQL 2014 CE behavior in a more specific fashion than TF 9481.

PWhite_1 | Connect_2

9476 Doc2014

BOL 2014: “Causes SQL Server to generate a plan using the Simple Containment assumption instead of the default Base Containment assumption, under the query optimizer cardinality estimation model of SQL Server 2014 through SQL Server 2016 versions. Beginning with SQL Server 2016 SP1, to accomplish this at the query level, add the USE HINT ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS.”

Simple containment is the old CE default; base containment is the new CE default. Paul White email: “Ignores the histograms (avoiding coarse alignment) and simply assumes containment at the join.”

USE HINT | 3189675 | Dima_1

9479

Dima: “Forces the optimizer to use Simple Join [estimation] even if a histogram is available…will force optimizer to use a simple join estimation algorithm, it may be CSelCalcSimpleJoinWithDistinctCounts, CSelCalcSimpleJoin or CSelCalcSimpleJoinWithUpperBound, depending on the compatibility level and predicate comparison type.”

Paul White email: “uses simple containment instead of base containment for simple joins”

Dima_4

9481 Doc2014

BOL 2014: “Enables you to set the query optimizer cardinality estimation model to the SQL Server 2012 and earlier versions, irrespective of the compatibility level of the database. Beginning with SQL Server 2016 SP1, to accomplish this at the query level, add the USE HINT FORCE_LEGACY_CARDINALITY_ESTIMATION.”

Note also the LEGACY_CARDINALITY_ESTIMATION database-scoped setting.

USE HINT | DB SCOPED CONFIG | 2801413

9482

Turns off the “overpopulated primary key” adjustment that the optimizer might use when determining that a “dimension” table (the schema could be OLTP as well) has many more distinct values than the “fact” table. (The seminal example is where a Date dimension is populated out into the future, but the fact table only has rows up to the current date). Since join cardinality estimation occurs based on the contents of the histograms of the joined columns, an “overpopulated primary key” can result in higher selectivity estimates, causing rowcount estimates to be too low.

Dima_5

9483

Forces the optimizer to create (if possible) a filtered statistics object based on a predicate in the query. This filtered stat object is not persisted. In Dima’s example, the filtered stat object is actually created on the join column…i.e. “CREATE STATISTICS [filtered stat obj] ON [table] (Join column) WHERE (predicate column = ‘literal’)”

Dima_6

9488

Reverts the estimation behavior for multi-statement TVFs back to 1 row instead of the 100-row estimate behavior that was adopted in SQL 2014.

Dima_7

9489

Turns off the new CE logic that handles ascending keys.

Dima_8


Informational, Stats Update, and Other

2309 (Info)

In SQL 2014, enables output from a 3rd parameter for DBCC SHOW_STATISTICS such that the partial statistics histogram (for just one partition) is shown.

Stellato_1 | DBIServices_1

2363 (Info)

Outputs information regarding statistics information used and derived during the (new CE) optimization process. Dima shows it giving insight into the new “calculator” framework in 2014 for deriving statistics at intermediate nodes of the tree. It also shows which histograms (statistics objects) were loaded, and acts as a replacement for 9204 and 9292.

Dima_9 | PWhite_1

2371 Doc2014

BOL 2014: “Changes the fixed auto update statistics threshold to dynamic auto update statistics threshold. Note: Beginning with SQL Server 2016 this behavior is controlled by the engine and trace flag 2371 has no effect.” Switches the threshold for a performance-based recompile (for a given stats object) from the default of 20%+500 rows to SQRT(1000*<num rows in table>).

2754171 | SRGolla | SAPonSQLServer

2388 (Info)

Changes the output of DBCC SHOW_STATISTICS. Instead of the normal Header/Vector/Histogram output, instead we get a single row that gives information related to whether the lead column of the stat object is considered to be ascending or not. This TF is primarily helpful in watching the state of a stat object change from “Unknown”, to “Ascending” (and potentially to “Stationary”).

Nevarez_2 | SQLSasquatch_2 | Stellato_2

7471

Allows multiple statistics objects on the same table to be updated concurrently.

3156157 | TigerTeam_1

8721 Doc2014 (Info)

BOL 2014: “Reports to the error log when auto-update statistics executes.” The KB also describes locks taken by autostats.

195565 | SQLSasquatch_1

9204 (Info)

PWhite: for the old CE, gives “the interesting statistics which end up being fully loaded and used to produce cardinality and distribution estimates for some plan alternative or other.”

PWhite_4

9292 (Info)

PWhite: for the old CE, gives “a report of statistics objects which are considered ‘interesting’ by the query optimizer when compiling, or recompiling the query in question. For [these] potentially useful statistics, just the header is loaded.”

PWhite_4

9485 Doc2012
BOL 2014: “Disables SELECT permission for DBCC SHOW_STATISTICS.”

Fixes and Past Relevance

These flags either are old and irrelevant for modern builds, appear only in CTPs, or enable a fix in a CU but are baselined in a later service pack or release.