减少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 计划关联的工作文件。

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

原因

大量使用 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) 使用的不同分配机制，请参阅参考部分。

解决方案

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

例如，如果 tempdb 的单个数据文件大小为 8 GB，而日志文件大小为 2 GB，则建议将数据文件数增加到 8 (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 页的资源争用，因为一次八个页面被标记为 FULL，因为 GAM 正在分配页面。

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

注意

本部分仅适用于 SQL Server 2014 及更早版本。

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

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

缺点

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

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

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

References