Disabling Constant-Constant Comparison Estimation

SQL Server 8.0 did not perform cardinality estimates based on the comparion of two constants.  Instead, SQL Server 8.0 guessed at the resulting selectivity.  The reasoning for this is that one or more of the constants may be statement parameters, which would change from one execution of the statement to the next.  However, SQL Server 9.0 reversed this behavior and does esimate operator cardinality based on the comparison of two constants, when both values are available at compile time.  Constant values are avilable at compile when when 1) they are literal constants or 2) they are parameters to a stored procedure or otherwise set at the nesting level above the the compilation of the statement.  This change in behavior can be problematic for statements that optimize for one set of values but run for other sets of values.  Applications that find adverse plan changes resulting from the change in behvior can revert behavior to that of the previous released by enabling trace flag 2328.  For example, to revert the behavior server wide, for the current invokation of SQL Server, one could run the following command:

dbcc traceon( 2328, -1)

If running SQL Server 8.0, or SQL Server 9.0 with trace flag 2328 enabled, the optimizer will guess the selectivity arising from the comparion of two constants.  The guess used is the same as comparing a column to an constant (where either the column distribution or the constant value, or both, is unknown).  Since the selectivity of the comparison of two constants is either 0 or 1, one might think that a guess for such a comparion wouldbe 50%.  Although there is a lot of logic in this behavior, for historic reasons, the guess used by SQL Server was very different. 

Guessing the selectivity of a column-constant comparsion attempts to model several phenomena.  First, when there are many rows, the selectivity is likely smaller because there could be more values from which to choose.  Second, when there are many such conjuncts, it is more likely that subsequent conjuncts will be correlated with previous conjucts.  SQL Server therefore reduces the selectivity from one to 0 by the exponent of the cardinality and the reducition factor is reduced with each guess.  The following table shows the number of conjucts guessed and the resultant selectivity as a function of input table cardinality of N:

 

# Conjucts         Cardinality            Selectivity

1                        N^(3/4)               N^(-1/4)

2                        N^(11/16)           N^(-5/16)

3                        N^(43/64)           N^(-21/64)

4                        N^(171/256)       N^(-85/256)

5                        N^(170/256)       N^(-86/256)

6                        N^(169/256)       N^(-87/256)

7                        N^(168/256)       N^(-88/256)

...

175                    N^(0/256)          N^(-1)