SQL Server のブロックの問題の理解と解決

適用対象: SQL Server Azure SQL Managed Instance

元の KB 番号: 224453

まとめ

ブロックはロックベースのリレーショナルデータベースでは通常どおりですが、永続的または長時間のブロッキングではスループットが低下し、アプリケーションのタイムアウトが発生します。この記事では、SQL Serverのブロックとは何か、動的管理ビュー (DMV) と拡張イベントからブロックデータをキャプチャする方法、およびヘッドブロッカーを識別する方法について説明します。その後、最も一般的なブロックシナリオとその解決策について説明します。

この記事では、接続という用語は、データベースの単一のログオンセッションを指します。各接続は、多くの DMV でセッション ID (SPID) または session_id として表示されます。各 SPID は多くの場合、"プロセス" と呼ばれますが、個別のオペレーティングシステムプロセスではありません。 1 つのクライアント接続にサービスを提供するのは、サーバー側のリソースとデータ構造です。 1 つのクライアントアプリケーションに 1 つ以上の接続を設定できます。 SQL Serverの観点からは、多くのクライアントからの接続と 1 つのクライアントからの多くの接続は同じように扱われます。ソースクライアントに関係なく、1 つの接続によって、別の接続がブロックされる可能性があります。

Note

この記事では、Azure SQL Managed Instanceを含むSQL Serverインスタンスについて説明します。 Azure SQL Database でのブロックのトラブルシューティングに固有の情報については、「Azure SQL Database のブロックの問題を理解して解決する」を参照してください。

ブロックとは

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

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

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

ロックとブロックがシステムのパフォーマンスに悪影響を及ぼす時点まで維持される場合、次のいずれかの理由が原因で発生します。

SPID は、リソースを解放する前に、一連のリソースに対するロックを長期間保持します。この種類のブロックは、時間の経過と共に解決されますが、パフォーマンスが低下する可能性があります。
SPID は一連のリソースに対するロックを保持し、解放しません。この種類のブロックは、自動的に解決されず、影響を受けるリソースへのアクセスが無期限に妨げられます。

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

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

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

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

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

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

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

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

メインブロックセッション (ヘッドブロッカー) を特定します。
ブロックの原因となるクエリとトランザクションを検索します (長時間ロックを保持するもの)。
長時間のブロックが発生する理由を分析して理解します。
クエリとトランザクションを再設計して、ブロックの問題を解決します。

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

ブロック情報の収集

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

1 つ目の方法は、動的管理オブジェクト (DNO) に対してクエリを実行し、時間の経過に伴う比較のために結果を格納することです。この記事で参照しているオブジェクトには、動的管理ビュー (DMV) と、動的管理関数 (DMF) があります。

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

DMV からの情報の収集

ブロックのトラブルシューティングを行うには、DMV を使用して、ブロッキングチェーンと SQL ステートメントの先頭にある SPID (セッション ID) を識別します。ブロックされている被害者 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でアクティブに実行されている必要があることに注意してください。
このサンプルクエリを実行し、sys.dm_exec_sql_text または sys.dm_exec_input_buffer DMV を使用して、アクティブに実行されているクエリとそれらの現在の SQL バッチテキストまたは入力バッファーテキストを見つけます。 text の sys.dm_exec_sql_text 列によって返されるデータが NULL の場合、クエリは現在実行されていません。その場合、event_info の sys.dm_exec_input_buffer 列には、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;

実行時間の長いトランザクションまたはコミットされていないトランザクションをキャッチするには、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。この DMV は、要求で現在発生している SQL wait_typeに関する情報を返します。 sys.dm_exec_requests と同様に、sys.dm_os_waiting_tasks からはアクティブな要求のみが返されます。

Note

時間の経過に伴う集計された待機統計など、待機の種類の詳細については、DMV sys.dm_db_wait_statsを参照してください。

クエリによってどのロックが設定されているかに関するより詳細な情報については、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 つは、ロックが保持されている理由である可能性があります。トレースを使用すると、セッションが現在のトランザクション内で実行するすべてのコマンドを表示できます。

