减少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) 使用的不同分配机制，请参阅参考部分。
解决方案
SQL Server 2016 及更高版本：
审阅
优化 SQL Server 中的 tempdb 数据库性能。
应用 SQL Server 2016 和 2017 的相关 CU 以利用以下更新。 取得了一项改进，进一步减少了 2016 SQL Server 和 2017 SQL Server争用。 除了跨所有 tempdb 数据文件进行轮循机制分配外，该修补程序还通过在同一数据文件中的多个 PFS 页之间执行轮循机制分配来改进 PFS 页分配。 有关详细信息，请参阅 SQL Server 2014、2016 和 2017 KB4099472 - PFS 页面轮循机制算法改进。
有关 SQL 2016 中引入的这些建议和其他更改的详细信息，请查看
SQL Server 2014 及更早版本：
若要提高 tempdb 的并发性，请尝试以下方法：
增加 tempdb 中的数据文件数，以最大化磁盘带宽并减少分配结构中的争用。 通常，如果逻辑处理器的数量小于或等于 8 (8) ，请使用与逻辑处理器相同数量的数据文件。 如果逻辑处理器数大于 8 (8) ，请使用 8 个数据文件。 如果争用继续，请将数据文件数按 4 (4) 的倍数增加到逻辑处理器数，直到争用降低到可接受的水平。 或者，对工作负荷或代码进行更改。
考虑在 2005 SQL Server中使用 tempdb 中的最佳做法建议。
如果前面的步骤未显著减少分配争用，并且争用位于 SGAM 页上，请实现跟踪标志 -T1118。 在此跟踪标志下，SQL Server为每个数据库对象分配完整的盘区，从而消除 SGAM 页面上的争用。
注意
此跟踪标志影响 SQL Server 实例上的每个数据库。 有关如何确定分配争用是否位于 SGAM 页上的信息，请参阅 由 DML 操作引起的监视争用。
对于 SQL Server 2014 环境，请确保应用 Service Pack 3 以利用以下知识库文章中所述的修补程序。 此改进进一步减少了 SQL Server 2014 环境中的争用。 除了跨所有 tempdb 数据文件进行轮循机制分配外，该修补程序还通过在同一数据文件中的多个 PFS 页之间执行轮循机制分配来改进 PFS 页分配。
MSSQL Tiger 团队博客：SQL Server tempdb 中的文件和跟踪标志和更新
增加大小相等的 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) 页的新对象。