SQL Server中数据库兼容级别的重要性说明

SQL Server中数据库兼容级别的重要性

SQL Server 2014之前，用户数据库的数据库兼容级别通常不是您必须关注的重要属性，至少从性能角度来看。与数据库文件级别（在将较低级别的数据库还原或附加到运行较新版本的SQL Server的实例时自动升级，并且永远不会返回到较低级别）不同时，可以更改数据库兼容级别使用简单的ALTER DATABASE SET COMPATIBILITY LEVEL = xxx命令到任何支持的级别。

您不会受到任何特定支持的数据库兼容级别的限制，您可以将兼容级别更改回您希望的任何支持级别。在许多情况下，大多数用户数据库在迁移到新版本的SQL Server后从未改变其兼容级别。除非您确实需要由最新数据库兼容级别启用的新功能，否则这通常不会导致任何问题。

对于SQL Server 2012及更早版本，数据库兼容级别主要用于控制是否启用了特定版本的SQL Server引入的新功能，以及是否禁用了不受支持的旧功能。数据库兼容性级别还用作维护与旧版SQL Server更好的向后应用程序兼容性的方法。如果您没有时间使用最新的兼容级别进行完全回归测试，则可以使用之前的兼容级别，直到您可以根据需要测试和修改应用程序。

表1显示了SQL Server的主要版本及其默认和支持的数据库兼容级别。

SQL Server版本数据库引擎版本默认兼容级别支持的兼容级别

SQL Server 2019 15 150 150,140,​​130,120,110,100

SQL Server 2017 14 140 140,130,120,110,100

SQL Server 2016 13 130 130,120,110,100

SQL Server 2014 12 120 120,110,100

SQL Server 2012 11 110 110,100,90

SQL Server 2008 R2 10.5 100 100,90,80

SQL Server 2008 10 100 100,90,80

SQL Server 2005 9 90 90,80

SQL Server 2000 8 80 80

表1：SQL Server版本和支持的兼容级别

新数据库创建

当您创建在SQL Server中新的用户数据库，数据库兼容级别将被设置为该版本的SQL Server的默认兼容级别。因此，例如，在SQL Server 2017中创建的新用户数据库的数据库兼容级别为140.例外情况是，如果您已将模型系统数据库的兼容级别更改为不同的受支持的数据库兼容级别，则新的用户数据库将从model数据库继承其数据库兼容级别。

数据库还原或附加

如果将在较旧版本的SQL Server上执行的完整数据库备份还原到运行较新版本的SQL Server的实例，则数据库兼容级别将保持与旧版SQL Server上的相同，除非旧数据库兼容级别低于较新版本SQL Server的最低支持数据库兼容级别。在这种情况下，数据库兼容级别将更改为较新版本的SQL Server支持的最低版本。

例如，如果要将SQL Server 2005数据库备份还原到SQL Server 2017实例，则已还原数据库的数据库兼容级别将更改为100.如果从旧版本分离数据库，则会出现相同的行为的SQL Server，然后将其附加到较新版本的SQL Server。

这种一般行为并不新鲜，但新的和重要的是当您将用户数据库更改为数据库兼容级别120或更高版本时会发生什么。这些可能对性能产生巨大影响的其他更改似乎并未在更广泛的SQL Server社区中广为人知。我仍然看到许多数据库专业人员和他们的组织正在做我所谓的“盲目升级”，他们从SQL Server 2012或更早版本到SQL Server 2014或更新版本（特别是SQL Server 2016和SQL Server 2017），他们不这样做任何严重的性能回归测试，以了解他们的工作负载在新的本机兼容级别上的行为以及可用的其他配置选项是否会产生积极影响。

数据库兼容级别120

这是在引入“新”基数估算器（CE）时。在许多情况下，使用新的基数估算器时，大多数查询运行得更快，但是使用新的基数估算器遇到一些主要性能回归的查询是相当常见的。使用数据库兼容级别120意味着您将使用“新”CE，除非您使用实例范围的跟踪标志或查询级别的查询提示来覆盖它。

Joe Sack撰写了经典白皮书“ 使用SQL Server 2014基数估算器优化您的查询计划 ”，该解释了2014年4月此更改的背景和行为。如果您看到使用新CE，SQL Server 2014的某些查询的性能回归没有那么多选择来缓解新CE引起的性能问题。Joe的白皮书非常详细地介绍了这些选项，但实际上，您只能使用实例级跟踪标志或查询级查询提示来控制查询优化器使用哪个基数估算器，除非您想要恢复到数据库兼容级别110或更低。

之所以我称它为引号中的“新”CE是因为现在没有单一的“新”CE。自SQL Server 2014以来，每个新版本的SQL Server都具有与数据库兼容级别相关的CE和查询优化器更改。与SQL Server 2016及更新版本相关的新的更准确的术语是兼容级别120的CE120，兼容级别130的CE130，兼容级别140的CE140和兼容级别150的CE150。

数据库兼容级别130

