SQL Server のブロックの問題を理解して解決する

適用対象: SQL Server (サポートされているすべてのバージョン)、Azure SQL Managed Instance

元の KB 番号: 224453

目標

この記事では、SQL Server でのブロックについて説明し、ブロックのトラブルシューティングと解決方法について説明します。

この記事では、接続という用語は、データベースの 1 つのログオン セッションを指します。 各接続は、多くの DMV でセッション ID (SPID) または session_id として表示されます。 これらの各 SPID は、通常の意味では個別のプロセス コンテキストではありませんが、多くの場合、プロセスと呼ばれます。 代わりに、各 SPID は、特定のクライアントからの単一接続の要求を処理するために必要なサーバー リソースとデータ構造で構成されます。 1 つのクライアント アプリケーションに 1 つ以上の接続が含まれる場合があります。 SQL Server の観点からは、1 つのクライアント コンピューター上の 1 つのクライアント アプリケーションからの複数の接続と、複数のクライアント アプリケーションまたは複数のクライアント コンピューターからの複数の接続の間に違いはありません。これらはアトミックです。 1 つの接続では、ソース クライアントに関係なく、別の接続をブロックできます。

ブロックとは

ブロックは、ロックベースの同時実行を備えたリレーショナル データベース管理システム (RDBMS) の避けられない設計上の特性です。 前述のように、SQL Server では、1 つのセッションが特定のリソースに対するロックを保持し、2 つ目の SPID が同じリソースで競合するロックの種類を取得しようとすると、ブロックが発生します。 通常、最初の SPID によってリソースがロックされる期間は小さくなります。 所有セッションがロックを解除すると、2 つ目の接続でリソースに対する独自のロックを取得し、処理を続行できます。 ここで説明するブロックは通常の動作であり、システムのパフォーマンスに顕著な影響を与えず、1 日を通して何度も発生する場合があります。

クエリの期間とトランザクション コンテキストによって、ロックが保持される期間と、そのロックが他のクエリに与える影響が決まります。 トランザクション内でクエリが実行されない (ロック ヒントが使用されていない) 場合、SELECT ステートメントのロックは、クエリ中ではなく、実際に読み取られる時点でのみリソースに対して保持されます。 INSERT、UPDATE、および DELETE ステートメントの場合、データの整合性と必要に応じてクエリのロールバックを許可するために、クエリ中にロックが保持されます。

トランザクション内で実行されるクエリの場合、ロックが保持される期間は、クエリの種類、トランザクション分離レベル、およびクエリでロック ヒントが使用されるかどうかによって決まります。 ロック、ロック ヒント、およびトランザクション分離レベルの説明については、次の記事を参照してください。

• データベース エンジンのロック
• ロックと行のバージョン管理のカスタマイズ
• ロック モード
• ロックの互換性
• データベース エンジンの行のバージョン管理に基づく分離レベル
• Transactions

ロックとブロックがシステムのパフォーマンスに悪影響を与えるまで続く場合は、次のいずれかの理由が原因です。

• SPID は、リソースを解放する前に、一連のリソースに対するロックを長期間保持します。 この種類のブロックは時間の経過と共に解決されますが、パフォーマンスの低下を引き起こす可能性があります。

• SPID は一連のリソースに対するロックを保持し、解放しません。 この種類のブロックは、それ自体を解決せず、影響を受けるリソースへのアクセスを無期限に防止します。

最初のシナリオでは、さまざまな SPID によって時間の経過と共に異なるリソースがブロックされ、移動ターゲットが作成されるため、状況は非常に流動的になる可能性があります。 これらの状況では、SQL Server Management Studio を使用して個々のクエリに問題を絞り込むトラブルシューティングが困難です。 これに対し、2 番目の状況では、診断が容易な一貫性のある状態になります。

アプリケーションとブロック

ブロックの問題に直面すると、サーバー側のチューニングとプラットフォームの問題に重点を置く傾向がある場合があります。 ただし、データベースに対してのみ注意を払っても解決につながらない場合があり、クライアント アプリケーションとそれが送信するクエリを調べるための時間とエネルギーをより適切に吸収できます。 アプリケーションがデータベース呼び出しに関して公開する可視性のレベルに関係なく、ブロックの問題は多くの場合、アプリケーションによって送信された正確な SQL ステートメントの検査と、クエリのキャンセル、接続管理、すべての結果行のフェッチなどに関するアプリケーションの正確な動作の両方を必要とします。 開発ツールで接続管理、クエリのキャンセル、クエリ タイムアウト、結果フェッチなどを明示的に制御できない場合、ブロックの問題は解決できないことがあります。 特にパフォーマンスに影響を受けやすい OLTP 環境の場合は、SQL Server 用のアプリケーション開発ツールを選択する前に、この可能性を詳しく調べる必要があります。

データベースとアプリケーションの設計および構築フェーズでは、データベースのパフォーマンスに注意を払います。 特に、リソースの消費量、分離レベル、およびトランザクション パスの長さは、クエリごとに評価する必要があります。 各クエリとトランザクションは、可能な限り軽量である必要があります。 適切な接続管理の規範が実行されていないと、アプリケーションはユーザー数が少ない場合に許容できるパフォーマンスを発揮するように見えますが、ユーザー数が増えるにつれてパフォーマンスが大幅に低下する可能性があります。

アプリケーションとクエリを適切に設計することで、SQL Server は 1 台のサーバーで何千人もの同時ユーザーをサポートすることができ、ブロックはほとんど発生しません。

ブロックのトラブルシューティング

