SQL Server で無限に実行されるように見えるクエリのトラブルシューティング

この記事では、Microsoft SQL Server クエリの終了に過剰な時間 (数時間または数日) がかかる問題のトラブルシューティングガイダンスを提供します。

症状

この記事では、終了せずに実行またはコンパイルされるように見えるクエリに焦点を当てます。つまり、CPU 使用率は引き続き増加します。この記事は、解放されないリソースでブロックまたは待機しているクエリには適用されません。このような場合、CPU 使用率は一定のままであるか、わずかに変化します。

重要

クエリの実行を続行したままにすると、最終的に完了する可能性があります。このプロセスには数秒または数日かかる場合があります。場合によっては、 WHILE ループが終了しない場合など、クエリが本当に無限になる場合があります。ここでは、"終わらない" という用語を使用して、終了しないクエリの認識を記述します。

原因

実行時間の長い (終わることのない) クエリの一般的な原因は次のとおりです。

非常に大きなテーブルで入れ子になったループ (NL) 結合: NL 結合の性質上、多数の行を持つテーブルを結合するクエリは、長時間実行される可能性があります。詳細については、「結合」を参照してください。
- NL 結合の 1 つの例として、 TOP、 FAST、または EXISTSの使用があります。ハッシュ結合またはマージ結合の方が高速な場合でも、オプティマイザーは行の目標のためにどちらの演算子も使用できません。
- NL 結合のもう 1 つの例は、クエリでの不等値結合述語の使用です。たとえば、「 SELECT .. FROM tab1 AS a JOIN tab 2 AS b ON a.id > b.id 」のように入力します。オプティマイザーでは、ここでマージ結合またはハッシュ結合を使用することもできません。
古い統計: 古い統計に基づいてプランを選択するクエリは、最適ではない可能性があり、実行に時間がかかる場合があります。
無限ループ: WHILE ループを使用する T-SQL クエリが誤って書き込まれる可能性があります。結果のコードはループを離れることはなく、無限に実行されます。これらのクエリは本当に終わりがありません。手動で強制終了されるまで実行されます。
多数の結合と大きなテーブルを持つ複雑なクエリ: 多くの結合テーブルを含むクエリには、通常、実行に時間がかかる可能性がある複雑なクエリプランがあります。このシナリオは、行を除外せず、多数のテーブルを含む分析クエリで一般的です。
不足しているインデックス: テーブルで適切なインデックスを使用すると、クエリの実行速度が大幅に向上します。インデックスを使用すると、データのサブセットを選択して、より高速なアクセスを提供できます。

解決策

手順 1: 終わりのないクエリを検出する

システムで実行されている終わりのないクエリを探します。クエリの実行時間が長いか、待機時間が長いか (ボトルネックでスタックしているか)、またはコンパイル時間が長いかを判断する必要があります。

1.1 診断を実行する

終わりのないクエリがアクティブな SQL Server インスタンスで、次の診断クエリを実行します。

DECLARE @cntr INT = 0

WHILE (@cntr < 3)
BEGIN
    SELECT TOP 10 s.session_id,
                    r.status,
                    CAST(r.cpu_time / (1000 * 60.0) AS DECIMAL(10,2)) AS cpu_time_minutes,
                    CAST(r.total_elapsed_time / (1000 * 60.0) AS DECIMAL(10,2)) AS elapsed_minutes,
                    r.logical_reads,
                    r.wait_time,
                    r.wait_type,
                    r.wait_resource,
                    r.reads,
                    r.writes,
                    SUBSTRING(st.TEXT, (r.statement_start_offset / 2) + 1,
                    ((CASE r.statement_end_offset
                        WHEN -1 THEN DATALENGTH(st.TEXT)
                        ELSE r.statement_end_offset
                    END - r.statement_start_offset) / 2) + 1) AS statement_text,
                    COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) 
                    + N'.' + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS command_text,
                    r.command,
                    s.login_name,
                    s.host_name,
                    s.program_name,
                    s.last_request_end_time,
                    s.login_time,
                    r.open_transaction_count,
                    atrn.name as transaction_name,
                    atrn.transaction_id,
                    atrn.transaction_state
        FROM sys.dm_exec_sessions AS s
        JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id 
                CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
        LEFT JOIN (sys.dm_tran_session_transactions AS stran 
                JOIN sys.dm_tran_active_transactions AS atrn
                ON stran.transaction_id = atrn.transaction_id)
        ON stran.session_id =s.session_id
        WHERE r.session_id != @@SPID
        ORDER BY r.cpu_time DESC
    
    SET @cntr = @cntr + 1
