SQL Server 2022新功能概览

阅读原文时间：2023年08月24日阅读：4

开始之前

本篇文章仅仅是针对SQL Server 2022新推出功能的概览，以及我个人作为用户视角对于每个功能的理解，有些功能会结合一些我的经验进行描述，实际上，SQL Server 2022在引擎层面的增强的确算是里程碑级别，涉及到的每一个功能点展开都可以单独开出一篇文章。但本篇文章只是一个概览性文章，并不会深入解释每个功能。

本篇文章侧重于讨论SQL Server 2022引擎本身，与Azure的整合以及对S3 Blob的整合、On Linux与K8S的部署层面增强不在本文的讨论范围内。

测试环境

本篇文章会对一些新功能进行测试，但不会进行深入测试，仅做初步的验证，因此环境以简单为主，用完即释放。

阿里云RDS for SQL Server 2022企业集群版

mssql.x4.medium.e2（2c8g）

示例数据库：[https://github.com/microsoft/sql-server-samples/tree/master/samples/databases/wide-world-importers](https://github.com/microsoft/sql-server-samples/tree/master/samples/databases/wide-world-importers)

Built-In Query Intelligence

SQL Server 2022引入的部分功能是能够立竿见影地提升性能的，也是带来最大提升的部分。对于大多数产品来说，要想提升产品效果就需要改变使用行为，这通常会带来较高的成本。相比之下，单纯提升产品本身的能力而不改变使用方式是增益最大的。

举个例子，在手机电池电量不变的情况下，改变使用习惯，例如手动经常关闭不使用的功能节省电量，与操作系统定期根据特定规则自动关闭功能节省电量相比，前者的成本会远远大于后者。

对于数据库也是类似的情况。很多新功能可能需要应用程序代码的变更，这会带来较高的使用成本。相比之下，如果在内核层面进行增强，仅需设置部分功能的开关，这部分的使用成本就会比较低。

Build-In Query Intelligence的整个一个大图如下，可以看到Query Store是实现这一些基础的核心，那什么是Query Store？

过去DBA有一部分比较重要的工作就是性能调优，而性能调优中有一部分比较重要的工作就是“建立Baseline”,打个比方，建立Baseline能够就好比一个人定期去体检，健康状态下的指标就类似于Baseline，也就是一个人正常状态下的指标，当人感觉不舒服时，使用检查的数据比对Baseline可以快速缩小病因范围，类似的，当系统出现瓶颈时，通过当前指标与Baseline比较能够帮助快速缩小排查范围。

另一部分性能调优的工作是捕捉“慢SQL”，通过定期捕捉高资源消耗的语句，人为针对这些语句进行调优。

SQL Server虽然内置了不少DMV记录统计信息，但这部分信息基于内存，重启后就消失，同时只有聚合数据，没有细分数据。

上述两部分都需要进行数据收集，这个动作通常需要有一定SQL Server基础，同时也需要额外部署监控收集-数据处理-展示等多个组件。

传统的方式是通过外挂收集器进行，例如我之前提到的阿里云RDS for SQL Server性能洞察体系。

而SQL Server自从2016以来引入了Query Store，能够将已执行SQL性能的各类元数据随数据库持久化到磁盘，启用方式仅为一个选项。这些元数据包括：

执行计划多版本信息
执行计划统计信息（IO、CPU使用等）
等待类型信息

例如，我们通过Query Store可以按照CPU 对Query进行排序，每分钟做一次汇总，比如下图中该SQL只对应1条执行计划，1分钟执行379次，CPU时间等相关信息。

那么有了Query Store，过去DBA的调优方式简化版本：

发现慢
人为介入，找到慢SQL
调优慢SQL
上线，观察性能，如果不达预计重复调优步骤

Query Store演进了好几个版本，到了SQL Server 2022作为默认启用的选项，默认启用意味着微软需要为该功能引起的副作用负责，因此在我看来该功能已经进入到非常成熟的阶段。

因此Query Store作为下面谈到的几个优化器功能提供决策数据。

一个SQL从语法解析到最终生成的执行计划会在优化器经历一系列过程，一个SQL解析出的执行计划质量高低通常和SQL性能有直接关系，而解析的过程需要参考各类数据，这些数据的准确性对执行计划的至关重要，就好比你希望去逛商场，那么从家到商场如何最快取决于路上是否堵车，如果堵车则地铁会比驾驶汽车更快，反之则地铁更快，“路上的交通状况”这个元数据对做出更好的“执行路径”就非常重要。

一个SQL解析为执行计划也要参考很多元数据，例如统计信息、是否有索引、过滤后的预估行数等等。而Cardinality Estimation指的是SQL在访问表活对象根据过滤或Join等操作后，预估的行数，该行数的准确性直接影响执行计划的质量。

SQL Server从7.0 到2014之前（兼容级别低于2014）评估是基于这样一个假设，数据之间没有关联，例如where a =1 and b=2 的预估行数= a的选择性*b的选择性*总行数。

SQL Server 2014以上（兼容级别>=2014）的预设的场景是数据有较多关联，而同一个表多个条件之间预估行数应该更多，具体算法见链接。

这两者之间适应不同负载类型，旧模型更适合关联度低简单的负载类型，新模型适合更复杂的查询（微软称之为“modern workload”），之前可以通过兼容级别全局控制也可以通过hint单独控制，无论哪种方式都不完美。

2022提供的机制是通过历史查询提供反馈，查询优化器和Query Store之间联动，编译SQL找出不同CE成本差异比较大的语句，并尝试附加CE Hint，并根据Query Store的结果纠正这一机制（附加Hint提升不明显甚至性能下降，类似下图，摘自Bob ward）。

这意味着CE模型可以针对不同Query进行Per Query级别的适配，这部分工作已经是一个高级DBA的工作了，也就是根据多次历史记录做出优化决策，启用该功能无需应用程序适配，只需要满足下述条件：

启用Query Store
兼容级别160

SQL Server在执行Query中，一些操作会明显依赖内存，比如Sort或Hash Join，执行计划中会预设对内存的使用量提前申请，这里就会遇到两个问题：

内存申请过多：浪费内存是一部分副作用，分配内存的等待也会导致Query需要更长的时间（尤其是高并发Query）。
内存申请过少：那么查询所需的内存不够，就需要TempDB补齐，TempDB位于IO子系统，内存速度和IO速度通常不在同一个量级，导致查询会变慢非常多，SQL Server中称之为“spill to tempdb”。

内存授予的多少取决于执行计划，比如Sort 1万数据和10万数据所需的内存肯定不同，但执行计划很多时候估计并不精准，导致内存的不准确

SQL Server 2022提供了基于Query Store的内存历史记录，根据一个Query历史执行所需的实际内存，决定最新的查询的内存授予，但这也是基于阈值，只有内存授予差异极大的场景下才会进行内存授予的调整。

启用条件：

兼容级别140（2017）以上

前面提到，SQL Server中Query->执行计划的过程需要依赖的元数据依赖于成本预估，成本预估的一个先决条件就是参数本身。例如where a =1 返回1万行和where a=2 返回1行，执行计划通常不一样。比如下图参数1对应的就是1次Seek，参数2对应就是一次Scan：

另外，SQL Server与Oracle这类商业数据库牛逼的点是处理复杂SQL的能力（某种程度上是优点也是缺点，强大的SQL引擎导致滥用，相比mysql pg来说，一般使用的SQL会简单很多，更适应今天DDD的设计理念和微服务的部署方式，当然这是另外的话题），我见过最大的执行计划是一个1万多行的存储过程生成，执行计划本身80MB。

这类执行计划编译本身的成本就很高，不仅消耗CPU还会增加语句执行时间，当类似的语句并发出现，还可能导致系统层面的编译瓶颈，一般有等待类型和性能计数器展示这一点，比如在阿里云控制台截取的几个相关性能计数器：

因此将已经编译的执行计划缓存起来就是一个更好的选择。此时又会面临另外一个问题，已经缓存的执行计划不准怎么办？使用缓存的执行计划成本远高于重新编译怎么办？在此之前这个问题一直是给DBA同仁们留了一口饭吃，了解这个概念和不了解这个概念也是是否入门的分水岭:-)

