次の方法で共有

ブロッキング問題の説明と解決策

適用対象:Azure SQL DatabaseMicrosoft Fabric SQL Database

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

目的

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

デッドロックのトラブルシューティングの詳細については、「 Azure SQL Database と Fabric SQL Database でのデッドロックの分析と防止」を参照してください。

Note

この内容は Azure SQL Database が対象です。 Azure SQL データベースは、Microsoft SQL Server データベース エンジンの最新の安定バージョンに基づいているため、トラブルシューティングのオプションやツールが異なる場合はありますが、コンテンツの大半は似ています。 SQL Server のブロックの詳細については、「SQL Server の問題の概要と解決策」を参照してください。 Fabric SQL Database は、Azure SQL Database と多くの機能を共有します。 パフォーマンス監視の詳細については、「 Microsoft Fabric での SQL データベースの監視」を参照してください。

ブロックの概要

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

Azure SQL Database の新しいデータベースではそれぞれ、READ COMMITTED SNAPSHOT (RCSI) データベース設定が既定で有効になっています。 行のバージョン管理を使用してコンカレンシーを高める RCSI のもとで、データを読み取るセッションとデータを書き込むセッション間のブロックが最小限に抑えられます。 ただし、それでも、次の理由によって Azure SQL データベースのデータベースでブロックやデッドロックが発生する場合があります:

  • データを変更するクエリが互いをブロックする場合があります。
  • ブロックを増加させる分離レベルでクエリが実行される場合があります。 分離レベルは、アプリケーション接続文字列、クエリ ヒント、または Transact-SQL の SET ステートメント で指定できます。
  • RCSI が無効になっている可能性があります。この場合、データベースでは共有 (S) ロックを使用して、READ COMMITTED 分離レベルで実行される SELECT ステートメントを保護します。 これにより、ブロックとデッドロックが増加する場合があります。

Azure SQL Database の新しいデータベースでは、スナップショット分離レベルも既定で有効になっています。 スナップショット分離は行ベースの追加の分離レベルであり、トランザクションレベルのデータ整合性を提供し、行のバージョンを使用して更新する行を選択します。 スナップショット分離を使用するには、クエリまたは接続でそのトランザクション分離レベルを明示的に SNAPSHOT に設定する必要があります。 これは、データベースでスナップショット分離が有効である場合にのみ行うことができます。

RCSI とスナップショット分離の一方または両方が有効になっているかどうかは、Transact-SQL を使用して識別できます。 Azure SQL Database のデータベースに接続し、次のクエリを実行します。

SELECT name, is_read_committed_snapshot_on, snapshot_isolation_state_desc
FROM sys.databases
WHERE name = DB_NAME();
GO

RCSI が有効な場合、is_read_committed_snapshot_on 列は値 1 を返します。 スナップショット分離が有効な場合、snapshot_isolation_state_desc 列は値 [オン] を返します。

クエリの期間とトランザクション コンテキストによって、ロックが保持される期間と、その他のクエリへの影響が決まります。 RCSI 下で実行される SELECT ステートメントでは、読み取るデータに対して共有 (S) ロックが取得されない ので、データを変更しているトランザクションはブロックされません。 INSERT、UPDATE、および DELETE ステートメントでは、データの整合性と、必要に応じてクエリをロールバックできるように、クエリの実行中にロックが保持されます。

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

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

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

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

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

最適化されたロック

最適化されたロックは、ロック メモリと書き込みで同時に必要となるロック数を大幅に減らす、新しいデータベース エンジン機能です。 最適化されたロックでは、次の 2 つの主要なコンポーネントが使用されます: トランザクション ID (TID) ロック (他の行のバージョン管理機能でも使用されます) および該当後のロック (LAQ)。 追加の構成は必要ありません。

現在、この記事は最適化されたロックを使わないデータベース エンジンの動作を対象にしています。

最適化されたロックの詳細と使用できる場所については、「最適化されたロック」を参照してください。

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

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

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

アプリケーションとクエリの適切な設計により、Azure SQL Database では、単一のサーバーで、ブロックがほとんど発生せずに、数千の同時ユーザーをサポートすることができます。

Note

アプリケーション開発のガイダンスの詳細については、「 接続の問題とその他のエラーのトラブルシューティング」および 「 一時的な障害処理」を参照してください。

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

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

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

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

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

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

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

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

ブロック情報の収集

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

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

DMV からの情報の収集

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

Azure SQL Database のターゲット データベースでこれらの各スクリプトを実行してください。

  • 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);

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

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

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 のスレッドまたはタスク層にあるsys.dm_os_waiting_tasks を参照してください。 これにより、要求で現在発生している SQL の待機の種類に関する情報が返されます。 sys.dm_exec_requests と同様に、sys.dm_os_waiting_tasks からはアクティブな要求のみが返されます。

Note