WAITFOR DELAY '00:00:05'
END

1.2 出力を確認する

クエリが長時間実行されるシナリオには、長い実行、長い待機、長いコンパイルがあります。クエリの実行速度が遅くなる理由の詳細については、「実行中と待機中: クエリが遅くなる理由」を参照してください。

実行時間が長い

この記事のトラブルシューティング手順は、次のような出力を受け取った場合に適用されます。CPU 時間は、大幅な待機時間なしで経過時間に比例して増加します。

session_id	状態	cpu_time_minutes	elapsed_time_minutes	logical_reads	wait_time_minutes	wait_type
56	実行中	64.40	23.50	0	0.00	NULL

クエリは、次の場合に継続的に実行されます。

CPU 時間の増加
runningまたはrunnable
最小またはゼロの待機時間
wait_typeなし

このような場合、クエリは行の読み取り、結合、結果の処理、計算、または書式設定を行います。これらのアクティビティはすべて CPU にバインドされたアクションです。

Note

計算の実行やlogical_reads ループなど、一部の CPU バインド T-SQL 要求では論理読み取りがまったく行われない可能性があるため、この場合、WHILEの変更は関係ありません。

低速クエリがこれらの条件を満たしている場合は、そのランタイムの削減に焦点を当てます。通常、ランタイムを減らすには、インデックスの適用、クエリの書き換え、統計の更新によって、クエリが処理する必要がある行の数を削減する必要があります。詳細については、「解決策」セクションを参照してください。

長い待機時間

この記事は、長い待ち時間のシナリオには適用されません。待機シナリオでは、次の例のような出力を受け取る場合があります。この例では、セッションがリソースを待機しているために CPU 使用率が少し変わることはありません。

session_id	状態	cpu_time_minutes	elapsed_time_minutes	logical_reads	wait_time_minutes	wait_type
56	一時停止中	0.03	4.20	50	4.10	LCK_M_U

待機の種類は、セッションがリソースを待機していることを示します。長い経過時間と長い待機時間は、セッションがこのリソースの有効期間の大部分を待機していることを示します。 Тhe の短い CPU 時間は、クエリの実際の処理に費やされた時間がほとんどなかったことを示します。

待機が原因で長いクエリのトラブルシューティングを行うには、「 SQL Server での実行時間の遅いクエリのトラブルシューティング」を参照してください。

長いコンパイル時間

まれに、CPU 使用率は時間の経過とともに継続的に増加しますが、クエリの実行によって決まることはありません。代わりに、過度に長いコンパイル (クエリの解析とコンパイル) が原因である可能性があります。このような場合は、 transaction_name 出力列で sqlsource_transformの値を確認します。このトランザクション名はコンパイルを示します。

手順 2: 診断ログを手動で収集する

システムに終わりのないクエリが存在することを確認したら、クエリのプランデータを収集してさらにトラブルシューティングを行うことができます。データを収集するには、SQL Server のバージョンに応じて、次のいずれかの方法を使用します。

SQL Server Management Studio (SSMS) を使用して診断データを収集するには、次の手順に従います。

