SQL Server Tuning for Data Warehouse & TPC-H

DATE_CORRELATION_OPTIMIZATION

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.

Trace Flag 2301

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 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.