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 では、ブロックは、あるセッションによって、特定のリソースのロックが保持されており、2 つ目の SPID によって、同じリソースに対して競合するロックの種類の取得が試みられたときに発生します。 通常、最初の SPID によってリソースがロックされる期間はわずかです。 所有しているセッションでロックが解放されると、2 つ目の接続によって、自由にそのリソースへの独自のロックが取得され、処理が続行されます。 ここで説明するブロックは通常の動作であり、1 日の間に何度も発生する可能性があり、システムのパフォーマンスに顕著な影響はありません。

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

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

• データベース エンジンのロック
• ロックおよび行のバージョン管理のカスタマイズ
• ロック モード
• ロックの互換性
• データベース エンジンでの行のバージョン管理に基づく分離レベル
• トランザクション

システム パフォーマンスに有害な影響を与えるほどロックとブロックが持続する場合、通常、次のいずれかの理由によります:

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

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

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

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

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

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

適切なアプリケーションとクエリの設計により、SQL Server は 1 台のサーバーで多数の同時ユーザーをサポートでき、ほとんどブロックしません。

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

発生しているブロックの状況に関係なく、ロックのトラブルシューティングの方法は同じです。 これらの論理的な区別によって、この記事の残りの構成が決まります。 概念は、ヘッド ブロッカーを見つけて、そのクエリが何を実行しているかと、なぜそれがブロックしているかを特定することです。 問題のあるクエリが特定されたら (つまり、長時間ロックを保持しているもの)、次の手順は、ブロックが発生した理由を分析して判断することです。 その理由を理解したら、クエリとトランザクションを再設計することで変更を加えることができます。

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

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

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

3. 長時間のブロックが発生する原因を分析して理解する

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

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

ブロック情報の収集

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

1 つ目は、動的管理オブジェクト (DMO) のクエリを実行し、経時的な比較の結果を保存することです。 この記事で参照しているオブジェクトには、動的管理ビュー (DMV) と、動的管理関数 (DMF) があります。

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

DMV からの情報の収集

ブロックをトラブルシューティングするために DMV を参照することには、ブロック チェーンのヘッドにある SPID (セッション ID) と SQL ステートメントを特定するという目標があります。 ブロックされている犠牲者の SPID を見つけます。 SPID が別の SPID によってブロックされている場合、リソースを所有している SPID (ブロックしている SPID) を調査します。 その所有者の SPID もブロックされていますか。 チェーンをたどってヘッド ブロッカーを見つけて、そのロックが保持されている理由を調査できます。

これを行うには、次のいずれかの方法を使用できます。

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

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

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 でアクティブに実行されている必要があります。

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

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;

• 実行時間の長いトランザクションまたはコミットされていないトランザクションをキャッチするには、別の DMV セットを使用して、 sys.dm_tran_database_transactions、 sys.dm_tran_session_transactions、 sys.dm_exec_connections、 sys.dm_exec_sql_textなど、現在開いているトランザクションを表示します。 トランザクションの追跡に関連付けられているいくつかの 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 の Go-to ツールは、PSSDiag データ コレクターを使用することです。 このツールでは、"SQL Server Perf Stats" を使用して、上記で参照されている DMV から結果セットを時間の経過と同時に収集します。 このツールは絶えず進化しており、GitHub の DiagManager の最新のパブリック バージョンを確認してください。

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

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

SQL Server でトレースをキャプチャする方法は 2 つあります。 Extended Events (XEvents) および Profiler トレース。 ただし、SQL Server プロファイラーを使用する SQL トレースは非推奨です。 XEvents は、より汎用性が高く、観察されたシステムへの影響が少ない、新しい優れたトレース プラットフォームであり、そのインターフェイスは SSMS に統合されています。

SSMS で開始する準備ができている事前に作成された拡張イベント セッションがあります。XEvent Profiler のメニューの下のオブジェクト エクスプローラーに表示されます。 詳細については、「 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
  • ログイン
  • Logout

一般的なブロック シナリオの特定と解決

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

ブロック データの分析

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

