次の方法で共有

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 つの接続によって、別の接続がブロックされる可能性があります。

Note

この記事では、Azure SQL Managed Instances を含む、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 台のサーバーで何千人もの同時ユーザーをサポートすることができ、ブロックはほとんど発生しません。

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

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

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

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

  • このサンプル クエリを実行し、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;

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 からはアクティブな要求のみが返されます。

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 のブロックの問題を徹底的に調査する必要があります。 たとえば、セッションによって、トランザクション内で複数のステートメントが実行される場合、最後に送信されたステートメントのみが表示されます。 しかし、それ以前のいずれかのステートメントに、ロックがまだ保持されている理由がある場合があります。 トレースにより、現在のトランザクション内でセッションによって実行されたすべてのコマンドを確認できます。

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
    • ログイン
    • 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 は 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 はページ上の行の位置を示します。
      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)」を参照してください。

シナリオ Wait_type Open_Tran Status 解決するか その他の現象
1 NOT 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 の sys.dm_exec_sessions の hostname 列は、それによってブロックされている SPID の 1 つと同じになります。
5 NULL >0 ロールバック はい。 この SPID の拡張イベント セッションで、クエリのタイムアウトまたはキャンセルが発生したか、ロールバック ステートメントが発行されたことを示すアテンション シグナルが表示される場合があります。
6 NULL >0 休止中 最終的に。 Windows NT セッションがアクティブでなくなったと判断すると、接続が切断されます。 sys.dm_exec_sessions の last_request_start_time 値は、現在の時刻よりもはるかに前です。

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

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

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

解決策:

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

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

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

Note

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

シナリオ 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)」をご覧ください。

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 が事実上ブロックされます。 これにより、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 状態を示します。

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 秒かかる場合があります。

関連項目