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

原始产品版本：SQL Server
原始 KB 数： 4460004

本文介绍如何解决 SQL Server 中的最后一页插入 PAGELATCH_EX 争用。

现象

请考虑下列情形：

• 你有一个包含顺序值的列，例如标识列或通过 Getdate（） 函数插入的 DateTime 列。

• 你有一个聚集索引，该索引将顺序列作为前导列。

  注意

  最常见的方案是标识列上的聚集主键。 对于非聚集索引，可以不太频繁地观察到此问题。

• 应用程序针对表执行频繁的 INSERT 或 UPDATE 操作。

• 系统上有许多 CPU。 通常，服务器具有 16 个 CPU 或更多。 此硬件配置允许多个会话同时对同一表执行 INSERT 操作。

在这种情况下，可能会降低应用程序的性能。 检查等待类型 sys.dm_exec_requests时，将观察 PAGELATCH_EX 等待类型以及等待此等待类型的许多会话。

如果在系统上运行以下诊断查询，则会出现另一个问题：

从session_id > 5 wait_type 0 中选择session_id、wait_type、wait_time、sys.dm_exec_requestswait_resource =“pagelatch_ex”

在这种情况下，可能会获得如下所示的结果。

你注意到，多个会话都在等待类似于以下模式的同一资源：

database_id = 5，file_id = 1，数据库page_id = 4144

原因

PAGELATCH（数据或索引页面上的闩锁）是一种线程同步机制。 它可用于同步对位于缓冲区缓存中数据库页面的短期物理访问。

PAGELATCH 不同于 PAGEIOLATCH。 后者可用于在从磁盘读取或写入页面时同步对页面的物理访问。

页面闩锁在每个系统中都十分常见，因为它们可确保物理页面保护。 聚集索引会按前导键列对数据进行排序。 因此，在顺序列上创建索引时，位于索引末尾的同一页面上会插入所有新数据，直到该页面被填满为止。 但在高负载下，并发 INSERT 操作可能会导致 B 树的最后一个页面发生争用。 此争用可能发生在聚集索引和非聚集索引上。 其原因是，非聚集索引会按前导键对叶级页面进行排序。 此问题也称为最后一页插入争用。

有关详细信息，请参阅 在 SQL Server 上诊断和解析闩锁争用。

解决方法

可以选择以下两个选项之一来解决问题。

选项 1：通过 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://zcusa.951200.xyz/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：仅使用OPTIMIZE_FOR_SEQUENTIAL_KEY索引选项（仅限 SQL Server 2019）

在 SQL Server 2019 中，添加了一个新的索引选项（OPTIMIZE_FOR_SEQUENTIAL_KEY），可帮助解决此问题，而无需使用以下任何方法。 有关详细信息，请参阅 OPTIMIZE_FOR_SEQUENTIAL_KEY 幕后。

方法 2：将主键移出标识列

将包含顺序值的列设为非聚集索引，然后将聚集索引移到另一列。 例如，对于标识列上的主键，请删除聚集主键，然后将其重新创建为非聚集主键。 此方法是最简单的方法，直接实现目标。

例如，假设下表是使用标识列上的聚集主键定义的。

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 企业版上使用。

下面是具有 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：切换到内存中 OLTP

或者，尤其是在闩锁争用较高的情况下，使用内存中 OLTP。 该技术消除了整体闩锁争用。 但是，必须重新设计并迁移特定表（其中观察到页闩锁争用）到内存优化表。 可以使用 内存优化顾问 和 事务性能分析报告 来确定迁移是否可行，以及执行迁移所要付出的努力。 有关内存中 OLTP 如何消除闩锁争用的详细信息，请下载并查看内存中 OLTP 中的 文档 - 常见工作负荷模式和迁移注意事项。

参考

PAGELATCH_EX等待和大量插入