Cardinality Estimator Series III- Enabling New Cardinality Estimator (SQL Server 2014) with TRACE FLAG 2312, when your DB Compatibility level set to Legacy Server i.e. SQL Server 2012,2008..

Lets first set the database SQL SERVER 2014 to the legacy compatibility level of 110 i.e. SQL Server 2008 and with TRACE FLAG we will see the new Query Plan.

Changing the COMPATIBLITY LEVEL OF SQL Server 2014 to 110 i.e. SQL Server 2012

use master

go

alter database AdventureWorks2012

set COMPATIBILITY_LEVEL = 110

go

Command(s) completed successfully.

Lets fire the query in both the environment one under  SQL Server 2012 compatibility level 110 and other on SQL Server 2014 Compatibility level 120 by enabling TRACE Flag to 2132

use AdventureWorks2012

go

-- With Legacy Cardinality Estimator 2012

select * from Person.Address

where StateProvinceID =79 and  City = 'Bothell'

-- With New Cardinality Estimator 2014

select * from Person.Address

where StateProvinceID =79 and  City = 'Bothell'

OPTION (QUERYTRACEON 2312);

Lets see both the query plan to the check the Estimated Row Count.

Query with Legacy Compatibility Level of 110 (SQL Server 2012)

Query with Legacy Compatibility Level of 110 (SQL Server 2012)

Now here you can see, in first legacy plan the Estimated No of Rows has 1 count whereas in the second New Cardinality Estimator Plan the Estimated No of Rows has 3 counts to 2312.

Blaze up your code and people will come from miles to see your code burning!

 


Post Reference: Vikram Aristocratic Elfin Share