拡張イベント (XEvent) とプロファイラートレースの 2 つの方法を使用して、SQL Serverでトレースをキャプチャできます。ただし、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
- ログイン
- 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 テーブルに対するクエリが、sys.dm_exec_requestsの以前のクエリのwait_time値より小さい値をwait_time列に返す場合、この条件は、以前のロックが取得および解放され、新しいロックを待機していることを示します (0 以外の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 は titles テーブルであり、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` カタログビューを使用して、`hobt_id` を特定の `index_id` および `object_id` に関連付けます。インデックスキーのハッシュを特定のキー値に非ハッシュ化する方法はありません。
行	DatabaseID:FileID:PageID:Slot(row)	RID:5:1:104:3	この場合、データベース ID 5 は pubs、ファイル ID 1 はプライマリデータファイル、ページ 104 は titles テーブルに属するページ、スロット 3 はページ上の行の位置を示します。

sys.dm_tran_active_transactions sys.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 によって送信された最後のバッチが last_request_start_time の sys.dm_exec_sessions であった場合、 start_time などの sys.dm_exec_requests を使用して要求が実行されていた期間を判別できます。

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

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

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

シナリオ	Wait_type	Open_Tran	Status	解決するか	その他の現象
1	非NULL	>= 0	実行可能	はい。クエリが終了したとき。	`sys.dm_exec_sessions` では、`reads`、`cpu_time`、`memory_usage` 列は時間の経過と共に増加します。完了したときのクエリの実行時間は長くなります。
2	NULL	>0	休止中	いいえ。ただし、SPID は中止できます。	この SPID の拡張イベントセッションで、クエリのタイムアウトまたはキャンセルが発生したことを示すアテンションシグナルが表示される場合があります。
3	NULL	>= 0	実行可能	いいえ。クライアントによってすべての行がフェッチされるか、接続が閉じられるまで、解決されません。 SPID は中止できますが、最大で 30 秒かかることがあります。	open_transaction_count = 0 で、トランザクション分離レベルが既定 (READ COMMITTED) の間に SPID がロックを保持している場合は、これが原因である可能性があります。
4	場合により異なる	>= 0	実行可能	いいえ。クライアントによってクエリが取り消されるか、接続を閉じられるまで、解決されません。 SPID は中止できますが、最大で 30 秒かかることがあります。	ブロックチェーンのヘッドにある SPID の `hostname` の `sys.dm_exec_sessions` 列は、それによってブロックされている SPID の 1 つと同じになります。
5	NULL	>0	ロールバック	はい。	この SPID の拡張イベントセッションで、クエリのタイムアウトまたはキャンセルが発生したか、ロールバックステートメントが発行されたことを示すアテンションシグナルが表示される場合があります。
6	NULL	>0	休止中	最終的に。 Windows NT セッションがアクティブでなくなったと判断すると、接続が切断されます。	`last_request_start_time` の `sys.dm_exec_sessions` 値は、現在の時刻よりもはるかに前です。

ブロックのシナリオ (詳述)

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

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

解決策:

このブロックの問題を解決するには、クエリを最適化します。この種のブロッキングの問題は、パフォーマンスの問題である可能性があります。そのように扱います。特定の実行速度の遅いクエリのトラブルシューティングについては、SQL Server の実行速度の遅いクエリのトラブルシューティング方法に関するページを参照してください。詳細については、「パフォーマンスの監視とチューニング」を参照してください。

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

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

Note

クエリ実行中のブロックは、クエリのエスカレーション、つまり行またはページのロックがテーブルのロックにエスカレートされた場合のシナリオが原因である可能性があります。 Microsoft SQL Server は、ロックのエスカレーションを実行するタイミングを動的に決定します。ロックのエスカレーションを防ぐ最も簡単で安全な方法は、トランザクションを短くし、高価なクエリのロックのフットプリントを減らして、ロックのエスカレーションのしきい値を超えないようにすることです。過度のロックのエスカレーションの検出と防止の詳細については、「ロックのエスカレーションによるブロック問題の解決」を参照してください。

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

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

Note

接続は、接続プールから再利用されるまでリセットされないので、ユーザーはトランザクションを開いて接続プールへの接続を解放できますが、数秒間は再利用できない可能性があります。その間、トランザクションは未処理になります。接続が再利用されない場合、接続がタイムアウトするとトランザクションが中止され、接続プールから削除されます。したがって、クライアントアプリケーションがエラーハンドラーでトランザクションを中止するか、SET XACT_ABORT ON を使用して、この潜在的な遅延を回避することが最適です。

注意事項

SET XACT_ABORT ON に続いて、エラーの原因となるステートメントに続く T-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 が事実上ブロックされます。この状況では、関係するリソースの 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 ステートメントを実行して、一度に 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. ロールバック状態のセッションによって発生するブロック

ユーザー定義トランザクションの外部でデータ変更クエリを強制終了または取り消すと、クエリはロールバックされます。このロールバックは、クライアントネットワークセッションの切断、クライアントコンピューターの再起動、またはデッドロックの対象として選択されている要求の副作用としても発生する可能性があります。多くの場合、変更が最初に適用されたよりも速くデータ変更クエリをロールバックすることはできません。たとえば、 DELETE、 INSERT、または UPDATE ステートメントが 1 時間実行された場合、ロールバックには少なくとも 1 時間かかることがあります。この動作が予想されるのは、変更がロールバックされない場合、データベース内のトランザクション整合性と物理整合性が損なわれるためです。このロールバックは完了する必要があるため、SQL Serverはセッションを KILLED/ROLLBACK 状態にマークし、セッションを再度強制終了したり、デッドロックの対象として選択したりすることはできません。多くの場合、command列がKILLED/ROLLBACK報告し、percent_complete列に進行状況が表示されるsys.dm_exec_requestsの出力を観察することで、この状態を特定できます。

Note

高速データベース復旧機能が有効になっている場合、長時間のロールバックはまれです。この機能は SQL Server 2019 で導入されました。

解決策:

セッションが変更のロールバックを完了するまで待ちます。

この操作の途中でインスタンスをシャットダウンした場合、データベースは再起動時に復旧モードになり、開いているすべてのトランザクションが処理されるまでアクセスできません。スタートアップ時の回復には、トランザクションごとに実行時の回復と基本的に同じ時間がかかり、この期間中はデータベースにアクセスできません。したがって、ロールバック状態の 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

Note

KILL コマンドのチェック間隔が原因で、KILL コマンドが完了するまでに最大 30 秒かかる場合があります。

フィードバック

このページはお役に立ちましたか?

Last updated on 2026-08-04

SQL Server のブロックの問題の理解と解決

まとめ

ブロックとは

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

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

ブロック情報の収集

DMV からの情報の収集

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

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

ブロック データの分析

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

ブロックのシナリオ (詳述)

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

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

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

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

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

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

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

シナリオ 6. 孤立した接続によって発生するブロック

関連するコンテンツ

フィードバック

その他のリソース

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

ブロックデータの分析

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

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

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

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