ブロックの状況に関係なく、ブロックのトラブルシューティングの方法は同じです。 これらの論理的な分離によって、この記事の残りの部分が決まります。 コンセプトは、ヘッド ブロックを見つけて、そのクエリが実行している内容と、ブロックされている理由を特定することです。 問題のあるクエリ (つまり、長期間ロックしているクエリ) が特定されたら、次の手順では、ブロックが発生した理由を分析して決定します。 理由が判明したら、クエリとトランザクションを再設計して変更を加えることができます。

トラブルシューティングの手順:

1. メイン ブロック セッション (ヘッド ブロック) を特定する

2. ブロックの原因となっているクエリとトランザクション (長期間ロックしているもの) を見つける

3. 長期間ブロックが発生している理由を分析し、理解する

4. クエリとトランザクションを再設計してブロックの問題を解決する

次に、適切なデータ キャプチャを使用してメイン ブロック セッションを特定する方法について説明します。

ブロックの情報を収集する

ブロックの問題の困難なトラブルシューティングに対処するために、データベース管理者は、SQL スクリプトを使用して、SQL Server のロックとブロックの状態を常に監視することができます。 このデータを収集するには、2 つの無料の方法があります。

1 つ目は、動的管理オブジェクト (DMO) にクエリを実行し、結果を格納して時間の経過とともに比較する方法です。 この記事で参照されているオブジェクトの一部には、動的管理ビュー (DMV) と、動的管理機能 (DMF) があります。

2 つ目は、拡張イベント (XEvents) または SQL プロファイラーのトレースを使用して、実行中のイベントをキャプチャする方法です。 SQL トレースと SQL Server プロファイラーは非推奨であるため、このトラブルシューティング ガイドでは XEvents に焦点を当てます。

DMV から情報を収集する

DMV を参照してブロックのトラブルシューティングを行うと、ブロック チェーンの先頭にある SPID (セッション ID) と SQL ステートメントを識別できます。 ブロックの対象となっている SPID を探します。 いずれかの SPID が別の SPID にブロックされている場合は、リソースを所有する SPID (ブロック SPID) を調べます。 そのリソースを所有する SPID もブロックされていますか? チェーンを辿り、ヘッド ブロックを見つけて、ロックを維持している理由を調べます。

これを行うには、以下のいずれかの方法を使用します。

• SQL Server Management Studio (SSMS) オブジェクト エクスプローラーで、最上位のサーバー オブジェクトを右クリックし、[レポート] を展開し、[標準レポート] を展開して、[アクティビティ]、[すべてのブロック トランザクション] の順に選択します。 このレポートには、ブロック チェーンの先頭にある現在のトランザクションが表示されます。 トランザクションを展開すると、ヘッド トランザクションによってブロックされているトランザクションがレポートに表示されます。 このレポートには、ブロックしている SQL ステートメントとブロックされた SQL ステートメントも表示されます。

• SSMS でアクティビティ モニターを開き、[ブロック実行者] 列を参照します。 アクティビティ モニターの詳細については、こちらを参照してください。

DMV を使用すると、より詳細なクエリ ベースの方法も利用できます。

• sp_who と sp_who2 コマンドは、現在のすべてのセッションを表示するための古いコマンドです。 DMV sys.dm_exec_sessions は、クエリとフィルター処理が容易な結果セットでより多くのデータを返します。 sys.dm_exec_sessions は他のクエリの中心に表示されます。

• 特定のセッションをすでに特定している場合は、DBCC INPUTBUFFER(<session_id>) を使用して、セッションが送信した最後のステートメントを見つけることができます。 sys.dm_exec_input_buffer 動的管理機能 (DMF) を使用して同様の結果を返すことができ、クエリとフィルター処理が容易な結果セットで、session_id と request_id を提供します。 たとえば、session_id 66 と request_id 0 によって送信された最新のクエリを返すには、以下を使用します。

SELECT * FROM sys.dm_exec_input_buffer (66,0);

• sys.dm_exec_requests と blocking_session_id 列を参照してください。 blocking_session_id = 0 の場合、セッションはブロックされていません。 sys.dm_exec_requests では現在実行中の要求のみが一覧表示されますが、接続 (アクティブまたは非アクティブ) はすべて sys.dm_exec_sessions で一覧表示されます。 次のクエリでは、sys.dm_exec_requests と sys.dm_exec_sessions の間のこの共通結合に基づいて構築します。 sys.dm_exec_requests によって返されるように注意してください。クエリは SQL Server でアクティブに実行されている必要があります。

• このサンプル クエリを実行して、アクティブに実行されているクエリとその現在の SQL バッチ テキストや入力バッファー テキストを、sys.dm_exec_sql_text または sys.dm_exec_input_buffer DMV を使用して検索します。 sys.dm_exec_sql_text の text 列によって返されるデータが NULL の場合、クエリは現在実行されていません。 その場合、sys.dm_exec_input_buffer の event_info 列には、SQL エンジンに渡された最後のコマンド文字列が含まれます。 このクエリは、session_id ごとにブロックされた session_id の一覧など、他のセッションをブロックしているセッションを識別するためにも使用できます。

WITH cteBL (session_id, blocking_these) AS 
(SELECT s.session_id, blocking_these = x.blocking_these FROM sys.dm_exec_sessions s 
CROSS APPLY    (SELECT isnull(convert(varchar(6), er.session_id),'') + ', '  
                FROM sys.dm_exec_requests as er
                WHERE er.blocking_session_id = isnull(s.session_id ,0)
                AND er.blocking_session_id <> 0
                FOR XML PATH('') ) AS x (blocking_these)
)
SELECT s.session_id, blocked_by = r.blocking_session_id, bl.blocking_these
, batch_text = t.text, input_buffer = ib.event_info, * 
FROM sys.dm_exec_sessions s 
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
INNER JOIN cteBL as bl on s.session_id = bl.session_id
OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t
OUTER APPLY sys.dm_exec_input_buffer(s.session_id, NULL) AS ib
WHERE blocking_these is not null or r.blocking_session_id > 0
ORDER BY len(bl.blocking_these) desc, r.blocking_session_id desc, r.session_id;

