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

發行項
12/14/2024

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

本文介紹如何在 SQL Server 中解析最後一頁插入 PAGELATCH_EX 爭用。

徵兆

請考量下列案例：

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

注意

最常見的案例是身分識別數據行上的叢集主鍵。較不常發生此問題，可以觀察到非叢集索引的問題。
您的應用程式會針對數據表執行頻繁的 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	3:38	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 中開啟筆記本

選項 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：使用OPTIMIZE_FOR_SEQUENTIAL_KEY索引選項（僅限 SQL Server 2019）

在 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 作業。因為這個方法使用數據表分割，所以只能在 SQL Server 的 Enterprise 版本上使用。

注意

您可以在 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：切換至記憶體內部 OLTP

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

參考資料

PAGELATCH_EX等候和大量插入

共用方式為

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

徵兆

原因

解決方法

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

選項 2：手動遵循步驟

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

2.選擇方法來解決問題

方法 1：使用OPTIMIZE_FOR_SEQUENTIAL_KEY索引選項（僅限 SQL Server 2019）

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

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

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

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

方法 6：搭配哈希值使用數據表數據分割和計算數據行

方法 7：切換至記憶體內部 OLTP

參考資料

意見反應

其他資源

共用方式為

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

徵兆

原因

解決方法

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

選項 2：手動遵循步驟

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

2.選擇方法來解決問題

方法 1：使用OPTIMIZE_FOR_SEQUENTIAL_KEY索引選項 （僅限 SQL Server 2019）

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

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

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

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

方法 6：搭配哈希值使用數據表數據分割和計算數據行

方法 7：切換至記憶體內部 OLTP

參考資料

意見反應

其他資源

方法 1：使用OPTIMIZE_FOR_SEQUENTIAL_KEY索引選項（僅限 SQL Server 2019）