当您使用SQL Server 2016或更高版本时，使用数据库兼容级别130将默认使用CE130，并将启用许多其他与性能相关的更改。使用数据库兼容级别130启用全局跟踪标志1117,1118和2371的效果。对于在SQL Server 2016 RTM之前发布的所有查询优化器修补程序，还将获得全局跟踪标志4199的效果。

SQL Server 2016还引入了数据库作用域配置选项，使您可以使用ALTER DATABASE SCOPED CONFIGURATION命令控制以前在实例级别配置的某些行为。此讨论的两个最相关的数据库范围配置选项是LEGACY_CARDINALITY ESTIMATION和QUERY_OPTIMIZER_HOTFIXES。

无论数据库兼容级别设置如何，LEGACY_CARDINALITY ESTIMATION都会启用传统CE（CE70）。它等同于跟踪标志9481，但它只影响有问题的数据库，而不影响整个实例。它允许您将数据库兼容级别设置为130以获得其他功能和性能优势，但使用旧版CE数据库范围（除非被查询级查询提示覆盖）。

QUERY_OPTIMIZER_HOTFIXES选项等同于数据库级别的跟踪标志4199。当您使用130数据库兼容级别（不启用跟踪标志4199）时，SQL Server 2016将在 SQL Server 2016 RTM 之前启用所有查询优化器修补程序。如果您确实启用了TF 4199或启用了QUERY_OPTIMIZER_HOTFIXES，那么您还将获得SQL Server 2016 RTM 之后发布的所有查询优化器修补程序。

SQL Server 2016 SP1还引入了USE HINT查询提示，这些提示比您必须在SQL Server 2014及更早版本中使用的旧QUERYTRACEON查询提示更易于使用和理解。这使您可以更精细地控制与数据库兼容级别和正在使用的基数估算器版本相关的优化程序行为。您可以查询sys.dm_exec_valid_use_hints以获取正在运行的SQL Server的确切构建的有效USE HINT名称列表。

数据库兼容级别140

当您使用SQL Server 2017或更高版本时，使用数据库兼容级别140将默认使用CE140。您还可以从130获得所有其他与性能相关的更改，以及此处详述的新更改。SQL Server 2017引入了新的自适应查询处理功能，并且在使用数据库兼容级别140时默认启用它们。这些功能包括批处理模式内存授予反馈，批处理模式自适应连接和交叉执行。

数据库兼容级别150

当您使用SQL Server 2019或更高版本时，使用数据库兼容级别150将默认使用CE150。您还可以获得130和140之间的所有其他与性能相关的更改，以及此处详述的新更改。SQL Server 2019正在添加更多性能改进和行为更改，这些更改在数据库使用兼容模式150时默认启用。一个主要示例是标量UDF内联，它自动内联用户数据库中的许多标量UDF函数。这可能是某些工作负载最重要的性能改进之一。

另一个例子是智能查询处理功能，它是SQL Server 2017中自适应查询处理功能的超集。新功能包括表变量延迟编译，近似查询处理和rowstore上的批处理模式。

还有16个新的数据库作用域配置选项（从CTP 2.2开始），它们为您提供了更多项目的数据库级控制，这些项目也受到跟踪标志或数据库兼容级别的影响。它为您提供了对这些更高级别更改的更细粒度控制，这些更改默认情况下启用，数据库兼容级别为150。

结论

迁移到现代版本的SQL Server（意味着SQL Server 2016或更高版本）比使用旧版SQL Server复杂得多。由于与各种数据库兼容性级别和各种基数估计器版本相关的更改，将一些思考，规划和实际测试放入要在新版本的SQL Server上使用的数据库兼容级别实际上非常重要正在将现有数据库迁移到。

Microsoft 建议的升级过程是升级到最新的SQL Server版本，但保持源数据库兼容级别。然后，在每个数据库上启用Query Store并收集工作负载上的基准数据。接下来，将数据库兼容级别设置为最新版本，然后使用“查询存储”通过强制执行上次已知的良好计划来修复性能回归。

您真的希望避免随意的“盲目”迁移，在这种情况下，您幸福地意识到这是如何工作的以及您的工作负载将如何对这些变化做出反应。将数据库兼容性级别更改为适当的版本并使用适当的数据库范围配置选项以及绝对必要的适当查询提示对于SQL Server的现代版本非常重要。

另一件需要考虑的事情（特别是对于ISV）是微软开始真正推动你应该考虑将数据库和应用程序测试和认证到特定数据库兼容级别而不是特定版本的SQL Server。当新的SQL Server版本（目标）在与之前的SQL Server版本（源）运行的硬件相当的硬件上运行时，Microsoft提供查询计划形状保护，并且在目标SQL Server上使用相同的受支持的数据库兼容级别和源SQL Server。

这里的想法是，一旦您在特定的数据库兼容级别（如130）上测试和认证了应用程序，如果将该数据库移动到较新版本的SQL Server（例如SQL Server 2017），您将获得相同的行为和性能或者SQL Server 2019）只要您使用相同的数据库兼容级别并且您在相同的硬件上运行。

作者：格伦贝瑞     发表于：2019年1月14日下午2:55