应用程序对表执行频繁的 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”

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

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 
        CLOSE optimize_for_seq_key_cursor
        DEALLOCATE optimize_for_seq_key_cursor
        SELECT 'No PAGELATCH_EX contention found on user databases on in SQL Server at this time'
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
        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
        SELECT 'No PAGELATCH_EX contention found on user databases on in SQL Server at this time'
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 - 常见工作负载模式和迁移注意事项中的文档。
PAGELATCH_EX等待和大量插入