SQL Server 2016改进了查询优化器

SQL Server 的前两个版本主要是通过提供新特性提高性能，而 SQL Server 2016 主要是改进本身已有的功能。

基数估计器

基数估计器是所有查询优化器的核心。它会查看被查询表的统计信息以及执行的操作，估计查询执行计划每一步的行数。有经验的 DBA 都知道，错误的基数估计会严重降低数据库的性能。可能导致的问题包括：

• 选择了错误的索引；
• 选择了错误的连接运算符（例如嵌套循环、哈希和合并）；
• 分配的内存过多，妨碍了其他查询；
• 分配的内存过少，导致过多数据溢出到 tempdb。

考虑到基数估计器如此重要，你可能会惊讶，SQL Server 2012 使用的基数估计器基本上与 1998 年 SQL Server 7 引入的基数估计器相同。仅仅是两年之前，我们才看到了“SQL Server 查询优化器基数估计过程的第一次大规模重新设计”。要想深入了解那个版本，可以阅读白皮书《使用SQL Server 2014 基数估计器优化查询计划》。

SQL Server 2016 就是以那项工作为基础构建的，当兼容性级别设置为 130 时，可以提供更准确的估计。不过，也可能出现退化，因此，在生产环境中更改兼容性级别之前，要对现有的数据库进行彻底地测试。

兼容性级别

你可能不熟悉这个术语，兼容性级别在 SQL Server 中有两个关键的作用。首先，可以确定哪些数据库特性可用。将数据库设置到一个比较低的兼容性级别上，就会无法使用一些较新的特性。通常，这不会带来升级问题，因为数据库的设计考虑了较老的特性集。

另外一件受兼容性级别控制的事情是使用哪个查询优化器和基数估计器。在经过精心调优的数据库中，为了降低性能退化的可能性，你可以选择一个较低的兼容性级别，强制 SQL Server 使用使用一个来自旧版本的查询优化器。

在某些情况下，你需要更细粒度的控制。例如，SQL Server 2016 允许你将兼容性级别设为 130，以使用所有的新特性，但仍然使用旧版本的基数估计器。这可以通过下面的命令设置：

ALTER DATABASE SCOPED CONFIGURATION
SET LEGACY_CARDINALITY_ESTIMATION = ON;
 

除了 SQL Server 2008 R2 之外，每个版本的兼容性级别都比上一个版本大10。所以SQL Server 2000 的兼容性级别为80，而SQL Server 2016 为130。每个SQL Server 版本都至少支持前面的两个版本。

多线程插入

在SQL Server 2016 之前，Insert-Select 语句只在选择阶段是多线程的，而实际的插入操作是序列化的。现在，插入操作也可以是“多线程的或者可以有并行计划”。

内存优化表

内存优化表也具备了多线程的能力。

统计

SQL Server 2016 在统计方面有两个变化。第一个是在使用大表时更新频率更高了。

以前，当发生变化的行数达到 20% 时才会触发统计信息的自动更新，这对于大表来说是不合适的。从 SQL Server 2016（兼容性级别 130）开始，该阈值会与表的行数关联起来——表的行数越多，触发统计信息更新的阈值就越低。注意，在以前的版本中，该行为由 Trace Flag 2371 控制。

例如，如果一个表有 10 亿行，在以前的行为模式下，只有当发生变化的行数达到 2 亿时才会启动自动统计更新。在 SQL Server 2016 中，只需要 100 万行就可以触发自动统计更新。

继续讨论并行化。现在，当使用兼容性级别 130 时，统计信息可以“由一个多线程的进程抽样收集”。

外键约束

关系型数据库的一大卖点是能够将一个表和其他的表关联，并使用外键约束确保数据一致性。但那有一些开销，因此，在 SQL Server 2014 及更早的版本中，表的外键约束上限为 253。

你可能会觉得这个数很大了，但在一个大型数据库中，当你开始考虑包含审计列时，如“CreatedByKey”，就很容易达到那个限制了。为了缓解这个问题，微软将传入外键约束的上限增加到了 10000。就是说，你可以有数千个表同时引用某一个用户表。但有一些注意事项。

首先，这不适用于传出外键约束，也不适用于自引用表。这些情况的外键上限仍然是 200 多个。

其次，被引用的表不能使用 MERGE 操作修改；只允许进行 DELETE 和 UPDATE 操作。（理论上，SELECT 和 INSERT 操作也是允许的，但文档中并没有提到它们，因为它们不受传入外键约束的影响。）

注：“关系型数据库管理系统”中的“关系”一词实际上并不是指表之间的关系。更确切地说，它是一个数据科学术语，是指行里的每个值和同一行里的其他所有值的关系。在数据透视表中，每个单元格都是一个独立的和或平均值，它是一个非关系型表的例子。

连接和外键约束

前面已经讲过，外键约束是有成本的。如果你修改一个被外键约束潜在引用的行，那么数据库就需要进行检查，以确保没有违反约束。

在 SQL Server 2014 中，执行检查时会连接每个引用上述表的表。不难想象，开销很快就会变得非常大。为了解决这个问题，SQL Server 引入了一个新的“参照完整性运算器（Referential Integrity Operator）”。

新的查询执行运算器会就地执行参照完整性检查，比较修改的行和引用表里的行，以验证修改是否会破坏参照完整性。这会极大地减少此类计划的编译时间及相应的执行时间。

查看英文原文： Query Optimizer Improvements in SQL Server 2016