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

適用対象: SQL Server (すべてのサポートされているバージョン)、Azure SQL マネージ インスタンス

元の KB 番号:   224453

目標

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

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

注意

この記事では、Azure SQL Serverマネージド インスタンスを含む、SQLに焦点を当てしています。 トラブルシューティングのブロックに関する詳細については、「Azure SQL Databaseの問題を理解して解決Azure SQL Databaseを参照してください

ブロックとは

ブロックは、ロックベースの同時実行を持つリレーショナル データベース管理システム (RDBMS) の避けられない設計上の特徴です。 前述したように、SQL Server では、あるセッションが特定のリソースのロックを保持し、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 つ目は、動的管理オブジェクト (DMOs) にクエリを実行し、時間の間に比較するために結果を格納する方法です。 この記事で参照される一部のオブジェクトは、動的管理ビュー (DMV) であり、一部は動的管理機能 (DMF) です。

2 つ目は、拡張イベント(XEvents) またはプロファイラー SQLを使用して、実行中のイベントをキャプチャする方法です。 このSQLおよびSQL Server Profilerは廃止されたので、このトラブルシューティング ガイドは XEvents に焦点を当てる予定です。

DMV からの情報の収集

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

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

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

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

さらに詳細なクエリ ベースのメソッドは、DMV を使用して使用することもできます。

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

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

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_requestssys.dm_exec_sessions クエリと次のクエリの間に構築します。 によって返されるのを念頭に置いて、クエリがアクティブに実行されている必要 sys.dm_exec_requests SQL Server。

  • このサンプル クエリを実行して、アクティブに実行されているクエリとその現在の SQL バッチ テキストまたは入力バッファー テキストを、sys.dm_exec_sql_text または sys.dm_exec_input_buffer DMV を使用して検索します。 列によって返されるデータが NULL の場合 text sys.dm_exec_sql_text 、クエリは現在実行されません。 その場合、の列には、エンジンに渡された最後のコマンド event_info sys.dm_exec_input_buffer 文字列SQLされます。 また、このクエリを使用して、他のセッションをブロックしているセッションを識別することもできます。その他のセッションごとにブロックsession_idsを含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;
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];
  • 参照sys.dm_os_waiting_tasksのスレッド/タスク層にあるSQL Server。 これにより、要求が現在発生SQL wait_typeに関する情報が返されます。 同様 sys.dm_exec_requests に、アクティブな要求だけがによって返されます sys.dm_os_waiting_tasks

注意

時間の間に集計された待機統計を含む待機の種類の詳細については、「DMV sys.dm_db_wait_stats」 を参照してください

  • クエリによって sys.dm_tran_locks されたロックの詳細については、DMV を使用します。 この DMV は、実稼働環境インスタンスで大量のデータをSQL Server、現在保持されているロックを診断する場合に役立ちます。

INNER JOIN がオンの場合、次のクエリは出力を現在ブロックされている要求、待機状態、およびロックにのみ sys.dm_os_waiting_tasks 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 から結果セットを収集します。 このツールは絶えず進化していますので、最新のパブリック バージョンのDiagManager をGitHub。

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

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