• Microsoft サポートによって提供されるこのより詳細なサンプル クエリを実行して、ブロック チェーンに関係するセッションのクエリ テキストを含む、複数のセッション ブロック チェーンの先頭を識別します。

WITH cteHead ( session_id,request_id,wait_type,wait_resource,last_wait_type,is_user_process,request_cpu_time
,request_logical_reads,request_reads,request_writes,wait_time,blocking_session_id,memory_usage
,session_cpu_time,session_reads,session_writes,session_logical_reads
,percent_complete,est_completion_time,request_start_time,request_status,command
,plan_handle,sql_handle,statement_start_offset,statement_end_offset,most_recent_sql_handle
,session_status,group_id,query_hash,query_plan_hash) 
AS ( SELECT sess.session_id, req.request_id, LEFT (ISNULL (req.wait_type, ''), 50) AS 'wait_type'
    , LEFT (ISNULL (req.wait_resource, ''), 40) AS 'wait_resource', LEFT (req.last_wait_type, 50) AS 'last_wait_type'
    , sess.is_user_process, req.cpu_time AS 'request_cpu_time', req.logical_reads AS 'request_logical_reads'
    , req.reads AS 'request_reads', req.writes AS 'request_writes', req.wait_time, req.blocking_session_id,sess.memory_usage
    , sess.cpu_time AS 'session_cpu_time', sess.reads AS 'session_reads', sess.writes AS 'session_writes', sess.logical_reads AS 'session_logical_reads'
    , CONVERT (decimal(5,2), req.percent_complete) AS 'percent_complete', req.estimated_completion_time AS 'est_completion_time'
    , req.start_time AS 'request_start_time', LEFT (req.status, 15) AS 'request_status', req.command
    , req.plan_handle, req.[sql_handle], req.statement_start_offset, req.statement_end_offset, conn.most_recent_sql_handle
    , LEFT (sess.status, 15) AS 'session_status', sess.group_id, req.query_hash, req.query_plan_hash
    FROM sys.dm_exec_sessions AS sess
    LEFT OUTER JOIN sys.dm_exec_requests AS req ON sess.session_id = req.session_id
    LEFT OUTER JOIN sys.dm_exec_connections AS conn on conn.session_id = sess.session_id 
    )
, cteBlockingHierarchy (head_blocker_session_id, session_id, blocking_session_id, wait_type, wait_duration_ms,
wait_resource, statement_start_offset, statement_end_offset, plan_handle, sql_handle, most_recent_sql_handle, [Level])
AS ( SELECT head.session_id AS head_blocker_session_id, head.session_id AS session_id, head.blocking_session_id
    , head.wait_type, head.wait_time, head.wait_resource, head.statement_start_offset, head.statement_end_offset
    , head.plan_handle, head.sql_handle, head.most_recent_sql_handle, 0 AS [Level]
    FROM cteHead AS head
    WHERE (head.blocking_session_id IS NULL OR head.blocking_session_id = 0)
    AND head.session_id IN (SELECT DISTINCT blocking_session_id FROM cteHead WHERE blocking_session_id != 0)
    UNION ALL
    SELECT h.head_blocker_session_id, blocked.session_id, blocked.blocking_session_id, blocked.wait_type,
    blocked.wait_time, blocked.wait_resource, h.statement_start_offset, h.statement_end_offset,
    h.plan_handle, h.sql_handle, h.most_recent_sql_handle, [Level] + 1
    FROM cteHead AS blocked
    INNER JOIN cteBlockingHierarchy AS h ON h.session_id = blocked.blocking_session_id and h.session_id!=blocked.session_id --avoid infinite recursion for latch type of blocking
    WHERE h.wait_type COLLATE Latin1_General_BIN NOT IN ('EXCHANGE', 'CXPACKET') or h.wait_type is null
    )
SELECT bh.*, txt.text AS blocker_query_or_most_recent_query 
FROM cteBlockingHierarchy AS bh 
OUTER APPLY sys.dm_exec_sql_text (ISNULL ([sql_handle], most_recent_sql_handle)) AS txt;

• 実行時間の長いトランザクションまたはコミットされていないトランザクションをキャッチするには、sys.dm_tran_database_transactions、sys.dm_tran_session_transactions、sys.dm_exec_connections、sys.dm_exec_sql_text など、現在の未処理トランザクションを表示するための別の DMV セットを使用します。 トランザクションの追跡には複数の DMV が関連付けられます。詳細については、こちらの「トランザクションの DMV」を参照してください。

SELECT [s_tst].[session_id],
[database_name] = DB_NAME (s_tdt.database_id),
[s_tdt].[database_transaction_begin_time], 
[sql_text] = [s_est].[text] 
FROM sys.dm_tran_database_transactions [s_tdt]
INNER JOIN sys.dm_tran_session_transactions [s_tst] ON [s_tst].[transaction_id] = [s_tdt].[transaction_id]
INNER JOIN sys.dm_exec_connections [s_ec] ON [s_ec].[session_id] = [s_tst].[session_id]
CROSS APPLY sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est];

• SQL Server のスレッド/タスク レイヤーにある sys.dm_os_waiting_tasks を参照します。 これにより、要求が現在発生しているSQL wait_type に関する情報が返されます。 sys.dm_exec_requests と同様に、アクティブな要求のみが sys.dm_os_waiting_tasks によって返されます。

• クエリによって配置されたロックの詳細については、sys.dm_tran_locks DMV を使用します。 この DMV は、運用 SQL Server インスタンスに大量のデータを返すことができるため、現在保持されているロックを診断するのに役立ちます。

