The Cardinality Estimator is a SQL Server Query Processor component that is responsible for predicting the number of rows that the query will return. This estimation of the number of rows in addition to the SQL Server statistics will be used by the SQL Server Query Optimizer to create the optimal and the most accurate execution plan for your query that has the lowest processing cost to execute.
The Cardinality Estimation process is a mathematical algorithm that depends on SQL Server statistics to calculate the estimated number of rows, based on many assumptions such as the Uniformity that is used for unknown attributes distribution, the Independence that is used for attributes with independent relations, the Containment that is used for identical attributes and Inclusion that is used for attribute with constant comparison.
Microsoft SQL Server 2012 and earlier SQL Server versions work based on the old Cardinality Estimator that had its last update in 1998. After 15 years, Microsoft SQL Server 2014 comes with a re-designed Cardinality Estimator with new and major updates on the assumptions and algorithms that work better on both the modern data warehousing Online Analytical Processing OLAP and Online Transactional Processing OLTP workloads.. SQL Server depends on the database compatibility level to determine which Cardinality Estimator to be used. Using the compatibility levels 120 and 130 that works with the new Cardinality Estimator, better query plans will be generated, leading to better queries performance.
Take into consideration that upgrading the current SQL Server Engine to SQL Server 2014 or restoring a database from previous SQL Server versions will not change the compatibility level of the existing databases and will not enable the new Cardinality Estimator automatically for these databases. This helps in avoiding sudden query plans changes after the upgrade, and then it is up to you to ALTER the database with the 120 compatibility level after testing it well. Any new database under a SQL Server 2014 instance will be created with compatibility level 120. The compatibility level of the model, msdb and tempdb system databases will be changed to 120 when upgrading the SQL Server instance to SQL Server 2014, but the master database compatibility level will not be upgraded. The temporary tables will use the compatibility level of the database under which the query compiled, not the tempdb compatibility level.
In small number of some special cases, you need to go back to the old Cardinality Estimator, as the query performance may degrade with the new Cardinality Estimator due to the changes on that Cardinality Estimator, which is applicable in SQL Server 2014 and later. This problem is referred to as Plan Regressions. Plan regressions occur when two bad estimations performed, and only one of these bad estimations is corrected resulting with bad estimation, affecting the generated execution plan negatively. An ALTER DATABSE statement can be used to change the compatibility level of the affected database to a value previous to 120 or the LEGACY_CARDINALITY_ESTIMATION database scoped configuration option in SQL Server 2016, can be enabled, to go back to the legacy Cardinality Estimator as we will see later in this article.
In addition to the compatibility level and database scoped configuration options, there are two trace flags; Trace Flag 2312 and Trace Flag 9481, that can be used to decide which Cardinality Estimator will be used. Trace Flag 2312 can be used to force the new Cardinality Estimator usage and Trace Flag 9481 will force the old Cardinality Estimator usage, without looking to the compatibility level of the database. You can enable these trace flags at the session level or at the query level, where the option (QUERYTRACEON 2312) T-SQL Command can be used to enable the Trace Flag 2312 at the query level regardless of server, session or database level settings as we will see in the demo in this article. Enabling both trace flags at the same time, the two trace flags will cancel each other, and the Cardinality Estimator version will be decided depending on the database compatibility level.
SQL Server provides two new extended events in SQL Server 2014 that can be used to troubleshoot any problem with the Cardinality Estimator; the query_optimizer_estimate_cardinality event that occurs once the Cardinality is estimated by the SQL Server Query Optimizer and the query_optimizer_force_both_cardinality_estimation_behaviors event that will be raised if both Trace Flag 2312 and Trace Flag 9481 is enabled at the same time, which will cancel each other as described previously.
Let’s start our simple demo that will show how to change the Cardinality Estimator in different ways and how this will affect the estimated number of records. The first method that is used to change the Cardinality Estimator used is the ALTER DATABASE … SET COMPATIBILITY_LEVEL T-SQL Command, where compatibility level 120 and 130 will enable the usage of the new Cardinality Estimator and lower values will enable the old version of the Cardinality Estimator. Each time before running the simple SELECT statement we will clear the cache in our demo as follows:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
ALTER DATABASE SQLShackDemo SET COMPATIBILITY_LEVEL=110; GO DBCC FREEPROCCACHE; GO SELECT * FROM dbo.Employees WHERE EmpDEPID >=154037100 GO ALTER DATABASE SQLShackDemo SET COMPATIBILITY_LEVEL=130; GO DBCC FREEPROCCACHE; GO SELECT * FROM dbo.Employees WHERE EmpDEPID >=154037100 GO |
The generated execution plans using the APEXSQL PLAN application for the two queries will be the same, as we are using simple a SELECT query with no major difference between the old and new Cardinality Estimators.
If we click on the SELECT operator of the execution plan of the query that used the old Cardinality Estimator, the estimated number of rows will be as follows:
Clicking on the SELECT operator of the second execution plan that is using the new version of the Cardinality Estimator, the estimated number of rows will show us a small difference between the old and new Cardinality Estimators as below:
If you click F4 in the SQL Server Management Studio to show the Cardinality Estimator that is used to estimate the number of rows to generate each plan, where the first plan that is used the old version of the Cardinality Estimator will have value 70:
And the Cardinality Estimator of the second query that uses the new version will have the value 130 as below:
Another option to show the Cardinality Estimator that is used in each plan is to show the XML version of the execution plan for each query, and browse for the new attribute in SQL Server 2014 within StmtSimple called CardinalityEstimationModelVersion. From the APEXSQL PLAN application, browse the Plan XML tab as below:
Where the value shown in the first XML plan will be 70:
And the value shown in the second XML plan will be 130:
The second option that can be used to tune the Cardinality Estimator in SQL Server 2016 is the Database Scoped Configuration Options that can decide if you will use the legacy version of the Cardinality Estimator or use the new version of it at the database level as below:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
USE SQLShackDemo GO ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON; DBCC FREEPROCCACHE; GO SELECT * FROM dbo.Employees WHERE EmpDEPID >=154037100 GO ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF; DBCC FREEPROCCACHE; GO SELECT * FROM dbo.Employees WHERE EmpDEPID >=154037100 GO |
Clicking on the SELECT operator on the execution plans of both queries will show us the same result as follows:
The last option that can be used to force the old Cardinality Estimator at the query level is enabling the Trace Flag 9481 and forcing the new Cardinality Estimator by enabling the Trace Flag 2312 using the QUERYTRACEON T-SQL command as below:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
USE SQLShackDemo GO DBCC FREEPROCCACHE; GO SELECT * FROM dbo.Employees WHERE EmpDEPID >=154037100 OPTION(QUERYTRACEON 9481); GO DBCC FREEPROCCACHE; GO SELECT * FROM dbo.Employees WHERE EmpDEPID >=154037100 OPTION(QUERYTRACEON 2312 ); GO |
And again the estimated number of rows will be same as the previous methods:
Conclusion:
SQL Server 2014 comes with new redesigned and enhanced version of the Cardinality Estimator to meet the new requirements of the OLAP and OLTP workloads. SQL Server provides you with many ways to enable and disable the new version of the Cardinality Estimator if you find an issue with the new execution plan performance. It is better to test the new Cardinality Estimator on a test environment workload before enabling it on a production environment workload, in order to troubleshoot any issue you find and resolve it before moving forward the new Cardinality Estimator.
He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.
Also, he is contributing with his SQL tips in many blogs.
View all posts by Ahmad Yaseen
- Azure Data Factory Interview Questions and Answers - February 11, 2021
- How to monitor Azure Data Factory - January 15, 2021
- Using Source Control in Azure Data Factory - January 12, 2021