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 のインストール方法に関するページを参照してください。
オプション 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 - 一般的なワークロード パターンと移行に関する考慮事項」のドキュメントをダウンロードして確認してください。