SQL SERVER – How to Force New Cardinality Estimation or Old Cardinality Estimation

After reading my initial two blog posts on New Cardinality Estimation, I received quite a few questions. Once I receive this question, I felt I should have clarified it earlier few things when I started to write about cardinality. Before continuing this blog, if you have not read it before I suggest you read following two blog posts.

Q: Does new cardinality will improve performance of all of my queries?
A: Remember, there is no 0 or 1 logic when it is about estimation. The general assumption is that most of the queries will be benefited by new cardinality estimation introduced in SQL Server 2014. That is why the generic advice is to set the compatibility level of the database to 120, which is for SQL Server 2014.

Q: Is it possible that after changing cardinality estimation to new logic by setting the value to compatibility level to 120, I get degraded performance for few queries?
A: Yes, it is possible. However, the number of the queries where this impact should be very less.

Q: Can I still run my database in older compatibility level and force few queries to newer cardinality estimation logic? If yes, How?
A: Yes, you can do that. You will need to force your query with trace flag 2312 to use newer cardinality estimation logic.

USE AdventureWorks2014
GO
-- Old Cardinality Estimation
ALTER DATABASE AdventureWorks2014
SET COMPATIBILITY_LEVEL = 110
GO
-- Using New Cardinality Estimation
SELECT [AddressID],[AddressLine1],[City]
FROM [Person].[Address]
OPTION(QUERYTRACEON 2312);;
-- Using Old Cardinality Estimation
SELECT [AddressID],[AddressLine1],[City]
FROM [Person].[Address];
GO

Q: Can I run my database in newer compatibility level and force few queries to older cardinality estimation logic? If yes, How?
A: Yes, you can do that. You will need to force your query with trace flag 9481 to use newer cardinality estimation logic.

USE AdventureWorks2014
GO
-- NEW Cardinality Estimation
ALTER DATABASE AdventureWorks2014
SET COMPATIBILITY_LEVEL = 120
GO
-- Using New Cardinality Estimation
SELECT [AddressID],[AddressLine1],[City]
FROM [Person].[Address];
-- Using Old Cardinality Estimation
SELECT [AddressID],[AddressLine1],[City]
FROM [Person].[Address]
OPTION(QUERYTRACEON 9481);
GO

I guess, I have covered most of the questions so far I have received. If I have missed any questions, please send me again and I will include the same.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

One thought on “SQL SERVER – How to Force New Cardinality Estimation or Old Cardinality Estimation

  1. Pingback: SQL SERVER – How to Know Cardinality of Executed Query? | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s