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

原始产品版本:  SQL Server
原始 KB 编号:   4460004

本文介绍如何解决最后一页插入 PAGELATCH_EX 在SQL Server。

症状

请考虑以下方案:

  • 您的列包含通过 Getdate () 函数插入的顺序值,如 Identity 列或 DateTime 列。

  • 你有一个以连续列作为前导列的群集索引。

    备注

    最常见的方案是 Identity 列上的群集主键。 对于非群集索引,可以观察到此问题的频率较低。

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

  • 您系统中有很多 CPU。 通常,服务器具有 16 个 CPU 或更多 CPU。 这允许多个会话同时对同一个表执行 INSERT 操作。

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

如果对系统运行以下诊断查询,会出现另一个问题:

select session_id, wait_type, wait_time, wait_resource from sys.dm_exec_requests where session_id > 50 and 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 (数据或索引页上的闩锁) 是线程同步机制。 它用于同步对位于缓冲区缓存中的数据库页面的短期物理访问。

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

页面闩锁在每种系统中都很常见,因为它们可确保物理页面保护。 群集索引按前导键列对数据进行排序。 因此,当您在连续列上创建索引时,将导致所有新数据插入发生在索引末尾的同一页上,直到填充该页。 但是,在高负载下,并发 INSERT 操作可能导致在 B 树的最后一页上出现争用。 这种争用可能发生在群集索引和非群集索引上。 这是因为非群集索引按前导键对叶级别页面进行顺序。 此问题也称为最后一页插入争用。

有关详细信息,请参阅诊断和解决数据库上的闩锁SQL Server。

解决方案

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

选项 1:直接在笔记本中通过 Azure Data Studio

备注

尝试打开此笔记本之前,请确保Azure Data Studio本地计算机上安装此笔记本。 若要安装它,请转到了解如何安装Azure Data Studio。

选项 2:手动执行步骤

若要解决此争用,整体策略是阻止所有并发 INSERT 操作访问同一数据库页。 相反,让每个 INSERT 操作访问不同的页面并增加并发。 因此,按列(而不是顺序列)组织数据的以下任一方法都实现了此目标。

1. 确认资源上的PAGELATCH_EX并标识争用资源

此 T-SQL 脚本可帮助你发现具有多个会话的系统是否等待 (5 个或多个) ,等待时间为 PAGELATCH_EX (10 毫秒或) 。 它还可以帮助你发现使用 sys.dm_exec_requests 和DBCC 页面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://docs.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:仅在 OPTIMIZE_FOR_SEQUENTIAL_KEY 2019 (SQL Server索引选项)

在 SQL Server 2019 中,添加了一 () 索引选项,可帮助解决此问题,而无需使用下列任何 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 操作。 由于此方法使用表分区,因此它仅在 Enterprise 版本上SQL Server。

备注

您可以使用 SQL Server 2016 SP1 Standard Edition。 有关详细信息,请参阅文章Editions and supported features of 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。 此技术整体上消除了闩锁争用。 但是,您必须重新设计特定表 (,) 页闩锁争用的表迁移到内存优化表。 可以使用内存优化 顾问事务 性能分析报告来确定是否可以进行迁移以及执行迁移所涉及的工作。 有关 OLTP In-Memory消除闩锁争用的信息,请下载并查看内存中 OLTP - 常见工作负载模式和迁移 注意事项中的文档

References

PAGELATCH_EX和大量插入