Hi Friends,

In our previous blog we have seen how we can use new SQL Server 2014 cardinality Estimator for newly created databases under SQL Server 2014.Link for that blog is mentioned below;

Today we will see about how we can use New Cardinality Estimator for databases created under previous version of SQL Server. The logic is very simple here, from previous post we know that New Cardinality estimator can be used if database is having compatibility level 120. That means if you have migrated your database from previous version of SQL Server to SQL Server 2014 and you want to use New Cardinality Estimator then you have to change the compatibility level of that database to 120. Let me show you this thing practically.

I have created one new database under SQL Server 2012. I have restored the backup of this database to SQL server 2014 instance. As of now the compatibility level of this database is 110. You can check this by using below query:

1_SQL_Server_2014_Using_New_Cardinality_Estimator_for_databases_created_in_lower_version

Now from Query Plan you can see that as of now this database is using Old version of Cardinality Estimator. [Here it will not show you version details]

2_SQL_Server_2014_Using_New_Cardinality_Estimator_for_databases_created_in_lower_version

3_SQL_Server_2014_Using_New_Cardinality_Estimator_for_databases_created_in_lower_version

Now if you want to use new cardinality estimator then change the compatibility level of that database by using below query:

4_SQL_Server_2014_Using_New_Cardinality_Estimator_for_databases_created_in_lower_version

After changing compatibility level, now you can check which version of cardinality estimator is in use now:

Now run the above query along with actual execution plan. Right click on left most operators in plan and click on properties:

5_SQL_Server_2014_Using_New_Cardinality_Estimator_for_databases_created_in_lower_version

From above property window, it is clear that we can use new cardinality estimator for databases created in lower versions by restoring them to SQL Server 2014 and changing compatibility mode to 120.

HAPPY LEARNING!

 

Regards

Prince Rastogi

Like us on FaceBook Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook