[译文] 通过 SQL Server 2022 中的系统页面锁存器并发增强功能提高可扩展性

原创通讯员 2022-07-22

501

在过去的几个 SQL Server 版本中，Microsoft 改进了tempdb数据库的并发性和性能。在SQL Server 2022中，我们通过引入并发全局分配映射 (GAM) 和共享全局分配映射 (SGAM) 更新来解决最后一个争用领域之一，这将使 SQL Server 2022 的可伸缩性有了很大改进，因为tempdb可以说是最重要的环境中的数据库。

Tempdb性能挑战

从历史上看，tempdb一直是 SQL Server 中常见的痛点之一。为什么这是一个痛点？嗯，使用是关键原因之一。通过使用，我们指的是创建临时表和其他用户对象，但是当没有足够的内存可用于进程或存在不准确的估计导致 SQL Server 溢出到 tempdb 时， tempdb也用于内部溢出到磁盘.

什么是tempdb数据库？

Tempdb是一个特殊用途的系统数据库，但其结构本质上与任何其他用户数据库一样。顾名思义，tempdb数据库是为临时存储而设计的，这意味着写入tempdb的任何内容都不会被持久化。
重要的是要知道，虽然 SQL Server 几乎对每个 SQL Server 工作负载都使用tempdb数据库，但每个 SQL Server 实例只有一个tempdb ，并且每次重新启动 SQL Server 时都会重新创建tempdb 。

临时数据库工作负载

tempdb与其他数据库的主要区别在于工作负载。使用tempdb，我们不断地创建和销毁诸如临时表之类的对象。在繁重的 OLTP 环境中尤其如此，您可能有许多线程在执行各种工作，并且如果您已经看到系统上的资源争用，则影响将被放大。

那么，什么存储在tempdb中？

当然，临时表和表变量会进入tempdb——这通常是我们考虑的第一个对象类型。因此，每当您在存储过程或常规批处理中创建临时表时，都会转到tempdb。

行版本进入tempdb，如果您使用快照隔离，读取提交的快照，每次特定事务修改行时，数据库引擎将在tempdb中存储该行先前提交的图像的版本。

散列操作将溢出到tempdb。工作表还用于假脱机、游标、排序和临时大对象 (LOB) 存储——这些都将用于tempdb。

触发器使用 tempdb 中的行版本存储——这将转到tempdb。

在线索引操作——如果您使用 ONLINE ON 关键字维护索引，那么我们将在tempdb中创建临时卷影副本。

DBCC CHECKDB 还在tempdb中创建卷影副本

如您所见，tempdb 有很多内容——tempdb将用于用户对象，例如全局或本地临时表和索引、存储过程、表变量、表值函数和游标。但我们也将tempdb用于内部场景，例如溢出到磁盘以及作为存储假脱机和排序中间结果的工作表。

是什么导致tempdb中的争用？

由于tempdb用于许多不同的场景，每个 SQL Server 实例只有一个tempdb数据库，并且我们已经开始向具有更大工作负载的更大机器推进，我们已经开始看到tempdb空间在三个关键领域出现并发问题：

对象分配争用
元数据争用
临时表缓存争用

什么是对象分配争用？

同样，tempdb的结构与任何其他数据库一样，但请记住- tempdb中的工作负载不同，因此对象分配争用更重要，因为对象的不断创建和销毁。

在遇到对象分配争用的服务器上，您可能会注意到严重的阻塞，尤其是在服务器负载过重时。因此，SQL Server 工作负载会变慢，但服务器的 CPU 可能似乎没有得到充分利用。这是因为争用存在于系统元数据中。为了帮助避免这些争用领域， SQL Server 支持团队推荐了一些长期存在的最佳实践。

对于tempdb，关键的最佳实践之一始终是以相同的大小和相同的增长率创建多个主数据文件 (mdf)。这样做的原因是通过跨多个分区分布tempdb活动来帮助缓解对象分配争用。

SQL Server 数据库必须有一个默认使用 .mdf 文件扩展名的主数据文件和一个使用 .ldf 文件扩展名的日志数据文件。主tempdb数据文件tempdb.mdf具有跟踪对象在 SQL Server 中的分配方式的关键页面。如下图所示，tempdb.mdf标题下的表格代表页面。

页 0 是页眉页，对于 SQL Server 中的任何主要或辅助数据文件都是如此。

页 1 是所谓的页可用空间 (PFS) 页，在 SQL 需要为对象分配空间时使用。基本上，PFS 页面包含关于数据库中接下来的 8088 个页面的页面已满程度的信息。如果 SQL Server 需要添加一些数据，SQL Server 会使用 PFS 页面查看关联对象的填充程度，以查看数据可以放在哪里。

在 8088 页之后，同一数据文件中还有另一个 PFS 页面——它会重复自己。因此，您将拥有多个 PFS 页面，具体取决于文件的大小。

第 2 页始终是全局分配映射 (GAM)，当 SQL Server 需要分配一个统一的扩展区时，这是 GAM 跟踪的扩展区分配。