次の 2 つの方法でトレースをキャプチャSQL Server。拡張イベント (XEvents) とプロファイラー トレース。 ただし、SQLを使用するSQL Server Profilerは推奨されません。 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 つ以上のセッションが書き込み操作を実行しています。

  • DMV の出力と、ブロック チェーンのヘッドにある sys.dm_exec_requests sys.dm_exec_sessions SPID に関する情報を調べてください。 次の列を調べてください。

    • sys.dm_exec_requests.status
      この列には、特定の要求の状態が表示されます。 通常、スリープ状態は、SPID が実行を完了し、アプリケーションが別のクエリまたはバッチを送信するのを待つことを示します。 実行可能または実行中の状態は、SPID が現在クエリを処理中かどうかを示します。 次の表に、さまざまな状態値の簡単な説明を示します。
    状態 意味
    背景 SPID は、デッドロック検出、ログ ライター、チェックポイントなどのバックグラウンド タスクを実行しています。
    スリープ状態 SPID は現在実行中ではありません。 これは通常、SPID がアプリケーションからのコマンドを待っている状態を示します。
    実行中 SPID は現在スケジューラで実行されています。
    Runnable 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_typewait_time、および last_wait_type
      NULL の場合、要求は現在何も待機していません。値は、要求が最後に検出 sys.dm_exec_requests.wait_type last_wait_type wait_type されたことを示します。 最も一般的な sys.dm_os_wait_stats 待機の種類の詳細と説明については、「sys.dm_os_wait_stats」 を 参照してください。 この wait_time 値を使用して、要求が進行中かどうかを判断できます。 テーブルに対するクエリが、前のクエリの値より小さい列の値を返す場合、これは、以前のロックが取得され解放され、新しいロックを待機中 sys.dm_exec_requests wait_time wait_time sys.dm_exec_requests ( wait_time ゼロ以外の場合) を示します。 これは、要求が待機しているリソースを表示する出力間を比較 wait_resource sys.dm_exec_requests することで確認できます。

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

    Resource Format 説明
    Table DatabaseID:ObjectID:IndexID TAB: 5:261575970:1 この場合、データベース ID 5 は pubs サンプル データベース object_id 、261575970 はタイトル テーブル、1 はクラスター化インデックスです。
    Page DatabaseID:FileID:PageID PAGE: 5:1:104 この場合、データベース ID 5 は pubs、ファイル ID 1 はプライマリ データ ファイル、104 ページはタイトル テーブルに属するページです。 ページが属object_idを識別するには、動的管理機能 sys.dm_db_page_info を使用して、から DatabaseID、FileId、PageId を渡します wait_resource
    キー DatabaseID:Hobt_id (インデックス キーのハッシュ値) KEY: 5:720575940444284928 (3300a4f361aa) この場合、データベース ID 5 は Pubs で、Hobt_ID 720575940444284928 は object_id 261575970 (タイトル テーブル) の 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 ページはタイトル テーブルに属するページ、スロット 3 はページ上の行の位置を示します。
    のコンパイル DatabaseID:FileID:PageID:Slot(row) RID: 5:1:104:3 この場合、データベース ID 5 は pubs、ファイル ID 1 はプライマリ データ ファイル、104 ページはタイトル テーブルに属するページ、スロット 3 はページ上の行の位置を示します。
    • sys.dm_tran_active_transactions DMV sys.dm_tran_active_transactions には、コミットまたはロールバックを待っているトランザクションの完全な画像のために他の DMV に参加できるオープン トランザクションに関するデータが含まれている。 次のクエリを使用して、開いているトランザクションに関する情報を返します。このクエリは、他の DMV 参加sys.dm_tran_session_transactions。 トランザクションの現在の状態、および他の状況データを検討して、ブロックのソースであるかどうかを 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 ()、SPID によって最後に送信されたバッチが存在した場合、要求が実行されている時間などから問題が発生したかどうかを判断できます。 client_interface_name 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 状態 解決済みですか? その他の現象
1 NULL ではない >= 0 runnable はい、クエリが終了するとします。 sys.dm_exec_sessions、、 reads および cpu_time /または列 memory_usage では、時間の間に増加します。 クエリの期間は、完了すると高く設定されます。
2 NULL >0 睡眠 いいえ、しかし SPID は殺される可能性があります。 この SPID の拡張イベント セッションで、クエリのタイムアウトまたはキャンセルが発生したことを示す注意信号が表示される場合があります。
3 NULL >= 0 runnable いいえ。 クライアントがすべての行をフェッチするか、接続を閉じるまで解決しない。 SPID は殺される可能性がありますが、最大 30 秒かかる場合があります。 トランザクションopen_transaction_count = 0 で、トランザクション分離レベルが既定 (READ COMMITTED) の間に SPID がロックを保持している場合、これが原因である可能性があります。
4 さまざま >= 0 runnable いいえ。 クライアントがクエリをキャンセルするか、接続を閉じるまで解決しません。 SPID は殺される可能性がありますが、最大 30 秒かかる場合があります。 ブロック チェーンの頭にある SPID の列は、ブロックしている SPID の 1 つ hostname sys.dm_exec_sessions と同じになります。
5 NULL >0 ロールバック はい。 この SPID の拡張イベント セッションで、クエリのタイムアウトまたはキャンセルが発生したことを示すアテンションシグナルが表示される場合があります。または単にロールバック ステートメントが発行されています。
6 NULL >0 睡眠 最終的に。 セッションWindows NTがアクティブでなくなったと判断すると、接続は壊れます。 last_request_start_timesys.dm_exec_sessions は、現在の時刻よりずっと前です。

