SQL Server 2014 introduces a new cardinality estimation logic/algorithm that greatly improves the performance of queries. The cardinality estimation logic is responsible for predicting the number of rows in the query result, which the query optimizer uses to choose an optimal execution plan. In previous versions of SQL Server, this logic had not been updated for quite some time, but in SQL Server 2014, it has been redesigned to incorporate various assumptions and algorithms from OLTP and warehousing workloads.
Let’s take a quick example to see how cardinality estimation can improve the performance of a query. For this demonstration, we will be using the AdventureWorks database. However, it is important to note that even if you have SQL Server 2014, you need to set your database compatibility mode to 120 (which is for SQL Server 2014) in order to see the effect of the new cardinality estimates. If your server instance is of SQL Server 2014 but your database compatibility mode is set to 110 or any earlier version, you will not experience the performance improvements.
Now, let’s execute the following query in two different compatibility modes and compare their performance:
USE AdventureWorks2014
GO
-- NEW Cardinality Estimation
ALTER DATABASE AdventureWorks2014 SET COMPATIBILITY_LEVEL = 120
GO
EXEC [dbo].[uspGetManagerEmployees] 44
GO
-- Old Cardinality Estimation
ALTER DATABASE AdventureWorks2014 SET COMPATIBILITY_LEVEL = 110
GO
EXEC [dbo].[uspGetManagerEmployees] 44
GO
After executing the above query, we can compare the results using the “Statistics IO” command. In the case of compatibility level 120 (SQL Server 2014), we can see that there are only 6 logical reads from the “Person” table. However, in the case of compatibility level 110 (older version of SQL Server), there are 137 logical reads from the same table. This drastic reduction in logical reads significantly improves the performance of the query.
If we enable the execution plan, we can visually see the difference as well. The new cardinality estimation logic chooses a more efficient execution plan, resulting in improved query performance.
By upgrading to SQL Server 2014 and setting the database compatibility mode to 120, you can take advantage of the new cardinality estimation logic and greatly enhance the performance of your queries. This is just one example of how SQL Server continues to evolve and improve to meet the demands of modern data workloads.
For more in-depth information on this topic, you can refer to my latest Pluralsight Course.