最新のクエリ実行プラン XML をキャプチャします。
クエリプランを確認して、データが遅さの原因を明らかに示しているかどうかを確認します。一般的な表示の例を次に示します。
- テーブルまたはインデックスのスキャン (推定行を参照)
- 巨大な外部テーブルデータセットによって駆動される入れ子になったループ
- ループの内側に大きな分岐がある入れ子になったループ
- テーブルスプール
- 各行の処理に長い時間がかかる SELECT リスト内の関数
クエリの実行速度が速い場合は、"高速" 実行 (実際の XML 実行プラン) をキャプチャして結果を比較できます。

軽量クエリプロファイルインフラストラクチャは、Microsoft SQL Server 2014 で導入されました。これにより、低速なクエリの実行中に実際の統計をキャプチャできます。このトラブルシューティング機能を使用すると、実行時にクエリプランのクエリ演算子を調べ、クエリでほとんどの時間が費やされている場所を把握できます。

軽量クエリ実行統計プロファイルインフラストラクチャ v1 を使用してクエリ nu の低速な手順を特定するには、次の手順に従います。

次のコマンドを実行して、 query_thread_profile XEvent を有効にします。

CREATE EVENT SESSION [NodePerfStats] ON SERVER
ADD EVENT sqlserver.query_thread_profile(
  ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,
    sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,
    sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,
    sqlserver.sql_text))
ADD TARGET package0.ring_buffer(SET max_memory=(25600))
WITH (MAX_MEMORY=4096 KB,
  EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
  MAX_DISPATCH_LATENCY=30 SECONDS,
  MAX_EVENT_SIZE=0 KB,
  MEMORY_PARTITION_MODE=NONE,
  TRACK_CAUSALITY=OFF,
  STARTUP_STATE=OFF);

ALTER EVENT SESSION [NodePerfStats] ON SERVER STATE = START

影響を受ける終わりのないクエリをアプリケーションから開始します。

次のコマンドを約 1 分間隔で複数回実行して、クエリプラン演算子のランタイム実行統計を確認します。

SELECT CONVERT (varchar(30), getdate(), 126) as runtime,
            qp.session_id,
            convert(nvarchar(48), qp.physical_operator_name) as physical_operator_name,
            qp.row_count,
            qp.estimate_row_count,
            qp.node_id,
            req.cpu_time,
            req.total_elapsed_time,
            substring
            (REPLACE
            (REPLACE
                (SUBSTRING
                (SQLText.text
                , (req.statement_start_offset/2) + 1
                , (
                    (CASE statement_END_offset
                        WHEN -1
                        THEN DATALENGTH(SQLText.text)  
                        ELSE req.statement_END_offset
                        END
                        - req.statement_start_offset)/2) + 1)
            , CHAR(10), ' '), CHAR(13), ' '), 1, 512)  AS active_statement_text
FROM sys.dm_exec_query_profiles qp 
RIGHT OUTER JOIN sys.dm_exec_requests req
    ON qp.session_id = req.session_id
LEFT OUTER JOIN sys.dm_exec_sessions sess
    on req.session_id = sess.session_id
LEFT OUTER JOIN sys.dm_exec_connections conn on conn.session_id = req.session_id
OUTER APPLY sys.dm_exec_sql_text (ISNULL (req.sql_handle, conn.most_recent_sql_handle)) as SQLText
WHERE req.session_id <> @@SPID 
    AND sess.is_user_process = 1 
ORDER BY qp.session_id asc, row_count desc 
--this is to prevent massive grants
OPTION (max_grant_percent = 3, MAXDOP 1)

分析に十分なデータを取得するために、1 分間隔で 3 つまたは 4 つのスナップショットをキャプチャします。具体的には、時間の経過に伴う各演算子の row_count 数を比較して、行数の大幅な増加 (100 万以上) を示す量を確認できます。
SSMS の新しいクエリウィンドウで、次のコマンドを実行して、問題のあるクエリの推定クエリプランをキャプチャします。
```
SET SHOWPLAN_XML ON
GO
<problem query here>
GO
SET SHOWPLAN_XML OFF
```
ステップ 3 で実行したクエリによって決定される、行数が最も多いノード ID を使用して、推定クエリプラン内の同じノードを検索します。この手順は、実行時間が長い主な原因であるプラン内の演算子を特定するのに役立ちます。
次のコマンドを実行して XEvent を停止します。
```
ALTER EVENT SESSION [NodePerfStats] ON SERVER STATE = STOP
```

