SQL SERVER – How to Force New Cardinality Estimation or Old Cardinality Estimation
- by Pinal Dave
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.
SQL SERVER – Simple Demo of New Cardinality Estimation Features of SQL Server 2014
SQL SERVER – Cardinality Estimation and Performance – SQL in Sixty Seconds #072
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)Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL
