The SET DATE_CORRELATION_OPTIMIZATION option improves the performance of queries that perform an equi-join between two tables whose date or datetime columns are correlated, and which specify a date restriction in the query predicate.
When the DATE_CORRELATION_OPTIMIZATION database option is set to ON, SQL Server maintains correlation statistics between any two tables in the database that have date or datetime columns and are linked by a one-column foreign key constraint. By default, this option is set to OFF.
SQL Server uses these correlation statistics together with the date restriction specified in the query predicate to infer that additional restrictions can be added to the query without changing the result set. The query optimizer uses these inferred conditions when it chooses a query plan. A faster query plan may result, because the added restrictions let SQL Server read less data when it is processing the query. Performance is also improved when both tables have clustered indexes defined on them, and their date or datetime columns for which correlation statistics are maintained are the first or only key of the clustered index.
See Ian Jose's blog Query Processor Modelling Extensions in SQL Server 2005 SP1 for more on this.
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:
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 drops 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.