有关减少 SQL Server tempdb 数据库中的分配争用的建议

原始产品版本：SQL Server 2014、SQL Server 2016、SQL Server 2017、SQL Server 2019、SQL Server 2022
原始 KB 数： 2154845

总结

本文介绍如何诊断和减少SQL Servertempdb数据库中的分配争用。 它涵盖 PAGELATCH_UP 页面可用空间（PFS）、全局分配映射（GAM）和共享全局分配映射（SGAM）页面上的等待。 本文介绍如何使用多个大小相同的tempdb数据文件，并在较旧版本上使用跟踪标志 1118 和 1117，以提高在繁重的并发工作负荷下SQL Server性能。

现象

在运行Microsoft SQL Server的服务器上，当服务器负载过大时，你会注意到严重阻塞。 动态管理视图 sys.dm_exec_requests 并 sys.dm_os_waiting_tasks 显示请求或任务正在等待 tempdb 资源。 等待类型为PAGELATCH_UP，等待资源指向tempdb中的页。 这些页面的格式可能是 2:1:1、2:1:3 等（tempdb中的 PFS 和 SGAM 页面）。

注意

如果页码被 8088 均匀地分割，则为 PFS 页。 例如，页 2:3:905856 是 file_id=3 中 tempdb 中的一个 PFS 页。

以下操作将大量使用 tempdb：

  • 临时表（本地或全局）上的重复创建和删除操作。
  • 使用 tempdb 进行存储的表变量。
  • 与游标关联的工作表。
  • 与 ORDER BY 子句相关联的工作表。
  • 与 GROUP BY 子句关联的工作表。
  • 与哈希计划相关的工作文件。

这些活动可能会导致争用。

原因

当tempdb被大量使用时，SQL Server 在分配页时可能会出现争用。 视争用程度而定，使用 tempdb 的查询和请求可能会短暂无响应。

在较旧版本的 SQL Server 中创建对象期间，系统会从混合区分配两个页面，并将其分配给新对象。 一页是 “索引分配映射 ”（IAM）页，第二页是对象的第一页。 SQL Server 使用共享全局分配映射（SGAM）页跟踪混合盘区。 每个 SGAM 页跟踪大约 4 GB 的数据。

若要从混合区分配页，SQL Server 会扫描页可用空间 (PFS) 页，以查找空闲的混合页。 PFS 页面跟踪每个页面上的可用空间，每个 PFS 页面跟踪大约 8,000 页。 当 SQL Server 更改 PFS 和 SGAM 页时，需要进行同步，而这种同步可能会在短时间内阻塞其他修改操作。

当 SQL Server 搜索要分配的混合页时，它始终在同一文件和 SGAM 页上启动扫描。 当许多混合页分配同时运行时，此行为会导致 SGAM 页上出现激烈争用，这会产生前面所述的症状。

注意

解除分配活动也会更改这些页面，并且可以添加到争用中。

若要详细了解SQL Server使用的分配机制（SGAM、GAM、PFS 和 IAM），请参阅“相关内容”部分。

解决方案

SQL Server 2014 已于 2024 年 7 月 9 日结束扩展支持，因此大多数读者应参考 SQL Server 2016 及更高版本的指导。

SQL Server 2016 及更高版本

  1. 查看 tempdb 数据库 以获取当前性能指南。
  2. 确保 tempdb 具有多个大小相同的数据文件。 作为起点，每个逻辑处理器最多使用一个数据文件，最多 8 个文件。 如果争用仍然存在，请每次按四个文件一组增加，直到达到逻辑处理器的数量。
  3. 使用最新的累积更新使SQL Server实例保持最新状态，以获得进一步的分配改进。 例如，请参阅 KB 4099472：SQL Server 2014、2016 和 2017 中的 PFS 页面轮循机制算法改进。
  4. 如果在 SQL Server 2019 或更高版本中争用仍然存在，请参阅有关并发 PFS 更新和内存优化 tempdb元数据的 tempdb 数据库指南。

SQL Server 2014 及更早版本

若要提高 tempdb 的并发性，请尝试以下方法：

  • 增加数据文件 tempdb 的数量，以最大化磁盘带宽并减少分配结构上的争用。 通常，如果逻辑处理器数为 8 或更少，则使用与逻辑处理器相同的数据文件数。 如果逻辑处理器数大于 8，则从 8 个数据文件开始。 如果争用继续，请在四组（最多四个逻辑处理器）中添加数据文件，或更改工作负荷或代码。

  • 查阅 SQL Server 2005 中使用 tempdb中的最佳实践指南。

  • 如果前面的步骤未能显著减少分配争用，并且争用发生在 SGAM 页上，请启用跟踪标记 -T1118。 启用此跟踪标志后，SQL Server 会为每个数据库对象分配整区，这会显著减少对 SGAM 页的争用。

    注意

    • 此跟踪标志会影响SQL Server实例上的每个数据库。 若要检查分配争用是否发生在 SGAM 页面上，请参阅 监视由 DML 操作导致的争用。
    • 对于 SQL Server 2014，请安装 Service Pack 3，以获取 KB 4099472 中的修复；该修复通过在同一数据文件中的多个 PFS 页之间以轮循方式进行分配，进一步减少了争用。