sys.dm_os_waiting_tasks の INNER JOIN により、次のクエリは、sys.dm_tran_locks からの出力を、現在ブロックされている要求、それらの待機状態、およびそれらのロックのみに制限します。

SELECT table_name = schema_name(o.schema_id) + '.' + o.name
, wt.wait_duration_ms, wt.wait_type, wt.blocking_session_id, wt.resource_description
, tm.resource_type, tm.request_status, tm.request_mode, tm.request_session_id
FROM sys.dm_tran_locks AS tm
INNER JOIN sys.dm_os_waiting_tasks as wt ON tm.lock_owner_address = wt.resource_address
LEFT OUTER JOIN sys.partitions AS p on p.hobt_id = tm.resource_associated_entity_id
LEFT OUTER JOIN sys.objects o on o.object_id = p.object_id or tm.resource_associated_entity_id = o.object_id
WHERE resource_database_id = DB_ID()
AND object_name(p.object_id) = '<table_name>';

DMV を使用すると、クエリ結果を時間の経過と共に格納することでデータ ポイントが提供されます。これにより、指定した時間間隔でブロックを確認して、永続化されたブロックや傾向を特定できます。 このような問題のトラブルシューティングを行う CSS 用のメイン ツールには、PSSDiag データ コレクターを使用しています。 このツールでは、「SQL Server Perf Stats」 を使用して、上記で参照されている DMV から結果セットを時間の経過と共に収集します。 このツールは常に進化しているため、GitHub 上にある DiagManager の最新のパブリック バージョンを確認してください。

拡張イベントから情報を収集する

多くの場合、上記の情報に加えて、サーバー上のアクティビティのトレースをキャプチャして、SQL Server のブロックの問題を徹底的に調査する必要があります。 たとえば、セッションがトランザクション内で複数のステートメントを実行する場合、送信された最後のステートメントのみが表されます。 ただし、以前のステートメントの 1 つは、ロックがまだ保持されている理由である場合があります。 トレースを使用すると、現在のトランザクション内でセッションによって実行されたすべてのコマンドを確認できます。

SQL Server でトレースをキャプチャするには、拡張イベント (XEvents) トレースと Profiler トレース、という 2 つの方法があります。 ただし、SQL Server Profiler を使用する SQL トレースは非推奨です。 XEvents は、より多くの多様性と確認されたシステムへの影響を軽減する新しい優れたトレース プラットフォームであり、そのインターフェイスは SSMS に統合されています。

XEvent Profiler のメニューの下にあるオブジェクト エクスプローラーに一覧表示されている、SSMS で開始する準備が整った拡張イベント セッションが事前に用意されています。 詳細については、「XEvent Profiler」を参照してください。 SSMS で独自のカスタム拡張イベント セッションを作成することもできます。「拡張イベントの新しいセッション ウィザード」を参照してください。 ブロックの問題のトラブルシューティングでは、通常、次の情報がキャプチャされます。

• カテゴリ エラー:
  • 注意
  • Blocked_process_report**
  • Error_reported (チャネル管理者)
  • Exchange_spill
  • Execution_warning

**ブロックされたプロセス レポートが生成されるしきい値と頻度を構成するには、sp_configure コマンドを使用して、ブロックされたプロセスのしきい値オプションを構成します。これは秒単位で設定できます。 既定では、ブロックされたプロセス レポートは生成されません。

• カテゴリの警告:

  • Hash_warning
  • Missing_column_statistics
  • Missing_join_predicate
  • Sort_warning

• カテゴリの実行:

  • Rpc_completed
  • Rpc_starting
  • Sql_batch_completed
  • Sql_batch_starting

• カテゴリのロック

  • Lock_deadlock

• カテゴリのセッション

  • Existing_connection
  • ログイン
  • ログアウト

一般的なブロック シナリオを特定して解決する

上記の情報を調べることで、ほとんどのブロック問題の原因を特定できます。 この記事の残りの部分では、この情報を使用して、一般的なブロック シナリオを特定して解決する方法について説明します。 この説明では、ブロック スクリプト (前を参照) を使用してブロック SPID に関する情報をキャプチャし、XEvent セッションを使用してアプリケーション アクティビティをキャプチャしたことを前提としています。

ブロック データを分析する

• DMV sys.dm_exec_requests と sys.dm_exec_sessions の出力を調べて、blocking_these と session_id を使用して、ブロック チェーンの先頭を判別します。 これにより、ブロックされている要求とブロックしている要求が最も明確に識別されます。 ブロックされているセッションおよびブロックしているセッションをさらに詳しく調べます。 ブロック チェーンに共通またはルートはありますか? 共通テーブルを共有している可能性が高く、ブロック チェーンに関係する 1 つ以上のセッションが書き込み操作を実行しています。

• ブロック チェーンの先頭にある SPID については、sys.dm_exec_requests と sys.dm_exec_sessions の出力を調べます。 次の列を調べてください。

  • sys.dm_exec_requests.status
    この列には、特定の要求の状態が表示されます。 通常、スリープ状態は、SPID が実行を完了し、アプリケーションが別のクエリまたはバッチを送信するのを待機していることを示します。 実行可能な状態または実行中の状態は、SPID が現在クエリを処理していることを示します。 次の表に、さまざまな状態値の簡単な説明を示します。

    状態	意味
    背景	SPID は、デッドロック検出、ログ ライター、チェックポイントなどのバックグラウンド タスクを実行しています。
    スリープ状態	SPID は現在実行されていません。 これは通常、SPID がアプリケーションからのコマンドを待機していることを示します。
    実行中	SPID は現在スケジューラで実行されています。
    実行可能	SPID はスケジューラの実行可能なキューにあり、スケジューラの時刻の取得を待機しています。
    中断	SPID は、ロックやラッチなどのリソースを待機しています。

  • sys.dm_exec_sessions.open_transaction_count
    この列は、このセッションで未処理トランザクションの数を示します。 この値が 0 より大きい場合、SPID は未処理トランザクション内にあり、トランザクション内の任意のステートメントによって取得されたロックを保持している場合があります。

  • sys.dm_exec_requests.open_transaction_count
    同様に、この列は、この要求での未処理トランザクションの数を示します。 この値が 0 より大きい場合、SPID は未処理トランザクション内にあり、トランザクション内の任意のステートメントによって取得されたロックを保持している場合があります。

  • sys.dm_exec_requests.wait_type、wait_time、および last_wait_type
