SQL Serverの最終ページ挿入PAGELATCH_EX競合を解決する

  • [アーティクル]

元の製品バージョン: SQL Server
元の KB 番号: 4460004

この記事では、SQL Serverで最後のページ挿入PAGELATCH_EXの競合を解決する方法について説明します。

現象

次のようなシナリオを考えてみましょう。

  • Identity 列や Getdate() 関数を介して挿入される DateTime 列などのシーケンシャル値を含む列があります。

  • 先頭列としてシーケンシャル列を持つクラスター化インデックスがあります。

    注:

    最も一般的なシナリオは、ID 列のクラスター化された主キーです。 あまり頻繁に、この問題は非クラスター化インデックスで確認できます。

  • アプリケーションは、テーブルに対して頻繁に INSERT または UPDATE 操作を実行します。

  • システムには多くの CPU があります。 通常、サーバーには 16 個以上の CPU があります。 このハードウェア構成により、複数のセッションで同じテーブルに対して INSERT 操作を同時に実行できます。

このような状況では、アプリケーションのパフォーマンスが低下する可能性があります。 で sys.dm_exec_requests待機の種類を調べると、 PAGELATCH_EX 待機の種類と、この待機の種類で待機している多数のセッションで待機が観察されます。

システムで次の診断クエリを実行すると、別の問題が発生します。

session_id、wait_type、wait_time、session_id 50 からwait_resource sys.dm_exec_requests> を選択し、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 (データまたはインデックス ページのラッチ) は、スレッド同期メカニズムです。 これは、バッファー キャッシュにあるデータベース ページへの短期的な物理アクセスを同期するために使用されます。

PAGELATCH はPAGEIOLATCH とは異なります。 後者は、ページがディスクから読み取られたり、ディスクに書き込まれたりするときに、ページへの物理的なアクセスを同期するために使用されます。

ページ ラッチは、物理的なページ保護を確保するため、すべてのシステムで一般的です。 クラスター化インデックスは、先頭のキー列でデータを並べ替えます。 このため、シーケンシャル列にインデックスを作成すると、そのページが入力されるまで、インデックスの末尾にある同じページにすべての新しいデータ挿入が行われます。 ただし、負荷が高い場合、同時 INSERT 操作によって B ツリーの最後のページで競合が発生する可能性があります。 この競合は、クラスター化インデックスと非クラスター化インデックスで発生する可能性があります。 その理由は、非クラスター化インデックスは、先頭キーによってリーフ レベルのページを順序付けするためです。 この問題は、最後のページ挿入の競合とも呼ばれます。

詳細については、「SQL Serverでのラッチ競合の診断と解決」を参照してください。

解決方法

次の 2 つのオプションのいずれかを選択して、問題を解決できます。

オプション 1: Azure Data Studio を使用してノートブックで手順を直接実行する

注:

このノートブックを開く前に、Azure Data Studio がローカル コンピューターにインストールされていることを確認してください。 インストールするには、 Azure Data Studio のインストール方法に関するページを参照してください。

Azure Data Studio で Notebook を開く

オプション 2: 手順を手動で実行する

この競合を解決するには、全体的な方法として、すべての同時 INSERT 操作が同じデータベース ページにアクセスできないようにします。 代わりに、各 INSERT 操作に別のページにアクセスし、コンカレンシーを高めます。 したがって、シーケンシャル列以外の列でデータを整理する次のいずれかの方法でこの目標を達成します。

1. PAGELATCH_EXの競合を確認し、競合リソースを特定する

この T-SQL スクリプトは、待機時間が PAGELATCH_EX 長い (10 ミリ秒以上) 複数のセッション (5 つ以上) を持つシステムで待機があるかどうかを検出するのに役立ちます。 また、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: 主キーを ID 列から移動する

シーケンシャル値を含む列を非クラスター化インデックスにしてから、クラスター化インデックスを別の列に移動します。 たとえば、ID 列の主キーの場合は、クラスター化された主キーを削除し、非クラスター化主キーとして再作成します。 この方法は最も簡単に従い、目的を直接達成します。

たとえば、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 で使用できます。 詳細については、「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: OLTP を In-Memory に切り替える

または、特にラッチの競合が大きい場合は、OLTP In-Memory 使用します。 このテクノロジにより、ラッチの競合全体が排除されます。 ただし、ページ ラッチの競合が観察される特定のテーブルを再設計して、メモリ最適化テーブルに移行する必要があります。 メモリ最適化アドバイザーとトランザクション パフォーマンス分析レポートを使用して、移行が可能かどうかを判断し、移行を実行する作業を決定できます。 OLTP を In-Memory してラッチの競合を排除する方法の詳細については、「 インメモリ OLTP - 一般的なワークロード パターンと移行に関する考慮事項」のドキュメントをダウンロードして確認してください。

関連情報

PAGELATCH_EX待機と大量の挿入