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'

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]

SELECT TOP 1 * FROM [xtTest]

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:

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

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:

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:

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

Avatar

About Prince Rastogi

Prince Rastogi is working as Database Administrator at Elephant Insurance, Richmond. He is having more than 8 years of experience and worked in ERP Domain, Wealth Management Domain. Currently he is working in Insurance domain. In the starting of his career he was working on SQL Server, Internet Information Server and Visual Source Safe. He is post graduate in Computer Science. Prince is ITIL certified professional. Prince likes to explore technical things for Database World and Writing Blogs. He is Technical Editor and Blogger at SQLServerGeeks.com. He is a regular speaker at DataPlatformDay events in Delhi NCR. He has also presented some in depth sessions about SQL Server in SQL Server Conferences in Bangalore.

View all posts by Prince Rastogi →

Leave a Reply

Your email address will not be published. Required fields are marked *