传统的手段也是各显神通，从SQL上就是对参数写法的优化、定期更新统计信息，针对语句加recompile hint，调整索引，拆分SQL，高级一点的主动监测高消耗语句定点对单个缓存进行清理，当然还有更low的，直接重启（重启后所有执行计划缓存不存在，因此全部重编译）。

SQL Server 2022开始引入的PSP，用来解决这个问题，由于Query Store有能力对一个SQL缓存多个执行计划，因此根据参数选择更适合的执行计划就是很牛逼，大概原理就是：

在Query Hash -> plan cache hash中间增加dispather，根据dispather决定使用什么缓存的执行计划。该功能对于较多复杂查询的略大数据库绝对是大杀器级别，虽然目前我还没有机会看到一些案例，但根据经验这个功能会极大减少运维门槛。

启用条件：

兼容级别160（SQL Server 2022）

SQL Server的一个指标是“最大并行度”，另外是“并行开销阈值”，前者指的是是一个Query最大可以使用多少的Core并发执行，后者是执行成本达到某个值就会走并行。

这两个值是90年代的产物，默认值分别为0和5，0指的是SQL Server自行控制使用多少Core并行执行，基本等于当前机器所拥有的物理核数，在现代机器上，由于多个Socket和Numa的存在，这种配置极为不合理，对于典型的OLTP系统我们都默认调整为2或4，当然部分OLAP类语句也会单独做一些设置。