ブロックの詳細なシナリオ

  1. 通常実行中のクエリの実行時間が長い場合のブロック

    解決策: この種類のブロック問題の解決策は、クエリを最適化する方法を探す方法です。 実際には、このブロックの問題のクラスはパフォーマンスの問題にすすむ可能性があります。そのため、このクラスを実行する必要があります。 特定の低速実行クエリのトラブルシューティングの詳細については、「実行時間の遅いクエリをトラブルシューティングする方法」を参照SQL Server。 詳細については、「Monitor and Tune for Performance」を参照してください

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

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

    注意

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

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

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

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

    CREATE TABLE #test (col1 INT);
    INSERT INTO #test SELECT 1;
    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) 」を参照してください

    注意

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

    注意事項

    次に、エラー SQLするステートメントに続く T-SQLステートメント SET XACT_ABORT ON は実行されません。 これは、既存のコードの意図したフローに影響を与える可能性があります。

  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 で返されるすべてのデータが完了する処理を行います。 DBproc1 の結果を取得し、SPID2 が保持するロックで SPID1 がブロックされるまで (2 つのクエリがサーバー上で非同期的に実行されているため)。 この時点で、dbproc1 は、より多くのデータを無期限に待機します。 SPID2 はロックでブロックされませんが、クライアント dbproc2 にデータを送信しようとします。 ただし、アプリケーションの単一スレッドが dbproc1 で使用されている場合、dbproc2 はアプリケーション層の 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 は、行一時処理を実行し、バッファーを介して各行を dbproc1 に渡して、同じ表の 、 、 またはステートメントを渡す目的でステートメントを実行しています。 SELECT INSERT UPDATE DELETE 最終的に、SPID1 (、、または) を実行すると、SPID2 が保持するロック (を実行 INSERT UPDATE ) DELETE でブロックされます SELECT 。 SPID2 は、結果行をクライアント dbproc2 に書き込みます。 その後、Dbproc2 はバッファー内の行を dbproc1 に渡そうとしますが、dbproc1 がビジー状態である場合があります (SPID1 では、SPID2 でブロックされている現在の値を終了する待機がブロックされています INSERT )。 この時点で、dbproc2 は、SPID (SPID1) が SPID2 によってデータベース レベルでブロックされる dbproc1 によってアプリケーション層でブロックされます。 繰り返しますが、関連するリソースの 1 つだけがリソースSQL Server検出または解決できないデッドロックが発生SQL Serverされます。

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

    解決策:

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

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

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

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

    注意

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

    解決策:

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

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

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

  6. 孤立した接続によるブロック

    これは、一般的な問題シナリオです。 クライアント アプリケーションが停止するか、クライアント ワークステーションが再起動された場合、またはバッチ中止エラーが発生した場合、一部の条件下でサーバーへのネットワーク セッションが直ちに取り消されない場合があります。 これは、アプリケーションがアプリケーションの CATCH ブロックまたは FINALLY ブロックでトランザクションをロールバックしない場合に発生する可能性があります。

    このシナリオでは、SQLの実行が取り消されている間、SQL接続とトランザクションはアプリケーションによって開いた残りになります。 インスタンスのSQL Server見ると、クライアントは存在し続け、取得されたロックは保持されます。

    解決策:

    この状態を回避する最善の方法は、特に予期しない終了の場合に、アプリケーション エラー処理を改善することで行います。 接続、またはトランザクションを開始し、エラー後にクリーンアップしないストアド プロシージャの使用 SET XACT_ABORT ON も検討してください。 実行時エラーが発生した場合、この設定は開いているトランザクションを中止し、クライアントに制御を返します。 詳細については、「SET XACT_ABORT (Transact-SQL) 」を参照してください

    リソースを適切にクリーンアップせずに切断されたクライアント アプリケーションの孤立した接続を解決するには、コマンドを使用して SPID を終了 KILL できます。 参照については、「KILL (Transact-SQL)」を参照してください

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

    KILL 99
    

    注意

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

こちらもご覧ください