• DMV sys.dm_exec_requests と sys.dm_exec_sessions の出力で、ブロック チェーンのヘッドにある SPID に関する情報を調べます。 次の列を探します。

  • sys.dm_exec_requests.status

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

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

  • sys.dm_exec_sessions.open_transaction_count

    この列には、このセッションで開いているトランザクションの数が表示されます。 この値が 0 より大きい場合、SPID は開いているトランザクション内にあり、トランザクション内のいずれかのステートメントによって取得されたロックを保持している可能性があります。 開いているトランザクションは、現在アクティブなステートメントまたは過去に実行され、それ以上アクティブではないステートメント要求によって作成された可能性があります。

  • sys.dm_exec_requests.open_transaction_count

    同様に、この列には、この要求で開いているトランザクションの数が表示されます。 この値が 0 より大きい場合、SPID は開いているトランザクション内にあり、トランザクション内のアクティブなステートメントによって取得されたロックを保持している可能性があります。 sys.dm_exec_sessions.open_transaction_countとは異なり、アクティブな要求がない場合、この列には 0 が表示されます。

  • 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 テーブルに対するクエリで、wait_time 列に、sys.dm_exec_requests の前のクエリからの wait_time 値より少ない値が返された場合、これは前のロックが取得され、解放されており、現在新しいロックを待機中であることを示しています (wait_time がゼロ以外であると想定します)。 これは sys.dm_exec_requests の出力間で wait_resource を比較して確認できます。これにより、要求で待機中のリソースが表示されます。

  • sys.dm_exec_requests.wait_resource

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

    リソース	Format	例	説明
    テーブル	DatabaseID:ObjectID:IndexID	TAB:5:261575970:1	この場合、データベース ID 5 は pubs サンプル データベースで、 object_id 261575970はタイトル テーブル、1 はクラスター化インデックスです。
    ページ	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 の index_id 2 に対応します (titles テーブル)。 sys.partitions カタログ ビューを使用して、特定のindex_idとobject_idにhobt_idを関連付けます。 インデックス キーのハッシュを特定のキー値に非ハッシュ化する方法はありません。
    行	DatabaseID:FileID:PageID:Slot(row)	RID:5:1:104:3	この場合、データベース ID 5 は pubs、ファイル ID 1 はプライマリ データ ファイル、ページ 104 は titles テーブルに属するページ、スロット 3 はページ上の行の位置を示します。
    Compile	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 によって発生するブロック

この種類のブロックは、多くの場合、0 より大きいトランザクションの入れ子レベル (@@TRANCOUNT、 open_transaction_count sys.dm_exec_requests) を持つコマンドをスリープ状態または待機している 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 つの実行スレッドがある場合は、次の分散デッドロックが発生する可能性があります。 ここで使用 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 にデータを送信しようとします。 ただし、dbproc2 は、アプリケーションの実行の単一スレッドが dbproc1 によって使用されているため、アプリケーション 層の dbproc1 で実質的にブロックされます。 これにより、関係するリソースの 1 つだけが SQL Server リソースであるため、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 ステートメントを実行し、同じテーブル上の INSERT、 UPDATE、または DELETE ステートメントに対してバッファーを介して各行を dbproc1 に渡すという意図を持ちます。 最終的に、SPID1 ( INSERT、 UPDATE、または DELETEの実行) は、SPID2 によって保持されているロック ( SELECTの実行) でブロックされます。 SPID2 は、クライアント dbproc2 に結果行を書き込みます。 その後、Dbproc2 はバッファー内の行を dbproc1 に渡そうとしますが、dbproc1 がビジー状態であることを検出します (SPID1 で現在の INSERTが完了するのを待機しています。SPID2 ではブロックされます)。 この時点で、dbproc2 は、SPID (SPID1) が SPID2 によってデータベース レベルでブロックされている dbproc1 によってアプリケーション 層でブロックされます。 ここでも、関係するリソースの 1 つだけが SQL Server リソースであるため、SQL Server で検出または解決できないデッドロックが発生します。

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

解決策:

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

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

ユーザー定義トランザクションの外部で強制終了または取り消されたデータ変更クエリはロールバックされます。 これは、クライアント ネットワーク セッションの切断の副作用として、または要求がデッドロックの犠牲者として選択された場合にも発生することがあります。 これは多くの場合、ROLLBACK commandを示す可能性があるsys.dm_exec_requestsの出力を観察することによって識別でき、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: 孤立したトランザクションによって発生するブロック

これは一般的な問題のシナリオであり、 Scenario 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 コマンドを使用して SPID を終了します。 詳細については、「 KILL (Transact-SQL)」を参照してください。

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

KILL 99

関連項目

• クエリ ストアを使用したパフォーマンスの監視
• SQL Server でのロックのエスカレーションによって発生するブロックの問題を解決する
• トランザクションのロックおよび行のバージョン管理ガイド
• SET TRANSACTION ISOLATION LEVEL
• クイック スタート:SQL Server の拡張イベント