“并行开销阈值”默认为5也是极低的值，今天的硬件能力已经完全不一致，过去多核CPU完成的任务，今天CPU单核甚至更快，因为并行存在较高Core的协同成本，如果太低会导致协同成本甚至高于Query执行成本。

这两个值是实例级别，虽然后续引入到了库级别，但Scope还是过大，可能类AP语句需要DOP更大，而TP类甚至设置为1更合理。

DOP Feedback和上面提到的其他Feedback类似，都是基于Query Store，根据历史收集的反馈经验，Query Store后台任务会根据一系列因素判断查询是否适合使用DOP反馈，包括查询执行次数、持续时间、并行效率等，根据内置的规则针对语句动态调整DOP：

启用条件：

ALTER DATABASE SCOPED CONFIGURATION SET DOP_FEEDBACK = ON

Build-In Query Intelligence让SQL Server在优化器做决策时，能够根据历史数据做出更好的调整，这在自适应能力上已经到达新的高度。在我看来，未来数据库也应该向这个方向走，数据库作为底层平台系统，绝大多数场景更应该让使用者更关注业务，而不是底层数据使用的复杂性。

上面提供到的所有功能都能让系统在不做任何代码变更的情况下，大幅提升性能和稳定性，因此如果有条件，建议去升级2022，并启用Query Store。

如果对自适应数据库的一些概念感兴趣，可以读一下Andy Pavlo 大神的一篇旧闻What is a Self-Driving Database Management System?。

数据库引擎层面

账本数据库是SQL Server 2019引入的，在2022做了部分增强，利用区块链做防篡改和0信任相关的分布式账本数据库。这类功能和SSL和TDE一样，在国内都属于弱需求，对于业务方来说都属于“我不需要，但监管等第三方让我必须要”，这里就不多赘述了。

该功能又是一劳永逸的解决TempDB元数据的问题。

每次做DDL时，例如创建表、删除表等需要修改数据库标记内部分配使用的元数据，SQL Server内叫PFS页，通常情况这个点不应该成为瓶颈，但是临时表和表变量就是特殊的DDL语句，如果高频高并发创建临时表和表变量，就是需要高频修改PFS页，PFS页修改本身通过“悲观并发控制”，也就是通过数据库内一种保护内存结构特殊的锁，学名Latch Lock，这本身会成为数据库的一种瓶颈。

多年来作为SQL Server DBA一个常识就是创建实例根据机器核数多少，TempDB至少4个文件起步，多个文件意味着多个PFS页，减少锁阻塞发生的概率。

SQL Server 2022通过“乐观并发控制”更新PFS页，基本就能解决争抢问题，而仅对少数系统页做乐观并发成本也可控。这个提升还是为了减少运维

该选项需要手动启动：

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON

Buffer Pool是基于内存结构，内存的随机查找成本很低，更适用于Hash结构，因此随机查找通常不是问题，但对于大规格实例的扫描通常成本高昂，值得大规格通常是256GB以及以上内存。当内存>64GB时，过去单线程的Buffer Pool扫描变为多线程，会提升扫描性能。

受影响的操作包括：

数据库启动
数据库关闭或重启
AG 故障转移
删除数据库 (删除)
从数据库中删除文件
完整或差异数据库备份
数据库还原
事务日志还原
联机还原

这个功能从我的体验，针对256G以上内存的实例有提升，尤其是AlwaysOn发生故障转移时会快很多，其他场景并没有太明显的感觉，所以适用面有限。

HA/DR

Contained Availability Groups

这个功能主要是允许AlwaysOn 集群包含Contained Database，也就是过去实例级的对象比如作业、Login、链接服务器对象能够随着数据库迁移，或随着HA切换保证一致性。

这个功能我想是更进一步降低运维成本，毕竟发生HA之后，如果作业或者Login丢了，基本等同于故障，而Contained到数据库中由内核保证一致性会稳定很多。

AlwaysOn 恢复线程拥有更高死锁优先级

原文描述：The database recovery task is now run with a higher deadlock priority to avoid being chosen as a deadlock victim with user transactions.

也就是说用于recovery的系统线程Session死锁优先级为“高”。

这个事情虽然概率低，但我不幸遇到过，微软早就应该这么设计了，我曾经遇到过AlwaysOn切换之后，用户新连接将Recovery线程作为死锁kill掉，导致数据库一直沉迷在Recovery状态，重启后才解决，直接导致了不可用时间。

