推荐减少 tempdb 数据库中的SQL Server争用

本文帮助您解决在服务器遇到负载过重时发现严重阻止的问题。

原始产品版本:  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 子句关联的工作表。
  • 与哈希计划关联的工作文件。

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

原因

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

创建对象期间,必须从混合 (分配两) 2 个页面,并将其分配给新对象。 一个页面用于 IAM (索引) ,第二个页面用于对象的第一页。 SQL Server SGAM 页面使用"共享全局分配映射"页 (混合) 范围。 每个 SGAM 页跟踪大约 4 GB 的数据。

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

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

备注

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

若要了解有关 SGAM、GAM、PFS、IAM SQL Server (使用的不同分配机制) 请参阅参考部分。

解决方案

  • SQL Server 2016 及更高版本:

    审阅

    有关这些建议和 2016 年 2 月审阅中引入的其他SQL详细信息

  • SQL Server 2014 及更早版本:

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

    • 增加 tempdb 中数据文件的数量,以最大化磁盘带宽并减少分配结构的争用。 一般来说,如果逻辑处理器的数量小于或等于 8 (8) ,则使用与逻辑处理器相同的数据文件数。 如果逻辑处理器的数量大于 8 (8) ,请使用 8 个数据文件。 如果争用继续,将数据文件的数量增加 4 (4) 最多为逻辑处理器数,直到争用减少到可接受的级别。 或者,对工作负荷或代码进行更改。

    • 请考虑在2005年 1 月SQL Server tempdb 中实现最佳实践建议。

    • 如果前面的步骤没有显著减少分配争用,并且争用位于 SGAM 页面上,则实现跟踪标志 -T1118。 在此跟踪标志下,SQL Server向每个数据库对象分配全部范围,从而消除 SGAM 页上的争用。

      备注

      • 此跟踪标志影响数据库实例上的SQL Server。 若要了解如何确定分配争用是否在 SGAM 页面上,请参阅由 DML操作引起的监视争用。

      • 对于 SQL Server 2014 环境,请确保应用 Service Pack 3 以利用以下知识库文章中介绍的修补程序。 该改进进一步减少了 SQL Server 2014 环境中争用。 除了跨所有 tempdb 数据文件的循环分配之外,修复程序还通过在同一个数据文件中的多个 PFS 页面之间执行循环分配来改进 PFS 页面分配。

        KB4099472 - 2014、2016 和 2017 SQL Server PFS 页面循环算法改进

      • MSSQL 开发团队博客:tempdb 中的文件和跟踪SQL Server更新

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

例如,如果 tempdb 的单个数据文件大小为 8 GB,而日志文件大小为 2 GB,则建议将数据文件的数量增加至 8 (8) (每 1 GB 将保持大小相同的) 并保持 日志文件 的相同大小。 将不同的数据文件放在单独的磁盘上将提供额外的性能优势。 但是,这不是必需的。 文件可以共存于同一磁盘卷上。

tempdb 数据文件 的最佳数量取决于在 tempdb 中看到的争用程度。 作为起始点,您可以将 tempdb 配置为至少等于为服务器分配的逻辑处理器SQL Server。 对于高端系统,起始编号可以是 8 到 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,等等。

  • 由于 GAM 正在分配页面,因此 PFS 页面的资源争用减少了,因为一次将八个页面标记为 FULL。

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

备注

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

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

  • -T1118 是服务器范围的设置。
  • 在查询的 Startup 参数中包括 -T1118 跟踪SQL Server以便跟踪标记即使在回收后SQL Server仍然有效。
  • -T1118 将删除服务器上几乎所有单个页面分配。
  • 通过禁用大多数单页分配,可以减少 SGAM 页面上的争用。
  • 如果 -T1118 处于打开状态,则几乎所有新分配都从游戏页面 (例如,2:1:2) (一次向对象分配八个 (8) 页面 (一个范围) ,而不是从对象前八个 (8) 页的范围中向单个页面分配,不带跟踪标记。
  • IAM 页面仍使用 SGAM 页面中的单页分配,即使 -T1118打开。 但是,当它与修补程序 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) 个额外页面,这些页面只会分配给从不会超过一个 (1) 页面的新对象。

参考