通过

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

  • 项目

本文可帮助你解决服务器遇到大量负载时出现严重阻塞的问题。

原始产品版本：SQL Server
原始 KB 数： 2154845

现象

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

注意

如果页面被 8088 均匀地分割，则它是 PFS 页面。 例如，第 2：3：905856 页是 tempdb 中 file_id=3 中的 PFS。

以下操作 广泛使用 tempdb ：

  • 临时表（本地或全局）的重复创建和删除操作。
  • 使用 tempdb 存储的表变量。
  • 与 CURSORS 关联的工作表。
  • 与 ORDER BY 子句关联的工作表。
  • 与 GROUP BY 子句关联的工作表。
  • 与 HASH PLANS 关联的工作文件。

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

原因

在大量使用 tempdb 数据库时，SQL Server 在尝试分配页面时可能会遇到争用。 根据争用程度，这可能会导致涉及 tempdb 的查询和请求短暂无响应。

在对象创建过程中，必须从混合区分配两个（2）页，并分配给新对象。 一页用于索引分配映射（IAM），第二页用于对象的第一页。 SQL Server 使用共享全局分配映射（SGAM）页跟踪混合盘区。 每个 SGAM 页跟踪大约 4 GB 的数据。

若要从混合区分配页面，SQL Server 必须扫描页可用空间（PFS）页，以确定可以分配哪个混合页。 PFS 页面跟踪每个页面上可用的可用空间，每个 PFS 页面跟踪大约 8000 页。 保留适当的同步以更改 PFS 和 SGAM 页面;这可以在短时间内停止其他修饰符。

当 SQL Server 搜索要分配的混合页时，它始终在同一文件和 SGAM 页上启动扫描。 当多个混合页分配正在进行时，这会导致 SGAM 页面上的激烈争用。 这可能会导致“症状”部分中记录的问题。

注意

取消分配活动还必须修改页面。 这可能导致争用增加。

若要详细了解 SQL Server 使用的不同分配机制（SGAM、GAM、PFS、IAM），请参阅“参考”部分。

解决方法

  • SQL Server 2016 及更高版本：

    审阅

    有关这些建议的详细信息以及 SQL 2016 评审中引入的其他更改

  • SQL Server 2014 及更低版本：

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

    • 增加 tempdb 中的数据文件数，以最大化磁盘带宽并减少分配结构中的争用。 根据规则，如果逻辑处理器数小于或等于 8（8），则使用与逻辑处理器相同的数据文件数。 如果逻辑处理器数大于 8（8），请使用 8 个数据文件。 如果争用继续，将数据文件数增加四个（4）的倍数，直到争用降低到可接受的级别为止。 或者，对工作负荷或代码进行更改。

    • 请考虑在 SQL Server 2005 中使用 tempdb 时实施最佳做法建议。

    • 如果前面的步骤不会显著减少分配争用，并且争用位于 SGAM 页上，请实现跟踪标志 -T1118。 在此跟踪标志下，SQL Server 为每个数据库对象分配完整盘区，从而消除 SGAM 页面上的争用。

      注意

增加大小相等的 tempdb 数据文件数

例如，如果 tempdb 的单个数据文件大小为 8 GB，并且日志文件大小为 2 GB，则建议将数据文件数增加到 8（每个 1 GB 以保持相同的大小），并将日志文件保留原样。 将不同的数据文件放在单独的磁盘上将提供额外的性能优势。 不过，这不是必需的。 这些文件可以共存于同一磁盘卷上。

tempdb 数据文件的最佳数量取决于 tempdb 中看到的争用程度。 作为起点，可以将 tempdb 配置为至少等于为 SQL Server 分配的逻辑处理器数。 对于高端系统，起始数字可以是 8 （8）。 如果未减少争用，可能需要增加数据文件的数量。

建议使用数据文件的大小相等大小。 SQL Server 2000 Service Pack 4（SP4）引入了一个修补程序，该修补程序使用轮循机制算法进行混合页面分配。 由于此改进，每个连续混合页分配的起始文件不同（如果存在多个文件）。 SGAM 的新分配算法是纯轮循机制，不遵循比例填充来保持速度。 建议使用相同的大小创建所有 tempdb 数据文件。

如何增加 tempdb 数据文件的数量减少争用

以下列表说明了如何增加大小相等的 tempdb 数据文件的数量可减少争用：

  • 如果 tempdb 有一个数据文件，则每个 4 GB 空间只有一个 GAM 页和一个 SGAM 页。

  • 增加 tempdb 具有相同大小的数据文件数量实际上为每个数据文件创建一个或多个 GAM 和 SGAM 页面。

  • GAM 的分配算法一次分配一个范围（八个连续页面），以轮循机制方式分配文件数，同时遵循比例填充。 因此，如果你有 10 个大小相同的文件，则第一个分配来自 File1、File2 中的第二个、File3 中的第三个分配，依此表示。

  • PFS 页面的资源争用会减少，因为一次有 8 个页面标记为 FULL，因为 GAM 正在分配页面。

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

注意

本部分仅适用于 SQL Server 2014 和更低版本。

以下列表说明了如何使用跟踪标志 -T1118 减少争用：

  • -T1118 是服务器范围的设置。
  • 在 SQL Server 的启动参数中包含 -T1118 跟踪标志，以便跟踪标志即使在回收 SQL Server 后仍有效。
  • -T1118 删除服务器上的几乎所有单页分配。
  • 通过禁用大多数单页分配，可以减少 SGAM 页面上的争用。
  • 如果 启用 -T1118 ，则几乎所有新分配都是从 GAM 页面（例如，2：1：2）（例如，2：1：2）一次分配给对象（一个盘区），而不是从对象的前 8 页（8）页的盘区中分配单个页面，而无需跟踪标志。
  • 即使打开了 -T1118，IAM 页面仍使用 SGAM 页中的单页分配。 但是，当它与修补程序 8.00.0702 结合使用并增加 tempdb 数据文件时，NET 效果会减少 SGAM 页面上的争用。 有关空间问题，请参阅下一部分。

缺点

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

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

如果这些条件为 true，可以为只需要 8 KB 空间的对象分配 64 KB（8 页 * 8 KB = 64 KB），从而浪费 56 KB 的存储空间。 但是，如果新对象在其生存期内使用超过 64 KB（8 页），则跟踪标志没有缺点。 因此，在最坏的情况下，SQL Server 可能会在第一次分配期间分配七（7）个额外的页面，但对于永远不会增长超过一个（1）页的新对象。

参考