Share via

Query Processor Modelling Extensions in SQL Server 2005 SP1

  • Article

Trace flag 2301, available in SQL Server 2005 SP1, enhances the modelling ability of the query optimizer to better handle complex statements.  Improved modelling can lead to dramatically faster performing query plans in some cases.  These extensions to the query processor modelling abilities can lead to increased compile time and so should only be used by applications which compile infrequently.  The model extensions are as follows:

Integer Modelling

Normally, histogram modelling assumes that values between histogram steps are equally distributed to every numerical double code point.  This modelling extension remembers, for integer base types, that values can only occur on integer code points and this improves cardinality estimates for inequality filters.

Comprehensive Histogram Usage

Normally, histograms are ignored when the cardinality of a relation dropps below the number of steps in a histogram.  This is a heuristic which captures the liklihood that a histogram continues to describe a relation.  This modelling extension applies the histogram in cardinality estimate regardless of the cardinality estimate for the relation.

Base Containment Assumption

Normally, when two relations are joined, we assume that X distinct code points in the same key range on input relation R will join with Y distinct code points in the same key range on input relation S such that MIN(X,Y) will find matches.  This assumption is called Simple Containment.  We assume that the smaller number of distinct code points match with code points from the other side.  This modelling ignores the relative population of distinct code points in the base forms of R and S, and also ignores any filtering that has occured to the base forms for R and S before joining.  Base containment applies the containment assumption only to the base relations and uses probabilistic methods to compute the degree of joining.  In addition, implied filters are modelled correctly since their behavior is very different from orthogonal filters.

Comprehensive Density Remapping

Normally, when columns are CONVERTed only a small number of densities involving such columns are remapped to the new column definitions.  Note that operations like convert rarely change the density of a column.  Density is the measure of the number of duplicate values for each distinct value.  With this modelling extension, all such remappings are applied which makes possible subsequent density matching for the purposes of cardinality estimation.  In some cases, this can lead to excessive use of memory.

Comprehensive Density Matching

Normally, densities are matched when the very same base column is filtered or joined.  With this modelling extension, the notion of equivalence of columns as a result of equi-joins is applied leading to more complete density matching for the purposes of improved cardinality estimation.

These extentions all were developed to address customer found problems relation to poor performing query plans.  If customers experience such poor performing plans where one or more of the above extentions may help, then trace flag 2301 may be applied.  It is important to note that compile times will increase, and in some cases memory consumption can increase dramatically.  Thus, it is important to apply this trace flag with care and test exhaustively before using in production.

Comments