有关 tempdb 分配的更多信息

SQL Server 2016 及更高版本中的默认 tempdb 行为

从 SQL Server 2016 开始，一些改进会默认减少tempdb分配争用，因此通常不需要启用跟踪标志 1118 或 1117：

  • 默认情况下，对于 tempdb 已禁用混合区分配（即先前由跟踪标志 1118 启用的行为）。 所有新的分配均来自统一区段。
  • 所有 tempdb 数据文件都会一同自动增长（这是以前通过跟踪标志 1117 启用的行为）。
  • SQL Server安装程序根据逻辑处理器数（最多 8 个）建议多个大小tempdb相同的数据文件。
  • 在 SQL Server 2019 及更高版本中，并发 PFS 更新可进一步减少 PFS 页闩锁争用。

如果您运行的是 SQL Server 2016 或更高版本，请重点关注 tempdb 文件的大小和数量。 仅在 SQL Server 2014 或更早版本上使用跟踪标志。

tempdb 数据文件等大小配置示例

例如，如果单个 tempdb 数据文件为 8 GB 且日志文件为 2 GB，请将数据文件数增加到 8 个（每个 1 GB 保持相等大小），并使日志文件保持不变。 将数据文件放在单独的磁盘上可以带来额外的性能优势，但这不是必需的。 这些文件可以共存于同一磁盘卷上。

最佳tempdb数据文件数量取决于你观察到的争用程度。 作为起点，将文件计数设置为等于分配给SQL Server的逻辑处理器数。 对于高端系统，从 8 个文件开始。 如果争用仍未减少，请添加更多数据文件。

对所有数据文件使用相同大小。 SQL Server 2000 Service Pack 4（SP4）引入了一项修复，对混合页分配采用轮循算法。 通过这一改进，当存在多个文件时，每次连续进行混合页分配的起始文件都会发生变化。 SGAM 分配算法采用纯轮询方式，不遵循比例填充，因此请创建所有大小相同的 tempdb 数据文件。

更多的 tempdb 数据文件如何减少争用

添加大小相同的 tempdb 数据文件可通过以下方式减少争用：

  • 如果 tempdb 有一个数据文件，则每 4 GB 空间会有一个 GAM 页和一个 SGAM 页。
  • 相同大小的更多数据文件实际上为每个数据文件创建一个或多个 GAM 和 SGAM 页面。
  • GAM 分配算法一次分配一个盘区（八个连续页），同时遵循比例填充原则，并按照轮询方式从各个文件中进行分配。 例如，如果你有 10 个大小相同的文件，第一个分配来自 File1，第二个来自 File2，第三个来自 File3，依此类推。
  • PFS 页面争用得以降低，因为在 GAM 分配这些页时，每次都会将连续 8 个页标记为 FULL。

跟踪标志-T1118 如何减少争用

注意

本部分仅适用于 SQL Server 2014 及更低版本。 在 SQL Server 2016 及更高版本中，此行为是默认行为tempdb。

跟踪标记 -T1118 通过以下方式减少争用：

  • -T1118 是一个服务器范围内的设置。
  • 将 -T1118 添加到 SQL Server 启动参数中，以便在 SQL Server 重启后跟踪标志继续生效。
  • -T1118 消除了服务器上几乎所有的单页内存分配。
  • 禁用大多数单页分配可减少 SGAM 页面争用。
  • 当 -T1118 开启时，几乎所有新的分配都来自 GAM 页（例如 2:1:2）；该页每次向对象分配八个页（一个区），而不是在对象的前八页中从某个区逐页分配单个页。
  • 即使在启用 -T1118 时，IAM 页仍使用来自 SGAM 页的单页分配。 但如果配合修补程序 8.00.0702 和更多的 tempdb 数据文件，最终效果是减少了 SGAM 页面争用。 有关空间问题，请参阅下一部分。

跟踪标志 1118 与新版本 SQL Server 中的默认行为的比较

行为 SQL Server 2014 年及更早版本 （无-T1118） SQL Server 2014 年及更早版本 （含-T1118） SQL Server 2016 及更高版本（tempdb默认值）
新对象的前八页 混合区中的单页分配（SGAM） 完全统一盘区 （GAM） 完整统一区（GAM）
SGAM 页面争用风险 在大量使用tempdb时较高 低 低
Scope 不適用 全服务器范围 仅 tempdb
需要采取行动 None 添加 -T1118 启动参数 None

跟踪标志 -T1118 的缺点

使用 -T1118 缺点是，如果满足以下两个条件，数据库大小可能会增加：

  • 在用户数据库中创建新对象。
  • 每个新对象占用的存储空间少于 64 KB。

在这些情况下，SQL Server可以为只需要 8 KB 的对象分配 64 KB（8 页 * 8 KB），这会浪费 56 KB 的存储空间。 如果新对象在其生命周期内占用超过 64 KB（8 页），那么该跟踪标志就没有弊端。 在最坏的情况下，SQL Server在第一次分配期间分配 7 个额外页面，但仅适用于永远不会超过一页的新对象。

相关内容

  • Last updated on