次の方法で共有

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

  • [アーティクル]

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

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

現象

以下のようなシナリオが考えられます。

  • Getdate() 関数を通じて挿入される Identity 列や DateTime 列などの連続する値を含む列があります。

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

    Note

    最も一般的なシナリオは、ID 列のクラスター化された主キーです。 この問題は、非クラスター化インデックスで発生する頻度が低くなります。

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

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

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

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

sys.dm_exec_requestsからsession_id、wait_type、wait_time、wait_resourceを選択します (session_id > 50、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

Note

database_idはユーザー データベースである必要があります (ID 番号は 5 以上)。 database_idが 2 の場合は、代わりに、TEMPDB の Files、トレース フラグ、および更新で説明されている問題が発生している可能性があります。

原因

PAGELATCH (データまたはインデックス ページのラッチ) は、スレッド同期メカニズムです。 これは、バッファー キャッシュ内にあるデータベース ページへの短期的な物理アクセスを同期するために使用されます。

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

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

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

解決方法

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

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

Note

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

Azure Data Studio でノートブックを開く

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

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

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

この T-SQL スクリプトを使用すると、多数のセッション (5 つ以上) で、待機時間が長い (10 ミリ秒以上) システムで PAGELATCH_EX 待機があるかどうかを検出するのに役立ちます。 また、 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 列の主キーの場合は、クラスター化された主キーを削除してから、非クラスター化主キーとして再作成します。 この方法は最も簡単なフォローであり、目的を直接達成します。

たとえば、ID 列でクラスター化された主キーを使用して定義された次の表があるとします。

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 を使用して、挿入の均一な分散を確保します。

Note

この目標を達成しますが、大きなインデックス キー、頻繁なページ分割、ページ密度の低さなど、複数の課題があるため、この方法はお勧めしません。

方法 6: テーブルのパーティション分割と計算列とハッシュ値を使用する

INSERT 操作を分散するには、テーブルのパーティション分割とハッシュ値を持つ計算列を使用します。 このメソッドはテーブルパーティション分割を使用するため、SQL Server の Enterprise エディションでのみ使用できます。

Note

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 を使用します。 このテクノロジにより、ラッチの競合が全体的に排除されます。 ただし、ページ ラッチの競合が観察される特定のテーブルを再設計して、メモリ最適化テーブルに移行する必要があります。 Memory Optimization Advisor と Transaction Performance Analysis Report を使用して、移行が可能かどうか、および移行を実行する作業を決定できます。 インメモリ OLTP がラッチの競合を排除する方法の詳細については、「 In-Memory OLTP - 一般的なワークロード パターンと移行に関する考慮事項」のドキュメントをダウンロードして確認してください。

関連情報

PAGELATCH_EX待機と大量の挿入