SQL Server 中的一个区段由 8 x 8KB 页面组成，即 64KB，这通常是数据分配的单位，因此如果您有一个大于 8 个页面的表，那么每当我们为该表分配空间时，我们都会创建一个完整的区段，这是一个统一的范围，因为该范围中的所有八个页面都属于该对象。

因此，任何时候 SQL 需要为对象分配统一范围时，SQL 都会转到 GAM 页面并检查可用性。GAM 是一个位图，因此如果该位为 1，则该范围可用于分配，如果为 0，则该范围不可分配。一旦 SQL Server 分配了范围，它就会将该 GAM 页面的位从 1 翻转为 0，以表明它不再可用。

文件越大，您拥有的 GAM 页面就越多，对于 GAM，您将在 63,904 个扩展区之后获得另一个 GAM 页面。

第 3 页用于共享全局分配映射 (SGAM)，如果 SQL 需要在混合范围上分配空间，则使用此页面。当一个范围被多个对象使用时，此 SGAM 页面会跟踪混合范围的使用情况。因此，如果我有一个少于八页的对象并且我不想分配完整的范围，我们将使用混合范围。默认情况下，当创建一个全新的对象时，前八页将分配在混合范围上。

SGAM 是一个位图，所以如果该位为 1，那么它被用作混合区并且有空间可分配，那么我们将寻找相应的 PFS 页面以找到该区中的空页面，然后我们会分配该页面。这里重要的一点是 SGAM 与 PFS 页面结合使用，以便在混合范围上分配空间，并且在大约 64,000 个范围之后，您会在同一文件上获得另一个 SGAM。

当您在tempdb中有多个文件时，您会得到另一个直接标头 PFS、GAM 和 SGAM，因为所有文件都将具有相同的结构，并且对于多个文件，我们尝试通过这些文件共享工作负载。

SQL Server 根据比例填充算法将对象分配分布在同一文件组中的文件之间。我们尝试在文件组中的每个文件中保持相同百分比的可用空间，因此如果文件组中的所有文件以相同的大小开始并且它们保持相同的大小并且它们具有相同的可用空间量，那么我们将其转为将比例填充算法转换为循环算法——每个后续分配都会命中下一个文件，依此类推——这就是我们建议使用相同大小的多个文件的原因——在所有文件中分布对象分配可以让你绕过这个对象分配瓶颈。该建议在 SQL Server 2000 中提出，在 SQL Server 2019 中仍然适用。

多个相同大小的文件是我们的最佳实践，这将一直持续到测试证明并非如此。

跟踪对象分配争用

在 SQL Server 2019 之前，最好的方法是监视sys.dm_os_waiting_tasks动态管理视图并随时间记录争用历史记录。

考虑下面列出的 SQL Server 语句：

查看等待资源时，您可以知道第一个数字是指数据库，第二个数字是文件 id，最后一个数字是页面类型，因此可以监控争用。

这意味着等待资源 2:7:2 上的争用是tempdb争用，因为tempdb数据库始终是数据库 id 2，文件 id #7 上存在与 GAM 争用的争用，如图所示（第 1 页是 PFS，第 2 页是GAM，#3 是 SGAM）。

这些等待资源引用通常采用 2:1:1、2:1:3 等格式。

在数据库 id 2 上找到的任何结果都表明存在等待tempdb资源的请求，这些请求的累积可以帮助数据库管理员缩小争用的根本原因。

在 SQL Server 2019 中，我们创建了新函数来改进tempdb故障排除。sys.fn_PageResCracker动态管理函数返回给定 page_resource 值的 db_id、file_id 和 page_id，而sys.dm_db_page_info动态管理函数返回页眉中存在的 page_id、file_id、index_id、object_id 等页面信息。此信息对于对各种性能（锁定和闩锁争用）和损坏问题进行故障排除和调试很有用。

下面的示例查询可用于更好地解析 SQL Server 2019 之后的任何 SQL Server 版本的等待资源信息：

什么是元数据争用？

另一种主要类型的争用称为元数据争用。这种类型的争用与 I/O 无关。当多个线程试图同时修改内存中的同一页时，内存中会发生这种争用。

您可以使用与跟踪对象分配争用相同的方法跟踪元数据争用，区别在于 PFS、GAM 上的等待资源不是 2:1:1、2:1:2、2:1:3和 SGAM，您更有可能看到发生在索引页和数据页上的争用，并且等待资源中的页码将是更高的值，例如 2:1:111、2:1:118 或 2:1:122 ，例如。

对于元数据争用，记录大于单个数字的页码、跟踪对象名称和页面类型描述很有用。对象名称将显示为系统表，例如 sysallocunits、syscolpars、sysjobactivity、sysscalartypes、sysschobjs 等。

SQL Server 2019 中通过内存优化的tempdb元数据改进解决了元数据争用问题。

tempdb的内存优化元数据表基本上是内存中 OLTP 和临时表元数据功能的组合。我们获取了系统表和tempdb系统表，并将它们移到了非持久内存优化表中。

