Cardinality Estimation - IV CE -Running your Query under Legacy Cardinality Estimator Compatibility Level i.e Under Compatibility Level 110 in SQL Server 2014 with CL 120

Let see how we will be retrieving the legacy SQL Server 2008 compatibility level. First lets turn your SQL Server 2014 to 120 Compatibility level

Changing the COMPATIBLITY LEVEL OF SQL Server 2014 to 120i.e. the highest compatibility level of SQL Family.

alter database AdventureWorks2012

set COMPATIBILITY_LEVEL = 120

go

Command(s) completed successfully.

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

use AdventureWorks2012

go

-- With New Cardinality Estimator 2014

select * from Person.Address

where StateProvinceID =79 and  City = 'Bothell'

-- With Legacy Cardinality Estimator 2012

select * from Person.Address

where StateProvinceID =79 and  City = 'Bothell'

OPTION (QUERYTRACEON 9481);

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

Query with Compatibility Level of 120 (SQL Server 2014)

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.

Your code is warm, filled up sparks; bring into play to alleviate the world of business!!!

 

Post Reference: Vikram Aristocratic Elfin Share