SQL Server 2014基数估计器有哪些新增功能？

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.

基数估计器是一个SQL Server查询处理器组件，负责预测查询将返回的行数。 SQL Server查询优化器将使用该估计的行数（除SQL Server统计信息外）来为查询创建最佳且最准确的执行计划，以使其执行的处理成本最低。

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.

基数估算过程是一种数学算法，它基于许多假设，例如用于未知属性分布的Uniformity ，用于具有独立关系的属性的Independence ， 遏制其用于那些用于与常数比较属性相同的属性和夹杂物 。

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.

Microsoft SQL Server 2012和早期SQL Server版本基于旧的Cardinality Estimator（在1998年进行了最后一次更新）进行工作。15年后，Microsoft SQL Server 2014附带了重新设计的Cardinality Estimator，对假设和算法进行了新的和主要的更新。 SQL Server在现代数据仓库联机分析处理OLAP和联机事务处理OLTP工作负载上均能更好地工作。SQL Server取决于数据库兼容性级别来确定要使用的基数估计器。 使用与新的Cardinality Estimator兼容的兼容级别120和130，将生成更好的查询计划，从而提高查询性能。

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.

请注意，将当前SQL Server Engine升级到SQL Server 2014或从以前SQL Server版本还原数据库不会更改现有数据库的兼容性级别，也不会自动为这些数据库启用新的基数估算器。 这有助于避免升级后查询计划的突然更改，然后由您决定在对数据库进行良好测试后以120兼容级别更改数据库。 将在兼容级别120下创建SQL Server 2014实例下的任何新数据库。将SQL Server实例升级到SQL Server 2014时，模型数据库， msdb和tempdb系统数据库的兼容级别将更改为120，但是主数据库兼容级别将不会升级。 临时表将使用在其下编译查询的数据库的兼容性级别，而不是tempdb兼容性级别。

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.

在少数特殊情况下，您需要返回到旧的Cardinality Estimator，因为新的Cardinality Estimator的查询性能可能会由于该Cardinality Estimator的更改而降低，这适用于SQL Server 2014及更高版本。 此问题称为计划回归 。 当执行两个错误的估计时，计划将发生回归，并且仅纠正这些错误的估计之一，从而导致错误的估计，从而对生成的执行计划产生负面影响。 可以使用ALTER DATABSE语句将受影响的数据库的兼容性级别更改为120之前的值，或者可以启用SQL Server 2016中的LEGACY_CARDINALITY_ESTIMATION数据库范围的配置选项，以返回到旧的Cardinality Estimator，如下所示本文后面。

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.

除了兼容性级别和数据库范围的配置选项之外，还有两个跟踪标志；它们分别是： 跟踪标志2312和跟踪标志9481可以用来确定将使用哪个基数估计器。 跟踪标志2312可用于强制使用新的基数估计器，而跟踪标志9481将强制使用旧的基数估计器，而无需查看数据库的兼容性级别。 您可以在会话级别或查询级别启用这些跟踪标志，无论服务器，会话或数据库级别的设置如何，都可以使用选项（QUERYTRACEON 2312） T-SQL Command在查询级别启用跟踪标志2312。我们将在本文的演示中看到。 同时启用两个跟踪标记，这两个跟踪标记将互相取消，并且基数估计器版本将取决于数据库兼容性级别。

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.

SQL Server在SQL Server 2014中提供了两个新的扩展事件，可用于对基数估计器的任何问题进行故障排除。 SQL Server查询优化器估计基数后将发生的query_optimizer_estimate_cardinality事件和如果同时启用了跟踪标志2312和跟踪标志9481将引发的query_optimizer_force_both_cardinality_estimation_ behaviors事件将发生， 如上所述 ，这将相互取消。

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:

让我们开始一个简单的演示，该演示将演示如何以不同方式更改基数估算器，以及这将如何影响估计的记录数。 用来更改使用的基数估计器的第一种方法是ALTER DATABASE…SET COMPATIBILITY_LEVEL T-SQL命令，其中兼容性级别120和130将启用新基数估计器的使用，而较低的值将启用旧版本的基数估算器。 每次运行简单的SELECT语句之前，我们都会按如下所示清除演示中的缓存：

 
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.

使用APEXSQL PLAN应用程序为两个查询生成的执行计划将是相同的，因为我们使用的是简单的SELECT查询，新旧基数估计器之间没有重大差异。

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:

如果我们单击使用旧的基数估计器的查询的执行计划的SELECT运算符，则估计的行数如下：

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:

单击使用新版本的基数估计器的第二个执行计划的SELECT运算符，估计的行数将向我们显示新旧基数估计器之间的细微差别，如下所示：

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:

如果在SQL Server Management Studio中单击F4以显示基数估计器，该基数估计器用于估计要生成每个计划的行数，则使用旧版本的基数估计器的第一个计划的值将为70：

And the Cardinality Estimator of the second query that uses the new version will have the value 130 as below:

使用新版本的第二个查询的基数估计器将具有值130，如下所示：

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:

显示每个计划中使用的Cardinality Estimator的另一个选项是显示每个查询的执行计划的XML版本，并在SQL Server 2014的StmtSimple中的 CardinalityEstimationModelVersion中浏览新属性。 在APEXSQL PLAN应用程序中，浏览“ 计划XML”选项卡，如下所示：

Where the value shown in the first XML plan will be 70:

第一个XML计划中显示的值为70：

And the value shown in the second XML plan will be 130:

第二个XML计划中显示的值为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:

可以用来在SQL Server 2016中调整基数估计器的第二个选项是数据库范围配置选项，该选项可以决定是使用旧版本的基数估计器还是在数据库级别使用新版本的基数估计器，如下所示：

 
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:

在两个查询的执行计划上单击SELECT运算符，将向我们显示相同的结果，如下所示：

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:

可以在查询级别强制使用旧基数估计器的最后一个选项是启用跟踪标志9481，并通过使用QUERYTRACEON T-SQL命令启用跟踪标志2312来强制新基数估计器，如下所示：

 
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.

SQL Server 2014附带了经过重新设计和增强的Cardinality Estimator版本，可以满足OLAP和OLTP工作负载的新要求。 如果发现新的执行计划性能有问题，SQL Server为您提供了许多启用和禁用新版本的Cardinality Estimator的方法。 最好在生产环境工作负载上对新的Cardinality Estimator进行测试，然后再对其进行测试，以解决您发现的问题并解决该问题，然后再进行新的Cardinality Estimator。

翻译自: https://www.sqlshack.com/whats-new-sql-server-2014-cardinality-estimator/