SQL Server 2014 Cardinality Estimator for databases created in lower version

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:

Select compatibility_level from sys.databases where name='CETEST'

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]

SELECT TOP 1 * FROM [xtTest]

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

USE [master]
GO
ALTER DATABASE [CETEST] SET COMPATIBILITY_LEVEL = 120
GO
Select compatibility_level from sys.databases where name='CETEST'

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

USE [CETEST]
GO
SELECT TOP 1 * FROM [xtTest]

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

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 Twitter |  Follow me on FaceBook