経時的に集計される待機の統計情報などの待機の種類の詳細については、DMV「sys.dm_db_wait_stats」を参照してください。 この DMV によって、現在のデータベースのみの集計待機統計情報が返されます。

  • クエリによってどのロックが設定されているかに関するより詳細な情報については、sys.dm_tran_locks DMV を使用します。 この DMV によって、実稼働データベースに関する大量のデータが返される可能性があり、現在どんなロックが保持しているかを診断するのに役立ちます。

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 を使用すると、クエリ結果を時間の経過と共に格納することで、指定した期間にわたってブロックを確認して、永続的なブロックまたは傾向を特定できるデータ ポイントが提供されます。

拡張イベントからの情報の収集

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

SQL Server でトレースをキャプチャする方法は 2 つあります。拡張イベント (Xevent) とプロファイラー トレースです。 ただし、SQL Server Profiler は、Azure SQL Database でサポートされていない非推奨のトレース テクノロジです。 拡張イベント は、より汎用性が高く、観察されたシステムへの影響が少なく、そのインターフェイスが SQL Server Management Studio (SSMS) に統合される新しいトレース テクノロジです。

SSMS の拡張イベントの新しいセッション ウィザードの使用方法について説明しているドキュメントを参照してください。 ただし、Azure SQL Database の場合、SSMS によって、オブジェクト エクスプローラーの各データベースの下に拡張イベント サブフォルダーが提供されます。 拡張イベント セッション ウィザードを使用して、これらの有益なイベントをキャプチャします。

  • カテゴリ エラー:

    • Attention
    • Error_reported
    • Execution_warning

  • カテゴリ警告:

    • Missing_join_predicate

  • カテゴリ実行:

    • Rpc_completed
    • Rpc_starting
    • Sql_batch_completed
    • Sql_batch_starting

  • カテゴリ deadlock_monitor

    • database_xml_deadlock_report

  • カテゴリ セッション

    • Existing_connection
    • ログイン
    • Logout

Note

デッドロックの詳細については、「 Azure SQL Database と Fabric SQL Database でのデッドロックの分析と防止」を参照してください。

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

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

ブロック データの分析

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

  • ブロック チェーンの先頭にあるセッション ID に関する情報については、DMV sys.dm_exec_requests と sys.dm_exec_sessions の出力を調べます。 次のフィールドを探します。

    • sys.dm_exec_requests.status
      この列には、特定の要求の状態が表示されます。 通常、スリープ状態は、セッション ID が実行を完了し、アプリケーションが別のクエリまたはバッチを送信するのを待機していることを示します。 実行可能または実行中の状態は、セッション ID が現在クエリを処理していることを示します。 次の表に、さまざまな状態値の簡単な説明を示します。
    Status 意味
    バックグラウンド セッション ID は、デッドロック検出、ログ ライター、チェックポイントなどのバックグラウンド タスクを実行しています。
    休止中 セッション ID は現在実行中ではありません。 これは通常、セッション ID がアプリケーションからのコマンドを待機していることを示します。
    実行中 現在、セッション ID はスケジューラで実行されています。
    実行可能 セッション ID はスケジューラの実行可能キューにあり、スケジューラの時刻の取得を待機しています。
    Suspended セッション ID は、ロックやラッチなどのリソースを待機しています。

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

    • sys.dm_exec_requests.open_transaction_count
      同様に、このフィールドは、この要求で開いているトランザクションの数を示しています。 この値が 0 より大きい場合、セッション ID は開いているトランザクション内にあり、トランザクション内の任意のステートメントによって取得されたロックを保持している可能性があります。

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

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

    リソース Format 例 説明
    テーブル DatabaseID:ObjectID:IndexID TAB:5:261575970:1 この場合、データベース ID 5 は pubs サンプル データベース、オブジェクト ID 261575970 は titles テーブル、1 はクラスター化インデックスです。
    ページ DatabaseID:FileID:PageID PAGE:5:1:104 この場合、データベース ID 5 is pubs、ファイル ID 1 はプライマリ データ ファイル、104 ページはタイトル テーブルに属するページです。 ページが属している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はindex_id 261575970のobject_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 はページ上の行の位置を示します。
    Compile DatabaseID:FileID:PageID:Slot(row) RID:5:1:104:3 この場合、データベース ID 5 は pubs、ファイル ID 1 はプライマリ データ ファイル、ページ 104 はタイトル テーブルに属するページ、スロット 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
, azure_dtc_state = CASE tat.dtc_state 
                    WHEN 1 THEN 'ACTIVE'
                    WHEN 2 THEN 'PREPARED'
                    WHEN 3 THEN 'COMMITTED'
                    WHEN 4 THEN 'ABORTED'
                    WHEN 5 THEN 'RECOVERED' END
