SQL Server 2016改进了查询优化器

微软 SQL 查询优化

摘要

SQL Server的前两个版本主要是通过提供新特性提高性能，而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