解決 SQL Server 中的最後一頁插入PAGELATCH_EX爭用

發行項
03/25/2024

原始產品版本： SQL S
原始 KB 編號： 4460004

本文介紹如何解決 SQL Server 中的最後一頁插入PAGELATCH_EX競爭。

徵狀

請考量下列案例：

您有一個數據行，其中包含循序值，例如透過 Getdate () 函式插入的 Identity 資料行或 DateTime 資料行。
您有具有循序數據行做為前置數據行的叢集索引。

注意事項

最常見的案例是 Identity 數據行上的叢集主鍵。較不常見，非叢集索引可以觀察到此問題。
您的應用程式會對數據表執行頻繁的 INSERT 或 UPDATE 作業。
您在系統上有許多 CPU。一般而言，伺服器有16個以上的CPU。此硬體組態可讓多個會話同時對相同的數據表執行 INSERT 作業。

在此情況下，您的應用程式效能可能會降低。當您在 sys.dm_exec_requests中檢查等候類型時，您會觀察 PAGELATCH_EX 等候類型和等候此等候類型的許多會話。

如果您在系統上執行下列診斷查詢，就會發生另一個問題：

從 sys.dm_exec_requests session_id 50 和 wait_type = 'pagelatch_ex' 的位置選取session_id、wait_type > 、wait_time wait_resource

在此情況下，您可能會得到類似下列的結果。

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應該是使用者資料庫， (標識符大於或等於 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 中開啟 Notebook

選項 2：手動遵循步驟

若要解決此爭用，整體策略是防止所有並行 INSERT 作業存取相同的資料庫頁面。相反地，讓每個 INSERT 作業存取不同的頁面，並增加並行。因此，根據循序數據行以外的數據行來組織數據的下列任何方法都會達成此目標。

1.確認PAGELATCH_EX上的爭用，並識別爭用資源

此 T-SQL 腳本可協助您探索系統上是否有 PAGELATCH_EX 具有多個會話的等候 (5 或 5 個以上的) ， (10 毫秒以上的) 。它也可協助您探索爭用的物件和索引是使用 sys.dm_exec_requests 和 DBCC PAGE 或sys.fn_PageResCracker，且僅sys.dm_db_page_info 2019 (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：僅在 2019 (SQL Server 使用 OPTIMIZE_FOR_SEQUENTIAL_KEY 索引選項)

在 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 中使用數據分割數據表。如需詳細資訊，請參閱 2016 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：切換至 In-Memory OLTP

或者，請使用 In-Memory OLTP，特別是當閂鎖競爭偏高時。這項技術可消除整體閂鎖競爭。不過，您必須重新設計特定數據表，並將 (的) 重新設計並移轉至記憶體優化數據表，以在其中觀察到頁面閂鎖競爭。您可以使用記憶體優化建議程式和交易效能分析報告來判斷是否可進行移轉，以及執行移轉所需的工作。如需 In-Memory OLTP 如何消除闩鎖競爭的詳細資訊，請下載並檢閱記憶體內部 OLTP - 常見工作負載模式和移轉考慮中的檔。

參考資料

PAGELATCH_EX等候和大量插入

共用方式為

解決 SQL Server 中的最後一頁插入PAGELATCH_EX爭用

徵狀

原因

解決方案

選項 1：透過 Azure Data Studio 直接在筆記本中執行步驟

選項 2：手動遵循步驟

1.確認PAGELATCH_EX上的爭用，並識別爭用資源

2.選擇方法來解決問題

方法 1：僅在 2019 (SQL Server 使用 OPTIMIZE_FOR_SEQUENTIAL_KEY 索引選項)

方法 2：將主鍵移出標識列

方法 3：將前置索引鍵設為非循序數據行

方法 4：新增非循序值作為前置索引鍵

方法 5：使用 GUID 作為前置金鑰

方法 6：使用數據表數據分割和具有哈希值的計算數據行

方法 7：切換至 In-Memory OLTP

參考資料

意見反應

其他資源