AlwaysOn其他增强

原文描述：

We fixed a problem where a replica database would get stuck in a recovery pending state.

Ensured data movement is not paused to replicas due to internal log block errors.

Eliminated schema lock contention problems on secondary replicas

想起那个句话，懂得都懂，上面3条我更不幸全遇到过，最坏的结果就是重搭AlwaysOn，一次几T数据，简直比吃了老谭酸菜面还酸爽。当前提升还没有机会验证，有机会验证后补充一下实际效果。

Accelerated Database Recovery

关系数据库一个很重要特点就是“ACID”其中，D指的是持久性，保证持久性一个很重要的点是“崩溃一致性”的恢复。比如数据库在断电、进程崩溃的情况下，数据库中数据一致性不应该受到影响。

而实现这一点就是在下次数据库启动时，进行Recovery动作，也就是事务未提交，但已落盘的数据进行rollback，事务已提交，但在内存中未落盘的脏页进行redo，整个过程也就是所谓的“undo/redo recovery”。

但recovery过程的长短取决于进程崩溃、重启、关机时最早的活动事务，我见过recovery 8个小时的过程，用户在半夜做了大量的ETL操作，存在几百G的活动日志（Active transaction log），后重启实例，需要完整扫描几百G的日志并做Recovery。直接导致8个小时的不可用时间（哎，差点背锅，不堪回首:-( ）。

微软在之前版本为了解决慢的问题，做过并行Recovery，但根据我的经验效果比较一般，与此同时，还出现过并发Recovery进程之间死锁（这个真背过锅，越想越气-.-）。

ADR是2019引入，2022做了很多机制上的增强，的基本原理是通过多版本并发控制，因此弊端类似事务快照隔离，需要更多的存储空间和额外的CPU和内存使用。

因此带来的收益：

大事务不会导致日志增长失控
降低大事务回滚导致的数据库不可用
解决非常久的Recovery时间问题

https://cloudblogs.microsoft.com/sqlserver/2023/03/28/accelerated-database-recovery-enhancements-in-sql-server-2022/ 微软官方博客的验证数据是49秒和4秒的Recovery时间区别，提升还是很巨大的。

总结一下，Recovery时间可以从过去最早的事务日志，到现在最近一个Checkpoint（一般60S），将会极大降低恢复时间，与此同时由于数据快照存在，日志可以更激进的truncate，还避免了磁盘空间问题，因此，建议对于几百G以上，负载较高，同时经常有长事务的数据库，启用该功能能够提升可用性。

在阿里云RDS控制台直接修改该数据库的属性。

Parallel Redo Enhancements

Parallel Redo主要是AlwaysOn使用，但效果有限，有时候我们甚至需要关掉，2022的优化是将Worker限制100挪掉，对于数据库数量较多的实例会有帮助，但不知道之前并行Redo的一些问题是否得到修复。

T-SQL 增强

JSON基本已经是当今绝大多数数据交换的事实标准了，SQL Server 2016开始在T-SQL支持JSON，2022新增了JSON_ARRAY和IS_JSON函数，看名称基本知道做什么的，细节可以看微软文档。

一般来讲，今天的应用程序在JSON的处理已经非常成熟，拥有成熟的类库和方案，在数据库侧做JSON的解析通常得不偿失，所以这里不再赘述

DATE_BUCKET
GENERATE_SERIES
FIRST_VALUE 和 LAST_VALUE

都是一些和时间相关的函数，具体看微软官网

DATETRUNC
STRING_SPLIT
IS [NOT] DISTINCT FROM

针对数据库内置提供SQL方言的额外能力，我一直持保守态度，数据库因为自身的特点，Scale-Out成本是非常高的，而应用因为无状态更容易Scale-Out，同时应用语言做逻辑处理能力更加强大，因此个人通常更倾向于在数据库外侧完成工作，把数据存取以外的计算能力外移到应用。

安全性

这个功能本身用于端到端加密，应用程序侧掌握加密Key，整个链路数据都是加密的。SQL Server中无法查看加密后的数据，我个人觉得可能是用于解决客户和云厂商的信任问题，云PaaS侧即使负责运维数据库，也无法查看数据库的内容。

2022中做了一些提升，将应用的Key放到SQL Server的安全区域，以便一些字符串操作生效，例如Like操作，这里不再赘述。

主要是支持TLS1.3支持链路加密，这类功能主要满足合规需求，不再赘述。

例如##MS_DefinitionReader##、##MS_ServerStateReader##、##MS_ServerPerformanceStateReader##，主打一个能看系统的各类性能数据，不能看业务数据，或许是方便一些第三方的运维人员使用，又担心泄漏数据，在我看来这类功能在国内比较鸡肋。