解决SQL Server中最后一页插入PAGELATCH_EX争用问题
原始产品版本：SQL Server
原始 KB 编号： 4460004
本文介绍如何解决SQL Server中的最后一页插入PAGELATCH_EX争用。
症状
请考虑以下方案：
你有一个列，其中包含顺序值，例如通过 Getdate () 函数插入的 Identity 列或 DateTime 列。
你有一个聚集索引，该索引将顺序列作为前导列。
注意
最常见的方案是标识列上的群集主键。 对于非聚集索引，可以较少观察到此问题。
应用程序对表执行频繁的 INSERT 或 UPDATE 操作。
系统上有许多 CPU。 通常，服务器具有 16 个或更多 CPU。 此硬件配置允许多个会话同时对同一个表执行 INSERT 操作。
在这种情况下，应用程序的性能可能会下降。 在 中 sys.dm_exec_requests检查等待类型时，会看到 等待类型PAGELATCH_EX 以及许多正在等待此等待类型的会话。
如果在系统上运行以下诊断查询，则会出现另一个问题：
选择session_id、wait_type、wait_time、wait_resource sys.dm_exec_requests ，其中session_id > 50，wait_type = “pagelatch_ex”
在这种情况下，可能会获得如下所示的结果。
| session_id | wait_type | wait_time | wait_resource |
|---|---|---|---|
| 60 | PAGELATCH_EX | 100 | 5:1:4144 |
| 75 | PAGELATCH_EX | 123 | 5:1:4144 |
| 79 | PAGELATCH_EX | 401 | 5:1:4144 |
| 80 | PAGELATCH_EX | 253 | 5:1:4144 |
| 81 | PAGELATCH_EX | 312 | 5:1:4144 |
| 82 | PAGELATCH_EX | 355 | 5:1:4144 |
| 84 | PAGELATCH_EX | 312 | 5:1:4144 |
| 85 | PAGELATCH_EX | 338 | 5:1:4144 |
| 87 | PAGELATCH_EX | 405 | 5:1:4144 |
| 88 | PAGELATCH_EX | 111 | 5:1:4144 |
| 90 | PAGELATCH_EX | 38 | 5:1:4144 |
| 92 | PAGELATCH_EX | 115 | 5:1:4144 |
| 94 | PAGELATCH_EX | 49 | 5:1:4144 |
| 101 | PAGELATCH_EX | 301 | 5:1:4144 |
| 102 | PAGELATCH_EX | 45 | 5:1:4144 |
| 103 | PAGELATCH_EX | 515 | 5:1:4144 |
| 105 | PAGELATCH_EX | 39 | 5:1:4144 |
你注意到，多个会话都在等待与以下模式类似的同一资源：
database_id = 5，file_id = 1，数据库page_id = 4144
注意
database_id应为用户数据库， (ID 号大于或等于 5) 。 如果database_id为 2，则可能遇到 TEMPDB 上的文件、跟踪标志和更新中讨论的问题。
原因
PAGELATCH (数据或索引页上的闩锁) 是一种线程同步机制。 它用于同步对位于缓冲区缓存中的数据库页的短期物理访问。
PAGELATCH 不同于 PAGEIOLATCH。 后者用于在从磁盘读取页面或写入磁盘时同步对页面的物理访问。
页闩锁在每个系统中都很常见，因为它们可确保物理页面保护。 聚集索引按前导键列对数据进行排序。 因此，在顺序列上创建索引时，所有新数据插入都发生在索引末尾的同一页上，直到填充该页为止。 但是，在高负载下，并发 INSERT 操作可能会导致 B 树的最后一页发生争用。 此争用可能发生在聚集索引和非聚集索引上。 原因是非聚集索引按前导键对叶级页面进行排序。 此问题也称为最后一页插入争用。
有关详细信息，请参阅诊断和解决SQL Server上的闩锁争用。
解决方案
可以选择以下两个选项之一来解决问题。
选项 1：通过 Azure Data Studio 直接在笔记本中执行步骤
注意
在尝试打开此笔记本之前，请确保在本地计算机上安装了 Azure Data Studio。 若要安装它，请转到 了解如何安装 Azure Data Studio。
选项 2：手动执行步骤
若要解决此争用问题，总体策略是阻止所有并发 INSERT 操作访问同一数据库页。 相反，让每个 INSERT 操作访问不同的页面并提高并发性。 因此，以下任何按顺序列以外的列组织数据的方法都可实现此目标。
1. 确认PAGELATCH_EX上的争用并确定争用资源
此 T-SQL 脚本可帮助你发现系统上是否存在 PAGELATCH_EX 多个会话 (5 个或更多) 等待，等待时间长 (10 毫秒或更多) 。 它还有助于使用 sys.dm_exec_requests 和 DBCC PAGE 或仅) sys.fn_PageResCracker 和 sys.dm_db_page_info (SQL Server 2019 发现争用的对象和索引。
SET NOCOUNT ON
DECLARE @dbname SYSNAME, @dbid INT, @objectid INT, @indexid INT, @indexname SYSNAME, @sql VARCHAR(8000), @manul_identification VARCHAR(8000)
IF (CONVERT(INT, SERVERPROPERTY('ProductMajorVersion')) >= 15)
BEGIN
DROP TABLE IF EXISTS #PageLatchEXContention
SELECT DB_NAME(page_info.database_id) DbName, r.db_id DbId, page_info.[object_id] ObjectId, page_info.index_id IndexId
INTO #PageLatchEXContention
FROM sys.dm_exec_requests AS er
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st
CROSS APPLY sys.fn_PageResCracker (er.page_resource) AS r
CROSS APPLY sys.dm_db_page_info(r.[db_id], r.[file_id], r.page_id, 'DETAILED') AS page_info
WHERE er.wait_type = 'PAGELATCH_EX' AND page_info.database_id not in (db_id('master'),db_id('msdb'), db_id('model'), db_id('tempdb'))
GROUP BY DB_NAME(page_info.database_id), r.db_id, page_info.[object_id], page_info.index_id
HAVING COUNT(er.session_id) > 5 AND Max (er.wait_time) > 10
SELECT * FROM #PageLatchEXContention
IF EXISTS (SELECT 1 FROM #PageLatchEXContention)
BEGIN
DECLARE optimize_for_seq_key_cursor CURSOR FOR
SELECT DbName, DbId, ObjectId, IndexId FROM #PageLatchEXContention
OPEN optimize_for_seq_key_cursor
FETCH NEXT FROM optimize_for_seq_key_cursor into @dbname, @dbid, @objectid , @indexid
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT 'Consider using below statement to enable OPTIMIZE_FOR_SEQUENTIAL_KEY for the indexes in the "' + @dbname + '" database' AS Recommendation
SELECT @sql = 'select ''use ' + @dbname + '; ALTER INDEX '' + i.name + '' ON ' + OBJECT_NAME(@objectid, @dbid) + ' SET (OPTIMIZE_FOR_SEQUENTIAL_KEY = ON )'' AS Corrective_Action from #PageLatchEXContention pl JOIN ' + @dbname+'.sys.indexes i ON pl.ObjectID = i.object_id WHERE object_id = ' + CONVERT(VARCHAR, @objectid) + ' AND index_id = ' + CONVERT(VARCHAR, @indexid)
EXECUTE (@sql)
FETCH NEXT FROM optimize_for_seq_key_cursor INTO @dbname, @dbid, @objectid , @indexid
END
CLOSE optimize_for_seq_key_cursor
DEALLOCATE optimize_for_seq_key_cursor
END
ELSE
SELECT 'No PAGELATCH_EX contention found on user databases on in SQL Server at this time'
END
ELSE
BEGIN
IF OBJECT_ID('tempdb..#PageLatchEXContentionLegacy') IS NOT NULL
DROP TABLE #PageLatchEXContentionLegacy
SELECT 'dbcc traceon (3604); dbcc page(' + replace(wait_resource,':',',') + ',3); dbcc traceoff (3604)' TSQL_Command
INTO #PageLatchEXContentionLegacy
FROM sys.dm_exec_requests er
WHERE er.wait_type = 'PAGELATCH_EX' AND er.database_id NOT IN (db_id('master'),db_id('msdb'), db_id('model'), db_id('tempdb'))
GROUP BY wait_resource
HAVING COUNT(er.session_id) > 5 AND Max (er.wait_time) > 10
SELECT * FROM #PageLatchEXContentionLegacy
IF EXISTS(SELECT 1 FROM #PageLatchEXContentionLegacy)
BEGIN
SELECT 'On SQL Server 2017 or lower versions, you can manually identify the object where contention is occurring using DBCC PAGE locate the m_objId = ??. Then SELECT OBJECT_NAME(object_id_identified) and locate indexes with sequential values in this object' AS Recommendation
DECLARE get_command CURSOR FOR
SELECT TSQL_Command from #PageLatchEXContentionLegacy
OPEN get_command
FETCH NEXT FROM get_command into @sql
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sql AS Step1_Run_This_Command_To_Find_Object
SELECT 'select OBJECT_NAME(object_id_identified)' AS Step2_Find_Object_Name_From_ID
FETCH NEXT FROM get_command INTO @sql
END
CLOSE get_command
DEALLOCATE get_command
SELECT 'Follow https://learn.microsoft.com/troubleshoot/sql/performance/resolve-pagelatch-ex-contention for resolution recommendations that fits your environment best' Step3_Apply_KB_article
END
ELSE
SELECT 'No PAGELATCH_EX contention found on user databases on in SQL Server at this time'
END
2. 选择解决问题的方法
可以使用以下方法之一来解决此问题。 选择最适合自己情况的一个。
方法 1：仅 (SQL Server 2019) 使用OPTIMIZE_FOR_SEQUENTIAL_KEY索引选项
在 2019 SQL Server，添加了一个新的索引选项 (OPTIMIZE_FOR_SEQUENTIAL_KEY) ，可帮助解决此问题，而无需使用以下任何方法。 有关详细信息 ，请参阅OPTIMIZE_FOR_SEQUENTIAL_KEY的幕后 。
方法 2：将主键移出标识列
将包含顺序值的列设置为非聚集索引，然后将聚集索引移到另一列。 例如，对于标识列上的主键，请删除群集主键，然后将其重新创建为非聚集主键。 此方法是最简单的方法，可直接实现目标。
例如，假设你有下表，该表是在 Identity 列上使用群集主键定义的。
USE testdb;
CREATE TABLE Customers
( CustomerID BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
CustomerLastName VARCHAR (32) NOT NULL,
CustomerFirstName VARCHAR(32) NOT NULL );
若要更改此设计，可以删除主键索引并重新定义它。
USE testdb;
ALTER TABLE Customers
DROP CONSTRAINT PK__Customer__A4AE64B98819CFF6;
ALTER TABLE Customers
ADD CONSTRAINT pk_Cust1
PRIMARY KEY NONCLUSTERED (CustomerID)
方法 3：使前导键成为非顺序列
对聚集索引定义进行重新排序，以便前导列不是顺序列。 此方法要求聚集索引是复合索引。 例如，在客户表中，可以将 CustomerLastName 列设置为前导列，后跟 CustomerID。 建议全面测试此方法，以确保它满足性能要求。
USE testdb;
ALTER TABLE Customers
ADD CONSTRAINT pk_Cust1
PRIMARY KEY CLUSTERED (CustomerLastName, CustomerID)
方法 4：添加非顺序值作为前导键
添加非队列哈希值作为前导索引键。 此方法还有助于分散插入。 哈希值作为与系统上的 CPU 数匹配的模数生成。 例如，在 16 CPU 系统上，可以使用 16 的模数。 此方法针对多个数据库页统一分布 INSERT 操作。
USE testdb;
CREATE TABLE Customers
( CustomerID BIGINT IDENTITY(1,1) NOT NULL,
CustomerLastName VARCHAR (32) NOT NULL,
CustomerFirstName VARCHAR(32) NOT NULL );
ALTER TABLE Customers
ADD [HashValue] AS (CONVERT([TINYINT], abs([CustomerID])%16)) PERSISTED NOT NULL;
ALTER TABLE Customers
ADD CONSTRAINT pk_table1
PRIMARY KEY CLUSTERED (HashValue, CustomerID);
方法 5：使用 GUID 作为前导键
使用 GUID 作为索引的前导键列，以确保插入的均匀分布。
注意
尽管此方法实现了目标，但我们不建议使用此方法，因为它会带来多个挑战，包括索引键大、页面拆分频繁、页面密度低等。
方法 6：使用表分区和具有哈希值的计算列
使用表分区和具有哈希值的计算列来分散 INSERT 操作。 由于此方法使用表分区，因此只能在企业版SQL Server上使用。
注意
可以在 SQL Server 2016 SP1 Standard Edition 中使用分区表。 有关详细信息，请参阅 SQL Server 2016 的版本和支持的功能一文中的“表和索引分区”的说明。
下面是具有 16 个 CPU 的系统中的示例。
USE testdb;
CREATE TABLE Customers
( CustomerID BIGINT IDENTITY(1,1) NOT NULL,
CustomerLastName VARCHAR (32) NOT NULL,
CustomerFirstName VARCHAR(32) NOT NULL );
ALTER TABLE Customers
ADD [HashID] AS CONVERT(TINYINT, ABS(CustomerID % 16)) PERSISTED NOT NULL;
CREATE PARTITION FUNCTION pf_hash (TINYINT) AS RANGE LEFT FOR VALUES (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15) ;
CREATE PARTITION SCHEME ps_hash AS PARTITION pf_hash ALL TO ([PRIMARY]);
CREATE UNIQUE CLUSTERED INDEX CIX_Hash
ON Customers (CustomerID, HashID) ON ps_hash(HashID);
方法 7：切换到 In-Memory OLTP
或者，使用 In-Memory OLTP，尤其是在闩锁争用较高时。 此技术可消除闩锁争用。 但是，必须重新设计特定的表 () （其中观察到页闩锁争用）并将其迁移到内存优化表。 可以使用 内存优化顾问 和 事务性能分析报告 来确定迁移是否可行以及迁移工作量。 有关 In-Memory OLTP 如何消除闩锁争用的详细信息，请下载并查看 内存中 OLTP - 常见工作负载模式和迁移注意事项中的文档。
References
反馈
即将发布：在整个 2024 年，我们将逐步淘汰作为内容反馈机制的“GitHub 问题”，并将其取代为新的反馈系统。 有关详细信息，请参阅：https://aka.ms/ContentUserFeedback。
提交和查看相关反馈