Lightweight クエリ実行統計プロファイルインフラストラクチャ v2 を使用して、実際の行数を含むライブクエリプランをキャプチャできます。このプロファイリングインフラストラクチャを使用すると、実行時にクエリプランのクエリ演算子を調べ、クエリでほとんどの時間が費やされる場所を確認できます。

クエリの低速な手順を特定するには、次の手順に従います。

軽量インフラストラクチャを有効にするには、次のいずれかの方法を使用します。

次のコマンド実行して、trace フラグ 7412 を有効にします。
```
DBCC TRACEON (7412, -1)
```

または、次のコマンドを実行して、 query_thread_profile XEvent を有効にします。

CREATE EVENT SESSION [PerfStats_LWP_Plan_v2] ON SERVER
ADD EVENT sqlserver.query_plan_profile(
 ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,
   sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,
   sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,
   sqlserver.sql_text))
ADD TARGET package0.ring_buffer(SET max_memory=(25600))
WITH (MAX_MEMORY=4096 KB,
 EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
 MAX_DISPATCH_LATENCY=30 SECONDS,
 MAX_EVENT_SIZE=0 KB,
 MEMORY_PARTITION_MODE=NONE,
 TRACK_CAUSALITY=OFF,
 STARTUP_STATE=OFF);

ALTER EVENT SESSION [PerfStats_LWP_Plan_v2] ON SERVER STATE = START

影響を受ける終わりのないクエリをアプリケーションから開始します。
実行されている終わりのないクエリの Session_id を特定するには、次のコマンドのようなコマンドを実行します。
```
SELECT t.text, session_id 
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text (req.sql_handle) as t
```
プラン内のクエリプランと実際の統計を調べるには、1 分間隔で次のコマンドを 3 回または 4 回実行します。プランを比較し、CPU 時間の大半を消費しているクエリ演算子を確認できるように、クエリプランを毎回保存してください。具体的には、時間の経過に伴う各演算子の行数 (実際の行数) を比較して、行数が大幅に増加している演算子 (100 万以上) を確認できます。 <session_id>を、前の手順で見つけた整数値に置き換えます。
```
SELECT * FROM sys.dm_exec_query_statistics_xml (<session_id>)
```

XEvent を開始した場合は停止するか、トレースフラグを無効にします。

ALTER EVENT SESSION [PerfStats_LWP_Plan_v2] ON SERVER STATE = STOP
-- or
DBCC TRACEOFF (7412, -1)

Lightweight クエリ実行統計プロファイルインフラストラクチャ v3 を使用して、実際の行数を含むライブクエリプランをキャプチャできます。このプロファイリングインフラストラクチャを使用すると、実行時にクエリプランのクエリ演算子を調べ、ほとんどの時間がクエリに費やされる場所を把握できます。軽量プロファイリングは、Microsoft SQL Server 2019 では既定で有効になっています。

クエリの低速な手順を特定するには、次の手順に従います。

影響を受ける終わりのないクエリをアプリケーションから開始します。
実行されている終わりのないクエリの Session_id を特定するには、次のコマンドのようなコマンドを実行します。
```
SELECT t.text, session_id 
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text (req.sql_handle) as t
```
次のコマンドを実行して、プラン内のクエリプランと実際の統計を調べます。クエリプランを比較し、CPU 時間の大半を消費しているクエリ演算子を確認できるように、クエリプランを毎回保存してください。 <session_id>を前の手順で見つけた整数値に置き換えます。
```
SELECT * FROM sys.dm_exec_query_statistics_xml (<session_id>)
```
query_plan列の XML リンクを選択します。グラフィカルクエリプランが新しいウィンドウで開いたら、それを右クリックし、[ 実行プランを名前を付けて保存...] を選択します。これらの手順を繰り返して、1 分間隔で 3 つまたは 4 つのスナップショットをキャプチャし、分析に十分なデータを取得します。具体的には、時間の経過に伴う各演算子の行数 (実際の行数) を比較して、行数の大幅な増加 (100 万以上) を示している演算子を確認できます。
Note