, 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 の残りの列でも、問題の原因に関する分析情報を得ることができます。 これらの有用性は、問題の状況によって異なります。 たとえば、特定のクライアント (ホスト名)、特定のネットワーク ライブラリ (net_library) でのみ問題が発生したかどうか、セッション ID によって送信された最後のバッチがlast_request_start_timeでsys.dm_exec_sessionsされたとき、start_timeでsys.dm_exec_requestsを使用して要求が実行されていた期間などを判断できます。

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

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

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

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

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

  1. 実行時間が長い通常実行行されるクエリに原因があるブロック

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

    SSMS のクエリ ストアからのレポートも、最もコストがかかるクエリ、最適でない実行プランを特定するために、強く推奨される貴重なツールです。 また、 Query Performance Insight も確認します。

    クエリで SELECT 操作のみを実行するのであれば、データベースでスナップショット分離が有効である場合、特に RCSI が無効になっている場合に、スナップショット分離下でステートメントを実行する ことを検討してください。 RCSI が有効な場合と同様に、データを読み取るクエリは、スナップショット分離レベル下で共有 (S) ロックを要求しません。 さらに、スナップショット分離は、明示的な複数ステートメント トランザクションのすべてのステートメントに対してトランザクション レベルの整合性を提供します。 スナップショット分離が データベースで既に有効になっている 場合があります。 スナップショット分離は、変更を実行するクエリでも使用できますが、更新の競合 を処理する必要があります。

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

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

    この種類のブロックは、多くの場合、セッション ID がスリープ状態またはコマンドを待機しているもので識別できますが、トランザクションの入れ子レベル (@@TRANCOUNT、open_transaction_count から sys.dm_exec_requests) が 0 より大きいことを示すものです。 これは、アプリケーションでクエリのタイムアウトが発生した場合、または必要な数の ROLLBACK ステートメントや COMMIT ステートメントを発行せずに、キャンセルが発行された場合に発生する可能性があります。 セッション ID は、クエリのタイムアウトまたは取り消しを受け取ると、現在のクエリとバッチを終了しますが、トランザクションを自動的にロールバックしたりコミットしたりすることはありません。 Azure SQL Database では、1 つのクエリが取り消されたためにトランザクション全体をロールバックする必要があることを想定できないため、これはアプリケーションによって行われます。 クエリのタイムアウトまたは取り消しは、拡張イベント セッションのセッション ID の 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 ベースのアプリケーションなど、プールに接続を解放する前にいくつかのクエリを実行するアプリケーションで接続プールが使用されている場合、接続プールを一時的に無効にすると、エラーを適切に処理するようにクライアント アプリケーションが変更されるまで、問題の軽減に役立つ場合があります。 接続プールを無効にすると、接続を解放することで、Azure SQL データベース 接続の物理的な切断が発生し、サーバーによって開いているトランザクションのロールバックが行われます。
      • 接続に対して、またはトランザクションを開始し、エラーの発生後にクリーンアップされないストアド プロシージャで、SET XACT_ABORT ON を使用します。 ランタイム エラーが発生した場合、この設定により、開いているトランザクションが中止され、クライアントに制御が返されます。 詳細については、 SET XACT_ABORTを参照してください。

    Note

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

    注意

    SET XACT_ABORT ONに続いて、エラーの原因となるステートメントの後に続く T-SQL ステートメントは実行されません。 これは、既存のコードの目的のフローに影響する可能性があります。

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

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

    Azure SQL Database の既定の構成どおり、READ COMMITTED スナップショットがデータベースで有効になっている場合、このシナリオの影響は軽減されます。 詳細については、この記事の「ブロックの概要」を参照してください。

    Note

    Azure SQL Database に接続するアプリケーションの再試行ロジックのガイダンスを参照してください。

    解決方法:完了までに結果のすべての行をフェッチするように、アプリケーションを書き直す必要があります。 これにより、サーバー側ページングを実行するクエリの ORDER BY 句での OFFSET および FETCH の使用が妨げられるわけではありません。

  4. ロールバック状態のセッションに原因があるブロック

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

    2019 年に導入された 高速データベース復旧 のおかげで、長時間のロールバックはまれです。

    解決策: セッション ID が行われた変更のロールバックが完了するまで待ちます。

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

  5. 孤立した接続に原因があるブロック

    クライアント アプリケーションでエラーがトラップされた場合、またはクライアント ワークステーションが再起動された場合、一部の条件下で、サーバーへのネットワーク セッションが直ちに取り消されないことがあります。 Azure SQL データベースの観点から、クライアントはまだ存在しているように見え、取得されたロックが引き続き保持される場合があります。 詳細については、SQL Server の孤立した接続のトラブルシューティング方法に関するページを参照してください。

    解決策: クライアント アプリケーションがリソースを適切にクリーンアップせずに切断された場合は、 KILL コマンドを使用してセッション ID を終了できます。 KILL コマンドは、セッション ID 値を入力として受け取ります。 たとえば、セッション ID 99 を強制終了するには、次のコマンドを発行します。

    KILL 99

関連するコンテンツ

  • Last updated on