Cardinality estimation is an important concept in SQL Server that plays a crucial role in query optimization and performance. In this blog post, we will discuss some common questions related to cardinality estimation and how it can impact the performance of your queries.

Does new cardinality estimation improve performance of all queries?

It is important to note that there is no one-size-fits-all answer to this question. The new cardinality estimation introduced in SQL Server 2014 is designed to improve the performance of most queries. However, the actual impact on individual queries may vary. It is generally recommended to set the compatibility level of your database to 120, which corresponds to SQL Server 2014, to take advantage of the new cardinality estimation logic.

Can changing cardinality estimation degrade performance for some queries?

While the new cardinality estimation logic is expected to improve performance for most queries, there is a possibility that it may lead to degraded performance for a few queries. However, the number of queries affected by this degradation should be minimal. It is important to thoroughly test your queries after changing the cardinality estimation logic to ensure optimal performance.

Can I run my database in an older compatibility level and force some queries to use the new cardinality estimation logic?

Yes, you have the flexibility to run your database in an older compatibility level and still force specific queries to use the new cardinality estimation logic. To achieve this, you can use the trace flag 2312. By enabling this trace flag for a specific query, you can instruct SQL Server to use the new cardinality estimation logic for that query, even if the database is running in an older compatibility level.

Example:

USE AdventureWorks2014;

-- Old Cardinality Estimation
ALTER DATABASE AdventureWorks2014 SET COMPATIBILITY_LEVEL = 110;

-- Using New Cardinality Estimation
SELECT [AddressID], [AddressLine1], [City]
FROM [Person].[Address]
OPTION (QUERYTRACEON 2312);

Can I run my database in a newer compatibility level and force some queries to use the old cardinality estimation logic?

Similarly, you can run your database in a newer compatibility level and still force specific queries to use the old cardinality estimation logic. To achieve this, you can use the trace flag 9481. By enabling this trace flag for a specific query, you can instruct SQL Server to use the old cardinality estimation logic for that query, even if the database is running in a newer compatibility level.

Example:

USE AdventureWorks2014;

-- New Cardinality Estimation
ALTER DATABASE AdventureWorks2014 SET COMPATIBILITY_LEVEL = 120;

-- Using Old Cardinality Estimation
SELECT [AddressID], [AddressLine1], [City]
FROM [Person].[Address]
OPTION (QUERYTRACEON 9481);

By understanding the concept of cardinality estimation and its impact on query performance, you can make informed decisions when it comes to setting the compatibility level and choosing the appropriate cardinality estimation logic for your queries. It is recommended to thoroughly test and analyze the performance of your queries after making any changes to ensure optimal performance.

If you have any further questions or need clarification on any aspect of cardinality estimation, please feel free to reach out to me.