sys.dm_exec_query_statistics_xmlから出力が得られない場合は、次のコマンドを実行して、データベースオプションLAST_QUERY_PLAN_STATSが無効になっているかどうかを確認できます。
```
SELECT name, value, value_for_secondary, is_value_default 
FROM sys.database_scoped_configurations
WHERE name = 'LAST_QUERY_PLAN_STATS'
```
データベースレベルで最後のクエリプラン統計を有効にするには、 ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ONを実行します。

SQL LogScout を使用して終わりのないクエリをキャプチャする

SQL LogScout を使用して、終わりのないクエリの実行中にログをキャプチャできます。次のコマンドを使用して、終わることのないクエリシナリオを使用します。

.\SQL_LogScout.ps1 -Scenario "NeverEndingQuery" -ServerName "SQLInstance"

Note

このログキャプチャプロセスでは、長いクエリで少なくとも 60 秒の CPU 時間を消費する必要があります。

SQL LogScout は、CPU 使用率の高いクエリごとに少なくとも 3 つのクエリプランをキャプチャします。 servername_datetime_NeverEnding_statistics_QueryPlansXml_Startup_sessionId_#.sqlplanのようなファイル名を見つけることができます。これらのファイルは、プランを確認してクエリの実行時間が長い理由を特定するときに、次の手順で使用できます。

手順 3: 収集されたプランを確認する

このセクションでは、収集されたデータを確認する方法について説明します。 Microsoft SQL Server 2016 SP1 以降のビルドとバージョンで収集された複数の XML クエリプラン (拡張機能 .sqlplanを使用) が使用されます。

次の手順に従って実行プランを比較します。

以前に保存したクエリ実行プランファイル (.sqlplan) を開きます。
実行プランの空白領域を右クリックし、[ プランの比較] を選択します。
比較する 2 番目のクエリプランファイルを選択します。
演算子間を流れる多数の行を示す太い矢印を探します。次に、矢印の前または後の演算子を選択し、2 つのプランの実際の行数を比較します。
2 番目と 3 番目のプランを比較して、同じ演算子で最大の行フローが発生するかどうかを確認します。

例えば次が挙げられます。

手順 4: 解決

クエリで使用されるテーブルの統計が更新されていることを確認します。
クエリプランで不足しているインデックスの推奨事項を探し、見つけたものをすべて適用します。
クエリを簡略化します。
- より選択的な WHERE 述語を使用して、事前に処理されるデータを減らします。
- 分割します。
- 一時テーブルにいくつかの部分を選択し、後で結合します。
- TOPのために長時間実行されるクエリのEXISTS、FAST、および (T-SQL) を削除します。
  - または、 DISABLE_OPTIMIZER_ROWGOALhint を使用します。詳細については、「 Row Goals Gone Rogue」を参照してください。
- このような場合は、ステートメントを 1 つの大きなクエリに結合するため、共通テーブル式 (CTE) の使用は避けてください。
クエリヒントを使用して、より適切なプランを作成してみてください。
- HASH JOINまたはヒントMERGE JOIN
- FORCE ORDER ヒント
- FORCESEEK ヒント
- RECOMPILE
- USE PLAN N'<xml_plan>' (強制できる高速クエリプランがある場合)
クエリストア (QDS) を使用して、適切な既知のプランが存在する場合、および SQL Server のバージョンでクエリストアがサポートされている場合に、適切な既知のプランを強制します。

2 台のサーバー間のパフォーマンスの大きな違いを示すクエリのトラブルシューティング

フィードバック

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

Last updated on 2025-08-19