sys.dm_exec_requests.wait_type が NULL の場合、要求は現在何も待機しておらず、last_wait_type 値は、要求が最後に検出された wait_type を示します。 sys.dm_os_wait_stats の詳細と、最も一般的な待機の種類の説明については、「sys.dm_os_wait_stats」を参照してください。 wait_time 値を使用して、要求が進行中であるかどうかを判別できます。 sys.dm_exec_requests テーブルに対するクエリが、sys.dm_exec_requests の前のクエリからの wait_time 値よりも小さい値を wait_time 列に返す場合、これは、前のロックが取得および解放され、現在新しいロックを待機していることを示します (ゼロ以外の wait_time を前提としています)。 これは、要求が待機しているリソースを表示する sys.dm_exec_requests 出力間で wait_resource を比較することで確認できます。

  • sys.dm_exec_requests.wait_resource この列は、ブロックされた要求が待機しているリソースを示します。 次の表に、一般的な wait_resource 形式とその意味を示します。

    リソース	フォーマット	例	説明
    Table	DatabaseID:ObjectID:IndexID	TAB: 5:261575970:1	この場合、データベース ID 5 は pubs サンプルデータベースであり、object_id 261575970 は titles テーブルであり、1 はクラスター化インデックスです。
    Page	DatabaseID:FileID:PageID	PAGE: 5:1:104	この場合、データベース ID 5 は pubs であり、ファイル ID 1 はプライマリ データ ファイルであり、104 ページは titles テーブルに属するページです。 ページが属する object_id を識別するには、動的管理関数 sys.dm_db_page_info を使用して、wait_resource からDatabaseID、FileId、PageId を渡します。
    キー	DatabaseID:Hobt_id (インデックス キーのハッシュ値)	KEY: 5:72057594044284928 (3300a4f361aa)	この場合、データベース ID 5 は Pubs であり、Hobt_ID 72057594044284928 は object_id 261575970 (titles テーブル) の index_id 2 に対応します。 sys.partitions カタログ ビューを使用して、hobt_id を特定の index_id および object_id に関連付けます。 インデックス キー ハッシュを特定のキー値にハッシュ解除する方法はありません。
    行	DatabaseID:FileID:PageID:Slot(row)	RID: 5:1:104:3	この場合、データベース ID 5 は pubs であり、ファイル ID 1 はプライマリ データ ファイルであり、104 ページは titles テーブルに属するページであり、スロット 3 はページ上の行の位置を示します。
    のコンパイル	DatabaseID:FileID:PageID:Slot(row)	RID: 5:1:104:3	この場合、データベース ID 5 は pubs であり、ファイル ID 1 はプライマリ データ ファイルであり、104 ページは titles テーブルに属するページであり、スロット 3 はページ上の行の位置を示します。

  • sys.dm_tran_active_transactionssys.dm_tran_active_transactions DMV には、コミットまたはロールバックを待機しているトランザクションの全体像を把握するために他の DMV に参加できる未処理トランザクションに関するデータが含まれています。 次のクエリを使用して、sys.dm_tran_session_transactions を含む他の DMV に参加している未処理トランザクションに関する情報を返します。 トランザクションの現在の状態、transaction_begin_time、およびその他の状況データを考慮して、それがブロックの原因である可能性があるかどうかを評価します。

    SELECT tst.session_id, [database_name] = db_name(s.database_id)
    , tat.transaction_begin_time
    , transaction_duration_s = datediff(s, tat.transaction_begin_time, sysdatetime()) 
    , transaction_type = CASE tat.transaction_type  WHEN 1 THEN 'Read/write transaction'
                                                    WHEN 2 THEN 'Read-only transaction'
                                                    WHEN 3 THEN 'System transaction'
                                                    WHEN 4 THEN 'Distributed transaction' END
    , input_buffer = ib.event_info, tat.transaction_uow     
    , transaction_state  = CASE tat.transaction_state    
                WHEN 0 THEN 'The transaction has not been completely initialized yet.'
                WHEN 1 THEN 'The transaction has been initialized but has not started.'
                WHEN 2 THEN 'The transaction is active - has not been committed or rolled back.'
                WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
                WHEN 4 THEN 'The commit process has been initiated on the distributed transaction.'
                WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
                WHEN 6 THEN 'The transaction has been committed.'
                WHEN 7 THEN 'The transaction is being rolled back.'
                WHEN 8 THEN 'The transaction has been rolled back.' END 
    , transaction_name = tat.name, request_status = r.status
    , tst.is_user_transaction, tst.is_local
    , session_open_transaction_count = tst.open_transaction_count  
    , s.host_name, s.program_name, s.client_interface_name, s.login_name, s.is_user_process
    FROM sys.dm_tran_active_transactions tat 
    INNER JOIN sys.dm_tran_session_transactions tst  on tat.transaction_id = tst.transaction_id
    INNER JOIN Sys.dm_exec_sessions s on s.session_id = tst.session_id 
    LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
    CROSS APPLY sys.dm_exec_input_buffer(s.session_id, null) AS ib;
    

  • その他の列

    sys.dm_exec_sessions と sys.dm_exec_request の残りの列は、問題の根本に関する洞察も提供できます。 それらの有用性は、問題の状況によって異なります。 たとえば、問題が特定のクライアント (hostname)、特定のネットワーク ライブラリ (client_interface_name) でのみ発生するかどうか、SPID によって送信された最後のバッチが sys.dm_exec_sessions の last_request_start_time であった場合、 sys.dm_exec_requests などの start_time を使用して要求が実行されていた期間を判別できます。

