解决SQL Server中最后一页插入PAGELATCH_EX争用问题

项目
06/15/2023

原始产品版本：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。

在 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

PAGELATCH_EX等待和大量插入

通过