Statistics - Pro SQL Server Internals - page 69

Containment: This model assumes that when two attributes “might be the same,” they
are indeed the same. It means that when you join two tables, in the absence of statistical
information, the model assumes that all distinct values from one table exist in the other.

Inclusion: This model assumes that when an attribute is compared to a constant, there is
always a match.

Even though such assumptions provide acceptable results in many cases, they are not always correct. Unfortunately,
the original implementation of the model makes it very hard to refactor, which led to a decision to redesign it in
SQL Server 2014.
SQL Server 2014 uses a new cardinality estimation model when the database compatibility level is set to 120,
which is SQL Server 2014. This happens when a database was created in SQL Server 2014 or when the compatibility
level was set explicitly with the ALTER DATABASE SET COMPATIBILITY_LEVEL = 120 command. For databases with a
lower compatibility level, the legacy cardinality estimation model is used.
The cardinality estimation model can be specified at the query level. Queries with OPTION (QUERYTRACEON 2312)
hint use the new cardinality estimator when the database does not have a compatibility level of 120. Alternatively,
queries with OPTION (QUERYTRACEON 9481) hint use the legacy cardinality estimation model when the database has a
compatibility level of 120.

■ the QUERYTRACEFLAG option enables a trace flag on a single query scope. You can get the same results server-wide
by specifying a trace flag as SQL Server startup parameter. Obviously, be careful with such an approach.

Tip

Unfortunately, there is very little information available in the public domain about the new cardinality estimation
model. Nevertheless, let's examine a few different examples and compare the behavior of the legacy and new
cardinality estimators.

Comparing Cardinality Estimators: Up-to-Date Statistics
As a first test, let's check out how both models perform estimations when statistics are up to date. Listing 3-12
constructs a test table, populates it with some data, and creates clustered and nonclustered indexes on the table.

Listing 3-12. Comparing Cardinality Estimators: Test table creation
create table dbo.CETest
(
ID int not null,
ADate date not null,
Placeholder char(10)
);

;with N1(C) as (select 0 union all select 0) -- 2 rows
,N2(C) as (select 0 from N1 as T1 cross join N1 as T2) -- 4 rows
,N3(C) as (select 0 from N2 as T1 cross join N2 as T2) -- 16 rows
,N4(C) as (select 0 from N3 as T1 cross join N3 as T2) -- 256 rows
,N5(C) as (select 0 from N4 as T1 cross join N4 as T2) -- 65,536 rows
,IDs(ID) as (select row_number() over (order by (select NULL)) from N5)
insert into dbo.CETest(ID,ADate)
select ID,dateadd(day,abs(checksum(newid())) % 365,'2013-06-01')
from IDs;