一般的なブロック シナリオ

次の表は、一般的な現象を考えられる原因にマップしています。

wait_type、open_transaction_count、および status 列は、sys.dm_exec_request によって返される情報を参照し、他の列は sys.dm_exec_sessions によって返される場合があります。 "Resolves?" 列は、ブロックが単独で解決されるかどうか、またはコマンドを使用して KILL セッションを強制終了するかどうかを示します。 詳細については、「KILL (Transact-SQL)」を参照してください。

詳細なブロック シナリオ

シナリオ 1．実行時間の長い通常の実行クエリによって発生するブロック

このシナリオでは、アクティブに実行されているクエリがロックを獲得し、ロックが解放されていない状態です(トランザクション分離レベルの影響を受けます)。 そのため、他のセッションはロックが解除されるまで待機することになります。

解決策:

このタイプのブロック問題の解決策は、クエリを最適化する方法を探すことです。 実際、このクラスのブロック問題はパフォーマンスの問題に過ぎず、そのための処理が必要になります。 実行時間の遅い特定のクエリのトラブルシューティングについては、「SQL Server の実行時間の遅いクエリをトラブルシューティングする方法」を参照してください。 詳細については、「パフォーマンスの監視とチューニング」を参照してください。

クエリ ストア (SQL Server 2016 で導入) から SSMS に組み込まれたレポートは、最もコストのかかるクエリ、最適でない実行プランを特定するための非常に推奨され、価値のあるツールでもあります。

他のユーザーをブロックしていて最適化できない実行時間の長いクエリがある場合は、OLTP 環境から専用のレポート システムに移動するか、 Always On 可用性グループを使用して、データベースの読み取り専用レプリカを同期することを検討してください。

シナリオ 2. コミットされていないトランザクションを持つスリープ状態の SPID によって発生するブロック

このタイプのブロックは、多くの場合、スリープ状態またはコマンドを待機しているが、トランザクション入れ子のレベル (@@TRANCOUNT、sys.dm_exec_requests の open_transaction_count) がゼロより大きい SPID によって識別できます。 これは、アプリケーションでクエリ タイムアウトが発生した場合や、必要な数の ROLLBACK ステートメントや COMMIT ステートメントも発行せずにキャンセルを発行した場合に発生する可能性があります。 SPID は、クエリ タイムアウトまたはキャンセルを受け取ると、現在のクエリとバッチを終了しますが、トランザクションを自動的にロールバックまたはコミットすることはありません。 SQL Server は 1 つのクエリがキャンセルされたためにトランザクション全体をロールバックする必要があると想定できないため、アプリケーションがこれを担当します。 クエリ タイムアウトまたはキャンセルは、拡張イベント セッションで SPID の ATTENTION シグナル イベントとして表示されます。

コミットされていない明示的なトランザクションを示すには、次のクエリを発行します。

CREATE TABLE #test (col1 INT);
INSERT INTO #test SELECT 1;
GO
BEGIN TRAN
UPDATE #test SET col1 = 2 where col1 = 1;

次に、同じウィンドウでこのクエリを実行します。

SELECT @@TRANCOUNT;
ROLLBACK TRAN
DROP TABLE #test;

2 番目のクエリの出力は、トランザクションの入れ子のレベルが 1 であることを示します。 トランザクションで取得されたすべてのロックは、トランザクションがコミットまたはロールバックされるまで保持されます。 アプリケーションでトランザクションを明示的に開いてコミットした場合、通信またはその他のエラーによってセッションとそのトランザクションが未処理状態のままになることがあります。

sys.dm_tran_active_transactions に基づくこの記事の前半のスクリプトを使用して、インスタンス全体で現在コミットされていないトランザクションを識別します。

解決方法:

• さらに、このクラスのブロック問題もパフォーマンスの問題である可能性があり、そのための処理が必要になります。 クエリの実行時間を減らすことができる場合、クエリのタイムアウトまたはキャンセルは発生しません。 アプリケーションでタイムアウトまたはキャンセルのシナリオが発生した場合に処理できることが重要ですが、クエリのパフォーマンスを調べることも利点があります。

• アプリケーションでトランザクションの入れ子のレベルを適切に管理する必要があります。または、この方法でクエリをキャンセルした後にブロックの問題が発生する場合があります。 次の状況について検討しましょう。

  • クライアント アプリケーションのエラー ハンドラーで、クライアント アプリケーションでトランザクションが未処理でない場合でも、エラーの後に IF @@TRANCOUNT > 0 ROLLBACK TRAN を実行します。 バッチ中に呼び出されたストアド プロシージャがクライアント アプリケーションの知らないうちにトランザクションを開始した可能性があるため、未処理トランザクションを確認する必要があります。 クエリのキャンセルなどの特定の条件により、プロシージャが現在のステートメントを超えて実行できなくなるため、プロシージャに IF @@ERROR <> 0 を確認してトランザクションを中止するロジックがある場合でも、このような場合にこのロールバック コードは実行されません。

  • Web ベースのアプリケーションなど、接続を開いて接続をプールに解放して戻す前にいくつかのクエリを実行するアプリケーションで接続プールが使用されている場合、接続プールを一時的に無効にすると、クライアント アプリケーションがエラーを適切に処理するように変更されるまで問題が軽減される場合があります。 接続プールを無効して、接続を解放すると、SQL Server 接続が物理的に切断され、未処理トランザクションがサーバーによってロールバックされます。

  • 接続、またはトランザクションを開始し、エラー後にクリーンアップされないストアド プロシージャでは、SET XACT_ABORT ON を使用します。 実行時エラーが発生した場合、この設定は未処理トランザクションをすべて中止し、クライアントに制御を戻します。 詳細については、「SET XACT_ABORT (Transact-SQL)」を参照してください。

