ブロックされたプロセス レポートを使用するか、SQL Server によって提供されるデッドロック グラフを定期的に収集すると、次のような問題が発生します。
waitresource="ページ: 6:3:70133"
waitresource=“KEY: 6:72057594041991168 (ce52f92a058c)“
場合によっては、調査対象の巨大な XML にさらに多くの情報が含まれることがあります (デッドロック グラフには、オブジェクトとインデックスの名前を見つけるのに役立つリソースのリストが含まれています)。ただし、常にそうとは限りません。
このテキストはそれらを解読するのに役立ちます。
ここにある情報はすべてインターネット上のさまざまな場所にあり、非常に分散されています。 DBCC PAGE から hobt_id 、文書化されていない %%physloc%% および %%lockres%% 関数まで、すべてをまとめたいと考えています。
まず、PAGE ロックの待機について説明し、次に KEY ロックに進みます。
1) waitresource="ページ: 6:3:70133" = データベース ID: ファイル ID: ページ番号
リクエストが PAGE ロックで待機している場合、SQL Server はそのページのアドレスを提供します。
「PAGE: 6:3:70133」を分解すると、次のようになります。
- データベースID = 6
- データファイルID = 3
- ページ番号 = 70133
1.1) データベース ID を復号化する
次のクエリを使用してデータベースの名前を検索します。
SELECT
name
FROM sys.databases
WHERE database_id=6;
GO公開です 私のSQL Server上で。
1.2) データ ファイルの名前を探します - 興味がある場合
次のステップでは data_file_id を使用してテーブル名を検索します。 次のステップに進むこともできますが、ファイル名に興味がある場合は、見つかったデータベースのコンテキストでクエリを実行し、次のクエリに data_file_id を置き換えることでファイルを見つけることができます。
USE WideWorldImporters;
GO
SELECT
name,
physical_name
FROM sys.database_files
WHERE file_id = 3;
GOWideWorldImporters データベースでは、これは WWI_UserData というファイルであり、これを C:MSSQLDATAwideWorldImporters_UserData.ndf に復元しました。 （おっと、私がシステム ドライブにファイルを置いているところを見つかりましたね。いや！恥ずかしいです）。
1.3) DBCC ページからオブジェクト名を取得する
データファイル 70133 のページ #3 が WorldwideImporters データベースに属していることがわかりました。 文書化されていない DBCC PAGE とトレース フラグ 3604 を使用して、このページの内容を確認できます。
注: DBCC PAGE は文書化されていないため、別のサーバーのどこかに復元されたコピーで使用することを好みます。 場合によっては、彼女は (約。 翻訳者 - 残念ながら、リンクはどこにもつながりませんが、URL から判断すると、フィルターされたインデックスについて話していることがわかります。).
/* This trace flag makes DBCC PAGE output go to our Messages tab
instead of the SQL Server Error Log file */
DBCC TRACEON (3604);
GO
/* DBCC PAGE (DatabaseName, FileNumber, PageNumber, DumpStyle)*/
DBCC PAGE ('WideWorldImporters',3,70133,2);
GO
結果までスクロールすると、object_id とindex_id を見つけることができます。
ほぼ完了しました！ これで、クエリを使用してテーブル名とインデックス名を検索できるようになりました。
USE WideWorldImporters;
GO
SELECT
sc.name as schema_name,
so.name as object_name,
si.name as index_name
FROM sys.objects as so
JOIN sys.indexes as si on
so.object_id=si.object_id
JOIN sys.schemas AS sc on
so.schema_id=sc.schema_id
WHERE
so.object_id = 94623380
and si.index_id = 1;
GOここでは、ロックの待機が Sales.OrderLines テーブルの PK_Sales_OrderLines インデックス上で行われていることがわかります。
注: SQL Server 2014 以降では、文書化されていない DMO sys.dm_db_database_page_allocations を使用してオブジェクト名を見つけることもできます。 ただし、データベース内のすべてのページに対してクエリを実行する必要があり、大規模なデータベースではあまり見栄えがしないため、DBCC PAGE を使用しました。
1.4) ブロックされたページのデータを見ることはできますか?
ぬぅ、そうだね。 でも...本当にそれが必要ですか?
小さなテーブルでも遅いです。 でも、それはちょっとクールなので、ここまで読んだのですから...%%physloc%% について話しましょう!
%%physloc%% は、各エントリの物理 ID を返す文書化されていない魔法です。 使用できます .
Sales.OrderLines でページをブロックする必要があることがわかったので、次のクエリを使用して、ページ #3 のデータ ファイル #70133 に保存されているこのテーブル内のすべてのデータを確認できます。
Use WideWorldImporters;
GO
SELECT
sys.fn_PhysLocFormatter (%%physloc%%),
*
FROM Sales.OrderLines (NOLOCK)
WHERE sys.fn_PhysLocFormatter (%%physloc%%) like '(3:70133%'
GO先ほども言いましたが、小さなテーブルでも遅いです。 リクエストに NOLOCK を追加したのは、調べたいデータがロックが検出されたときとまったく同じであるという保証がまだないためです。そのため、ダーティ リードを安全に行うことができます。
しかし、クエリは、クエリが競合したまさに 25 行を返します。
PAGE ロックについては十分です。 KEY ロックを待っている場合はどうなりますか?
2) waitresource="KEY: 6:72057594041991168 (ce52f92a058c)" = Database_Id、HOBT_Id (本当に必要な場合は %%lockres%% で復号化できるマジック ハッシュ)
クエリがインデックス エントリをロックしようとして、それ自体でロックされてしまうと、まったく異なるタイプのアドレスが取得されます。
「6:72057594041991168 (ce52f92a058c)」を部分に分割すると、次のようになります。
- データベースID = 6
- hobt_id = 72057594041991168
- マジックハッシュ = (ce52f92a058c)
2.1) データベース ID を復号化する
上記の例とまったく同じように機能します。 次のクエリを使用してデータベースの名前を見つけます。
SELECT
name
FROM sys.databases
WHERE database_id=6;
GO私の場合も同様です .
2.2) hobt_id を復号化する
見つかったデータベースのコンテキストで、テーブルとインデックスの名前を決定するのに役立ついくつかの結合を使用して sys.partitions をクエリする必要があります。
USE WideWorldImporters;
GO
SELECT
sc.name as schema_name,
so.name as object_name,
si.name as index_name
FROM sys.partitions AS p
JOIN sys.objects as so on
p.object_id=so.object_id
JOIN sys.indexes as si on
p.index_id=si.index_id and
p.object_id=si.object_id
JOIN sys.schemas AS sc on
so.schema_id=sc.schema_id
WHERE hobt_id = 72057594041991168;
GOリクエストが PK_Application_Countries インデックスを使用して Application.Countries ロックで待機していることがわかります。
2.3) 次に、%%lockres%% の魔法を使用します - どのエントリがロックされているかを調べたい場合
どの行でロックが必要だったのかを本当に知りたい場合は、テーブル自体にクエリを実行することで知ることができます。 文書化されていない %%lockres%% 関数を使用して、マジック ハッシュに一致するエントリを見つけることができます。
このクエリはテーブル全体をスキャンするため、大きなテーブルではまったく面白くない可能性があることに注意してください。
SELECT
*
FROM Application.Countries (NOLOCK)
WHERE %%lockres%% = '(ce52f92a058c)';
GO
NOLOCKを追加しました（) ロックが問題になる可能性があるためです。 私たちは、トランザクションが開始されたときにそこにあったものではなく、現在そこにあるものだけを見たいのです。データの一貫性は私たちにとって重要ではないと思います。
ほら、私たちが目指して戦った記録です！
謝辞とさらなる読み物
これらの多くのことを誰が最初に説明したかは覚えていませんが、最も文書化されていないものについて、あなたが気に入るかもしれない XNUMX つの投稿を以下に示します。
- ポール・ランダルの投稿 (最初の例にデータがあるため)
- StackOverflow に関する質問 (XNUMX 番目の例でデータが見つかったように)。 答えの XNUMX つが投稿につながります .
出所： habr.com