请记住，tempdb是临时的 - 每次重新启动 SQL Server 时都会删除并重新创建它，因此元数据没有理由持久。我们将对象跟踪和tempdb中涉及的 12 个系统表转换为内存优化的非持久表。

我们不需要为tempdb专门的内存优化文件组，因为它无论如何都是非持久的。所有这些都在“内存中”——不需要磁盘，并且内存优化表没有锁存和锁定。我们可以使用这些无锁、无锁存的数据结构来大量增加对这些元数据表的并发性。

启用内存优化元数据表确实需要重新启动，因为我们必须配置 Hekaton DLL。这是 SQL Server 2019 中的一项重大改进，它将消除大量元数据争用。

在我们的内存优化tempdb文档中了解有关此改进如何消除tempdb繁重工作负载的元数据争用瓶颈的更多信息。

临时表缓存争用

在从 SQL Server 2005 开始的过去 SQL Server 版本中，我们引入了临时表缓存来解决一些元数据缓存争用问题。基本上，当你缓存一个临时表对象时——当你删除该表时，SQL Server 实际上并没有删除元数据——我们保留通过存储过程使用的所有临时对象的缓存，然后我们为这些对象重用元数据当您再次使用相同的临时表调用相同的存储过程时。

因此，临时表缓存对元数据的命中较少，并减轻了部分元数据争用——但并非完全如此。

临时表缓存通过允许我们重用在存储过程执行之间没有变化的表来帮助解决元数据争用问题。只要表在创建后没有被更改，它就有资格被同一存储过程的另一个执行重用。但是，如果表被更改（例如，通过添加索引或列），则不能重用它，必须在存储过程完成时将其删除。

为了完全删除表，我们需要从几个不同的表中删除元数据，而这一切都是在存储过程执行结束时同步完成的。此外，每次向 SQL Server 添加新功能（列存储索引、临时表、内存中 OLTP 等）时，所有这些新功能都需要跟踪新的元数据，因此我们需要从中删除的系统表的数量正在增加，这使得该过程更具影响力。

临时表缓存争用在较大的 SQL Server 环境、较大的核心数以及随着缓存大小和访问缓存的并发线程数的增加可能会更加突出，这可能会导致更慢的缓存访问以及相关内存对象的争用与缓存。

这种情况可以通过两种不同的方式表现出来：CMEMTHREAD 等待和 SOS_CACHESTORE 自旋锁等待。要解决临时表缓存争用问题，建议跟踪这些等待条件以获取证据，并确保您已安装 SQL Server 的最新累积更新 (CU)。

SQL Server 2022 tempdb改进

在 SQL Server 2022 中，我们通过引入类似于并发 PFS 更新的并发 GAM 和 SGAM 更新来解决最终的常见争用领域。

当我们在tempdb中查找混合范围时，我们使用全局分配映射 (GAM) 页面和共享全局分配映射 (SGAM) 页面。

在以前的版本中，在更高的并发工作负载下，我们可能会出现 GAM 争用，其中许多不同的线程尝试在同一个 GAM 页面上分配范围，并且每个线程必须首先等待另一个线程释放其 UPDATE 锁存器，然后才能获得自己的锁存器以允许它们做出改变——所以，我们只是在排队等候。

从下面的工作负载示例中可以看出，在 SQL Server 2019 上，存在一堵 GAM 争用墙，导致超过 123,000 次争用计数，最长等待时间为 949 毫秒。

显示 SQL Server 2019 的性能和过去五分钟内 123,000 个锁存器争用的仪表板。

这样做的原因是使用更新锁存器，一次只有一个线程可以修改 GAM 页面，从而导致争用。这是我们仍然需要多个数据文件的主要原因，并且由于这种争用，SQL Server 吞吐量降低，需要对 GAM 页面进行多次更新的工作负载将需要更长的时间才能完成，而机器的 CPU 将未被充分利用。这种争用是由于工作量大，尤其是重复的创建和删除操作的使用。

从 SQL Server 2016 开始，我们将默认行为更改为在tempdb中创建新对象时始终分配统一范围。这有助于避免大多数 SGAM 争用，但我们仍然使用混合范围进行索引分配映射 (IAM) 页面分配。IAM 页面用于跟踪属于一个对象的所有页面，因此创建的每个对象都至少有一个 IAM 页面。对于大多数工作负载，这些 IAM 页面分配不会导致任何问题，但对于具有许多并发分配线程的极其繁忙的tempdb工作负载，这些 IAM 页面分配仍然会导致 SGAM 争用。

SQL Server 2022 解决了 GAM 和 SGAM 争用问题

SQL Server tempdb争用在 SQL Server 2022 中几乎完全解决，并且这些好处默认情况下是启用的。通过 SQL Server 2022 中的这些改进，我们允许在共享锁存器下对 GAM 和 SGAM 进行并发更新，而不是使用更新锁存器。此改进消除了几乎所有tempdb争用，允许并行线程能够修改 GAM 和 SGAM 页，如下例所示。