シナリオ 3. 対応するクライアント アプリケーションが完了するまですべての結果行をフェッチしなかった SPID によって発生するブロック

サーバーにクエリを送信した後、すべてのアプリケーションで、すべての結果行をすぐに完了までフェッチする必要があります。 アプリケーションですべての結果行をフェッチしない場合は、ロックをテーブルに残し、他のユーザーをブロックできます。 SQL ステートメントを透過的にサーバーに送信するアプリケーションを使用している場合、アプリケーションですべての結果行をフェッチする必要があります。 そうしない場合 (およびそうするように構成できない場合)、ブロックの問題を解決できない場合があります。 この問題を回避するために、動作の悪いアプリケーションを、メイン OLTP データベースとは別のレポート データベースまたは意思決定サポート データベースに制限できます。

解決策:

結果のすべての行を完了にフェッチするには、アプリケーションを書き換える必要があります。 これにより、サーバー側のページングを実行するためのクエリの ORDER BY 句での OFFSET と FETCH の使用は除外されません。

シナリオ 4. 分散クライアント/サーバー デッドロックによって発生するブロック

従来のデッドロックとは異なり、分散デッドロックは RDBMS ロック マネージャーを使用して検出できません。 これは、デッドロックに関係するリソースの 1 つだけが SQL Server ロックであるためです。 デッドロックのもう一方の側はクライアント アプリケーション レベルにあり、その上に SQL Server 制御はありません。 これを行う方法と、アプリケーションで回避できる方法の 2 つの例を次に示します。

例 A. 1 つのクライアント スレッドを使用したクライアント/サーバー分散デッドロック

クライアントに複数の開かれている接続があり、1 つの実行スレッドがある場合、次の分散デッドロックが発生する可能性があります。 簡潔にするために、ここで使用する用語 dbproc はクライアント接続構造を指します。

 SPID1------blocked on lock------->SPID2
   /\ (waiting to write results back to client)
   | 
   | |
   | | Server side
   | ================================|==================================
   | <-- single thread --> | Client side
   | \/
   dbproc1 <------------------- dbproc2
   (waiting to fetch (effectively blocked on dbproc1, awaiting
   next row) single thread of execution to run)

上記の場合、1 つのクライアント アプリケーション スレッドに 2 つの開かれている接続があります。 dbproc1 に対して SQL 操作を非同期的に送信します。 つまり、続行する前に呼び出しが戻るまで待機しないことを意味します。 その後、アプリケーションで dbproc2 に対して別の SQL 操作を送信し、返されたデータの処理を開始する結果を待機します。 データが戻り始めると (どの dbproc が最初に応答するか、つまりこれが dbproc1 であると想定します)、その dbproc で返されたすべてのデータが完了するまで処理されます。 これは、SPID1 が SPID2 によって保持されているロックでブロックされるまで dbproc1 から結果をフェッチします (2 つのクエリがサーバー上で非同期的に実行されているため)。 この時点で、dbproc1 は、より多くのデータを無期限に待機します。 SPID2 はロックではブロックされませんが、クライアント dbproc2 にデータを送信しようとします。 ただし、アプリケーションの単一の実行スレッドが dbproc1 によって使用されているため、アプリケーション層の dbproc1 では dbproc2 が事実上ブロックされます。 これにより、SQL Server が検出または解決できないデッドロックが発生します。これは、関連するリソースの 1 つだけが SQL Server リソースであるためです。

例 B. 接続ごとのスレッドを使用したクライアント/サーバー分散デッドロック

クライアント上の接続ごとに個別のスレッドが存在する場合でも、次に示すように、この分散デッドロックのバリエーションが引き続き発生することがあります。

SPID1------blocked on lock-------->SPID2
  /\ (waiting on net write) Server side
  | |
  | |
  | INSERT |SELECT
  | ================================|==================================
  | <-- thread per dbproc --> | Client side
  | \/
  dbproc1 <-----data row------- dbproc2
  (waiting on (blocked on dbproc1, waiting for it
  insert) to read the row from its buffer)

このケースは例 A と似ていますが、dbproc2 と SPID2 が SELECT ステートメントを実行して、一度に 1 行ずつ処理を実行し、バッファーを介して各行を dbproc1 に渡して同じテーブルで INSERT、UPDATE、または DELETE ステートメントを実行する点が異なります。 最終的に、SPID1 (INSERT、UPDATE、または DELETE を実行) は、SPID2 (SELECT を実行) によって保持されているロックでブロックされます。 SPID2 は、結果行をクライアント dbproc2 に書き込みます。 次に、Dbproc2 はバッファー内の行を dbproc1 に渡そうとしますが、dbproc1 がビジー状態であることが検出されます (SPID2 でブロックされている現在の INSERT を終了するために SPID1 で待機してブロックされます)。 この時点で、SPID (SPID1) が SPID2 によってデータベース レベルでブロックされている dbproc1 によって、アプリケーション層で dbproc2 がブロックされます。 繰り返しますが、この結果、関係するリソースの 1 つだけが SQL Server リソースであるため、SQL Server が検出または解決できないデッドロックが発生します。

例 A と B はどちらも、アプリケーション開発者が認識する必要がある基本的な問題です。 これらのケースを適切に処理するようにアプリケーションをコーディングする必要があります。

解決策:

クエリ タイムアウトが指定されると、分散デッドロックが発生した場合は、タイムアウトが発生すると壊れます。 クエリ タイムアウトの使用の詳細については、接続プロバイダーのドキュメントを参照してください。

シナリオ 5. ロールバック状態のセッションによって発生するブロック

強制終了された、またはユーザー定義のトランザクションの外部でキャンセルされたデータ変更クエリは、ロールバックされます。 これは、クライアント ネットワーク セッションの切断の副作用として、または要求がデッドロックの対象として選択された場合にも発生する可能性があります。 これは、多くの場合、sys.dm_exec_requests の出力を確認することで識別できます。この出力は、ROLLBACK command を示している場合があり、percent_complete 列に進行状況が示されていることがあります。

強制終了された、またはユーザー定義のトランザクションの外部でキャンセルされたデータ変更クエリは、ロールバックされます。 これは、クライアント コンピューターの再起動とそのネットワーク セッションの切断の副作用としても発生する可能性があります。 同様に、デッドロックの対象として選択されたクエリはロールバックされます。 多くの場合、データ変更クエリは、変更が最初に適用された場合よりも速くロールバックできません。 たとえば、DELETE、INSERT、または UPDATE ステートメントが 1 時間実行されていた場合、ロールバックするのに少なくとも 1 時間かかる可能性があります。 これは、行われた変更をロールバックする必要がある、またはデータベース内のトランザクションと物理的な整合性が損なわれるため、想定される動作です。 これは必ず発生するため、SQL Server では、SPID をゴールデン状態またはロールバック状態でマークします (つまり、デッドロックの対象として、強制終了または選択することはできません)。 これは、多くの場合、ROLLBACK コマンドを示している可能性のある sp_who の出力を確認することで識別できます。 sys.dm_exec_sessions の status 列は、ROLLBACK 状態を示します。

解決策:

行われた変更のロールバックが完了するまでセッションを待つ必要があります。

この操作の途中でインスタンスがシャットダウンした場合、データベースは再起動時に回復モードになり、すべての未処理トランザクションが処理されるまでアクセスできなくなります。 スタートアップ時の回復には、トランザクションごとに実行時の回復と基本的に同じ時間がかかり、この期間中はデータベースにアクセスできません。 したがって、サーバーを強制的にダウンさせて SPID をロールバック状態に修正することは、多くの場合、逆効果になります。 高速データベース復旧が有効になっている SQL Server 2019 では、この問題は発生しません。

この状況を回避するには、OLTP システムのビジー時間に、大規模なバッチ書き込み操作やインデックスの作成またはメンテナンス操作を実行しないでください。 可能であれば、アクティビティが少ない期間にこのような操作を実行します。

シナリオ 6: 孤立したトランザクションによって発生するブロック

これは一般的な問題のシナリオであり、シナリオ 2 と部分的に重複しています。 クライアント アプリケーションが停止した場合、クライアント ワークステーションが再起動された場合、またはバッチ中止エラーが発生した場合、これらすべてがトランザクションを開いたままになる可能性があります。 この状況は、アプリケーションがCATCHまたはFINALLYブロックでトランザクションをロールバックしない場合、またはその他の方法でこの状況を処理しない場合に発生する可能性があります。

このシナリオでは、SQL バッチの実行がキャンセルされても、アプリケーションはSQLトランザクションを開いたままにしています。 SQL Server インスタンスの観点から見ると、クライアントは引き続き存在しているように見え、取得されたロックは引き続き保持される場合があります。

孤立した接続を示すには、次のクエリを実行します。これは、存在しないテーブルにデータを挿入することによってバッチ中止エラーをシミュレートします。

CREATE TABLE #test2 (col1 INT);
INSERT INTO #test2 SELECT 1;
go
BEGIN TRAN
UPDATE #test2 SET col1 = 2 where col1 = 1;
INSERT INTO #NonExistentTable values (10)

次に、同じウィンドウでこのクエリを実行します。

SELECT @@TRANCOUNT;

2 番目のクエリの出力は、トランザクションの入れ子のレベルが 1 であることを示します。 トランザクションで取得されたすべてのロックは、トランザクションがコミットまたはロールバックされるまで保持されます。 バッチはクエリによって既に中止されているため、それを実行するアプリケーションは、まだ開いている接続をクリーンアップせずに、同じセッションで他のクエリを実行し続ける可能性があります。 ロックはセッションが強制終了されるか、SQL Server インスタンスが再起動されるまで保持されます。

解決方法:

• この状態を防ぐ最善の方法は、特に予期しない終了の場合に、アプリケーション エラー/例外処理を改善することです。 例外が発生した場合は、アプリケーションコードに Try-Catch-Finally ブロックを使用し、トランザクションをロールバックするようにしてください。
• セッションや、接続を開始し、エラー後にクリーンアップされないストアドプロシージャでは、SET XACT_ABORT ONを使用することを検討してください。 実行時エラーでバッチが中断された場合、この設定により、開いている接続はすべて自動的にロールバックされ、クライアントに制御を戻します。 詳細については、「SET XACT_ABORT (Transact-SQL)」を参照してください。
• リソースを適切にクリーンアップせずに切断されたクライアント アプリケーションの孤立した接続を解決するには、KILL コマンドを使用して KILL を終了します。 参考までに、「KILL (Transact-SQL)」を参照してください。

KILL コマンドは、SPID 値を入力として受け取ります。 たとえば、SPID 9 を強制終了するには、次のコマンドを発行します。

KILL 99

関連項目

• クエリ ストアを使用したパフォーマンスの監視
• SQL Server でのロックのエスカレーションによるブロック問題の解決
• トランザクションのロックおよび行のバージョン管理ガイド
• トランザクション分離レベルの設定
• クイック スタート: SQL Server の拡張イベント