クエリオプティマイザーのタイムアウトの影響を受ける低速クエリのトラブルシューティング

[アーティクル]
03/24/2023

適用対象: SQL Server

この記事では、オプティマイザーのタイムアウト、クエリのパフォーマンスに与える影響、およびパフォーマンスを最適化する方法について説明します。

オプティマイザータイムアウトとは

SQL Server では、コストベースの Query Optimizer (QO) が使用されます。 QO の詳細については、 Query 処理アーキテクチャガイドを参照してください。コストベースのクエリオプティマイザーでは、複数のクエリプランを構築して評価した後、コストが最も低いクエリ実行プランが選択されます。 SQL Server クエリオプティマイザーの目的の 1 つは、クエリの実行と比較して、クエリの最適化に妥当な時間を費やすことです。クエリの最適化は、クエリを実行するよりもはるかに高速である必要があります。この目標を達成するために、QO には、最適化プロセスを停止する前に考慮するタスクのしきい値が組み込まれています。 QO が考えられるすべてのプランを考慮する前にしきい値に達すると、オプティマイザーのタイムアウト制限に達します。オプティマイザータイムアウトイベントは、クエリプランで TimeOut として報告されます ステートメント最適化の早期終了に対する Reason。このしきい値は、クロック時間ではなく、オプティマイザーによって考慮される可能性の数に基づいていることを理解することが重要です。現在の SQL Server QO バージョンでは、タイムアウトに達する前に 50 万を超えるタスクが考慮されます。

オプティマイザータイムアウトは SQL Server に設計されており、多くの場合、クエリのパフォーマンスに影響を与える要因ではありません。ただし、場合によっては、SQL クエリプランの選択がオプティマイザータイムアウトの影響を受け、クエリのパフォーマンスが低下する可能性があります。このような問題が発生した場合は、オプティマイザーのタイムアウトメカニズムを理解し、複雑なクエリに与える影響を理解すると、クエリ速度のトラブルシューティングと向上に役立ちます。

オプティマイザーのタイムアウトしきい値に達した結果、SQL Server は最適化の可能性のセット全体を考慮していないということです。つまり、実行時間が短くなる可能性のあるプランが見落とされている可能性があります。 QO はしきい値で停止し、その時点で最小コストのクエリプランを検討します。ただし、より優れた未踏のオプションが存在する可能性があります。オプティマイザータイムアウトに達した後に選択されたプランは、クエリの妥当な実行時間を生成する可能性があることに注意してください。ただし、場合によっては、選択したプランによってクエリが実行され、最適ではない場合があります。

オプティマイザーのタイムアウトを検出する方法

オプティマイザーのタイムアウトを示す現象を次に示します。

複雑なクエリ

多数の結合テーブルを含む複雑なクエリがあります (たとえば、8 つ以上のテーブルが結合されます)。
低速クエリ

クエリの実行速度が、別の SQL Server のバージョンまたはシステムで実行されるよりも遅い場合があります。

クエリプランに StatementOptmEarlyAbortReason=Timeout が表示される

クエリプランには、XML クエリプランの StatementOptmEarlyAbortReason="TimeOut" が表示されます。

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.518" Build="13.0.5201.2" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
  <Batch>
   <Statements>
    <StmtSimple  ..." StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="TimeOut" ......>
    ...
   <Statements>
  <Batch>
<BatchSequence>

Microsoft SQL Server Management Studio で、左端のプランオペレーターのプロパティを確認します。ステートメントの最適化の早期終了に対する Reason の値を確認できます は TimeOut です。

オプティマイザータイムアウトの原因

オプティマイザーのしきい値に達したり超えたりする原因となる条件を簡単に判断する方法はありません。次のセクションでは、最適なプランを探すときに QO で探索されるプランの数に影響を与えるいくつかの要因について説明します。

テーブルはどのような順序で結合する必要がありますか?

3 つのテーブル結合 (Table1、 Table2、 Table3) の実行オプションの例を次に示します。
- Table1をTable2に結合し、結果をTable3
- Table1をTable3に結合し、結果をTable2
- Table2をTable3に結合し、結果をTable1
注: テーブルの数が多いほど、可能性は大きくなります。
テーブルから行を取得するために使用するヒープまたはバイナリツリー (HoBT) アクセス構造は何ですか?
- クラスター化したインデックス
- 非クラスター化インデックス 1
- 非クラスター化インデックス 2
- テーブルヒープ
どのような物理的なアクセス方法を使用しますか?
- インデックスシーク
- インデックススキャン
- テーブルスキャン
使用する物理結合演算子は何ですか?
- 入れ子になったループ結合 (NJ)
- ハッシュ結合 (HJ)
- マージ結合 (MJ)
- アダプティブ結合 (SQL Server 2017 (14.x) 以降)
詳細については、結合に関するページを参照してください。
クエリの一部を並列または直列に実行しますか?

詳細については、「 Parallel クエリ処理」を参照してください。

次の要因により、考慮されるアクセス方法の数が減り、考えられる可能性は減少します。

クエリ述語 ( WHERE 句のフィルター)
制約の存在
適切に設計された統計と最新の統計の組み合わせ

注: QO がしきい値に達しても、最終的にクエリが遅くなるわけではありません。ほとんどの場合、クエリは適切に実行されますが、場合によってはクエリの実行が遅くなる場合があります。

要因を考慮する方法の例

たとえば、3 つのテーブル (t1、 t2、 t3) の結合の例を見てみましょう。各テーブルにはクラスター化インデックスと非クラスター化インデックスがあります。

最初に、物理結合の種類を検討します。ここには 2 つの結合があります。また、3 つの物理結合の可能性 (NJ、HJ、MJ) があるため、クエリは 3² = 9 の方法で実行できます。

NJ - NJ
NJ - HJ
NJ - MJ
HJ - NJ
HJ - HJ
HJ - MJ
MJ - NJ
MJ - HJ
MJ - MJ

次に、順列 P (n, r) を使用して計算される結合順序を検討します。最初の 2 つのテーブルの順序は関係ないため、P(3,1) = 3 つの可能性があります。

t2でt1を結合してから、t3
t3でt1を結合してから、t2
t3でt2を結合してから、t1

次に、データ取得に使用できるクラスター化インデックスと非クラスター化インデックスを検討します。また、インデックスごとに、シークまたはスキャンという 2 つのアクセス方法があります。つまり、テーブルごとに、2² = 4 つの選択肢があります。 3 つのテーブルがあるため、4³ = 64 個の選択肢があります。

最後に、これらすべての条件を考慮すると、9*3*64 = 1728 のプランが考えられます。

次に、クエリに結合された n 個のテーブルがあり、各テーブルにクラスター化インデックスと非クラスター化インデックスがあるとします。次のような要素が考えられます。

結合注文: P(n,n-2) = n!/2
結合の種類: 3^n-1
シークメソッドとスキャン方法を使用したさまざまなインデックスの種類: 4ⁿ

上記のすべてを乗算すると、可能なプランの数を取得できます:2*n!*12^n-1。 n = 4 の場合、数値は 82,944 です。 n = 6 の場合、数値は 358,318,080 です。そのため、クエリに関係するテーブルの数が増えると、可能なプランの数が幾何学的に増加します。さらに、並列処理の可能性やその他の要因を含める場合は、考えられるプランの数を想像できます。そのため、結合が多いクエリは、結合数が少ないクエリよりも、オプティマイザーのタイムアウトしきい値に達する可能性が高くなります。

上記の計算は最悪のシナリオを示しています。既に指摘したように、フィルター述語、統計、制約など、可能性の数を減らす要因があります。たとえば、フィルター述語と更新された統計では、スキャンよりもインデックスシークを使用する方が効率的な場合があるため、物理アクセス方法の数が減ります。これにより、結合の選択も小さくなります。

単純なクエリでオプティマイザータイムアウトが表示される理由

クエリオプティマイザーでは単純なものはありません。多くのシナリオが考えられます。複雑さの程度が非常に高いため、すべての可能性を把握するのは困難です。クエリオプティマイザーは、特定のステージで見つかったプランのコストに基づいて、タイムアウトしきい値を動的に設定できます。たとえば、比較的効率的に表示されるプランが見つかった場合、より適切なプランを検索するタスクの制限が減る可能性があります。したがって、過小評価されたカーディナリティ推定 (CE) は、オプティマイザータイムアウトを早期にヒットするシナリオの 1 つです。この場合、調査の焦点は CE です。前のセクションで説明した複雑なクエリの実行に関するシナリオと比較すると、まれなケースですが、可能です。

解決策

クエリプランに表示されるオプティマイザータイムアウトは、必ずしもそれがクエリパフォーマンスの低下の原因であるとは限りません。ほとんどの場合、この状況について何もする必要がない場合があります。 SQL Server が終了するクエリプランは妥当な場合があり、実行しているクエリのパフォーマンスが良好である可能性があります。オプティマイザーのタイムアウトが発生したことを知らない可能性があります。

チューニングと最適化が必要な場合は、次の手順を試してください。

手順 1: ベースラインを確立する

SQL Server の別のビルド、別の CE 構成、または別のシステム (ハードウェア仕様) で、同じデータセットで同じクエリを実行できるかどうかを確認します。パフォーマンスチューニングの基本原則は、"ベースラインなしでパフォーマンスの問題はありません" です。そのため、同じクエリのベースラインを確立することが重要です。

手順 2: オプティマイザーのタイムアウトにつながる "非表示" 条件を探す

クエリを詳細に調べて、複雑さを判断します。最初の検査では、クエリが複雑であり、多くの結合が含まれることは明らかでない場合があります。ここでの一般的なシナリオは、ビューまたはテーブル値関数が関係することです。たとえば、サーフェス上では、クエリは 2 つのビューを結合するため、単純に見える場合があります。ただし、ビュー内のクエリを調べると、各ビューが 7 つのテーブルを結合していることがわかります。その結果、2 つのビューが結合されると、最終的に 14 テーブル結合になります。クエリで次のオブジェクトを使用する場合は、各オブジェクトをドリルダウンして、その中の基になるクエリの外観を確認します。

これらのすべてのシナリオで最も一般的な解決策は、クエリを書き直して複数のクエリに分割することです。詳細については、「手順 7: クエリを絞り込むを参照してください。

サブクエリまたは派生テーブル

次のクエリは、それぞれ 4 ~ 5 個の結合を持つ 2 つの個別のクエリセット (派生テーブル) を結合する例です。ただし、SQL Server による解析後、8 つのテーブルが結合された単一のクエリにコンパイルされます。

SELECT ...
  FROM 
    ( SELECT ...
        FROM t1 
        JOIN t2 ON ...
        JOIN t3 ON ...
        JOIN t4 ON ...
        WHERE ...
    ) AS derived_table1
INNER JOIN
  ( SELECT ...
      FROM t5 
      JOIN t6 ON ...
      JOIN t7 ON ...
      JOIN t8 ON ...
      WHERE ...
  ) AS derived_table2 
ON derived_table1.Co1 = derived_table2.Co10 
AND derived_table1.Co2 = derived_table2.Co20

共通テーブル式 (CTE)

複数の共通テーブル式 (CTE) を使用することは、クエリを簡略化し、オプティマイザーのタイムアウトを回避するための適切なソリューションではありません。複数の CTE を使用すると、クエリの複雑さが増すだけです。そのため、オプティマイザーのタイムアウトを解決するときに CTE を使用するのは逆効果です。 CTE はクエリを論理的に分割するように見えますが、1 つのクエリに結合され、1 つの大きなテーブル結合として最適化されます。

多数の結合を持つ単一のクエリとしてコンパイルされる CTE の例を次に示します。 my_cteに対するクエリは 2 オブジェクトの単純結合のように見えるかもしれませんが、実際には、CTE には他に 7 つのテーブルが結合されています。

WITH my_cte AS (
  SELECT ...
    FROM t1 
    JOIN t2 ON ...
    JOIN t3 ON ...
    JOIN t4 ON ...
    JOIN t5 ON ...
    JOIN t6 ON ...
    JOIN t7 ON ...
    WHERE ... )

SELECT ...
  FROM my_cte 
  JOIN t8 ON ...

ビュー

ビュー定義を確認し、関連するすべてのテーブルを取得していることを確認します。 CTE や派生テーブルと同様に、結合はビュー内で非表示にすることができます。たとえば、2 つのビュー間の結合は、最終的に 8 つのテーブルが関係する単一のクエリになる場合があります。

CREATE VIEW V1 AS 
  SELECT ...
    FROM t1 
    JOIN t2 ON ...
    JOIN t3 ON ...
    JOIN t4 ON ...
    WHERE ...
GO

CREATE VIEW V2 AS 
  SELECT ...
    FROM t5 
    JOIN t6 ON ...
    JOIN t7 ON ...
    JOIN t8 ON ...
    WHERE ...
GO

SELECT ...
  FROM V1 
  JOIN V2 ON ...

テーブル値関数 (TVF)

一部の結合は、TFV 内で非表示になる場合があります。次の例は、2 つの TFV 間の結合として表示される内容を示しています。1 つのテーブルは 9 つのテーブル結合である可能性があります。

CREATE FUNCTION tvf1() RETURNS TABLE
AS RETURN
  SELECT ...
    FROM t1 
    JOIN t2 ON ...
    JOIN t3 ON ...
    JOIN t4 ON ...
    WHERE ...
GO 

CREATE FUNCTION tvf2() RETURNS TABLE
AS RETURN
  SELECT ...
    FROM t5
    JOIN t6 ON ...
    JOIN t7 ON ...
    JOIN t8 ON ...
    WHERE ...
GO

SELECT ...
  FROM tvf1() 
  JOIN tvf2() ON ...
  JOIN t9 ON ...

Union (結合)

共用体演算子は、複数のクエリの結果を 1 つの結果セットに結合します。また、複数のクエリを 1 つのクエリに結合します。その後、1 つの複雑なクエリを取得できます。次の例では、最終的に 12 個のテーブルを含む単一のクエリプランになります。

SELECT ...
  FROM t1 
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...

UNION ALL

SELECT ...
  FROM t5 
  JOIN t6 ON ...
  JOIN t7 ON ...
  JOIN t8 ON ...

UNION ALL

SELECT ...
  FROM t9
  JOIN t10 ON ...
  JOIN t11 ON ...
  JOIN t12 ON ...

手順 3: より高速に実行されるベースラインクエリがある場合は、そのクエリプランを使用する

手順 1 から取得した特定のベースラインプランがテストを通じてクエリに適していると判断した場合は、次のいずれかのオプションを使用して、QO でそのプランを選択します。

手順 4: プランの選択肢を減らす

オプティマイザータイムアウトの可能性を減らすには、プランの選択時に QO で考慮する必要がある可能性を減らしてください。このプロセスでは、さまざまな hint オプションを使用してクエリをテスト。 QO を使用したほとんどの決定と同様に、さまざまな要因を考慮する必要があるため、選択は常に決定論的であるとは限りません。そのため、1 つの保証された成功戦略はありません。選択したプランでは、選択したクエリのパフォーマンスが向上または低下する可能性があります。

JOIN 順序を強制する

順序順列を排除するには、 OPTION (FORCE ORDER) を使用します。

SELECT ...
  FROM t1
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...
OPTION (FORCE ORDER)

JOIN の可能性を減らす

他の方法が役に立たない場合は、物理結合演算子の選択を結合ヒントで制限することで、クエリプランの組み合わせを減らしてみてください。例: OPTION (HASH JOIN, MERGE JOIN)、 OPTION (HASH JOIN, LOOP JOIN) 、または OPTION (MERGE JOIN)。

注: これらのヒントを使用するときは注意が必要です。

場合によっては、結合の選択肢が少ないオプティマイザーを制限すると、最適な結合オプションが使用できない可能性があり、実際にはクエリの速度が低下する可能性があります。また、場合によっては、オプティマイザーで特定の結合 (たとえば、の目標) が必要であり、その結合がオプションでない場合、クエリでプランの生成に失敗することがあります。そのため、特定のクエリの結合ヒントをターゲットにした後、パフォーマンスが向上し、オプティマイザーのタイムアウトを排除する組み合わせが見つかるかどうかを確認します。

このようなヒントを使用する方法の 2 つの例を次に示します。

OPTION (HASH JOIN, LOOP JOIN)を使用して、ハッシュ結合とループ結合のみを許可し、クエリでのマージ結合を回避します。
```
SELECT ...
  FROM t1 
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...
  JOIN t5 ON ...
OPTION (HASH JOIN, LOOP JOIN)
```

2 つのテーブル間に特定の結合を適用します。

SELECT ...
  FROM t1 
  INNER MERGE JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...
  JOIN t5 ON ...

手順 5: CE の構成を変更する

レガシ CE と新しい CE を切り替えて、CE の構成を変更してみてください。 CE 構成を変更すると、SQL Server がクエリプランを評価して作成するときに、QO によって別のパスが選択される可能性があります。そのため、オプティマイザーのタイムアウトの問題が発生した場合でも、代替 CE 構成を使用して選択したプランよりも最適に実行されるプランになる可能性があります。詳細については、「最適なクエリプランをアクティブ化する方法 (カーディナリティ推定)を参照してください。

手順 6: オプティマイザーの修正を有効にする

クエリオプティマイザーの修正プログラムを有効にしていない場合は、次の 2 つの方法のいずれかを使用して有効にすることを検討してください。

サーバーレベル: トレースフラグ T4199を使用します。
データベースレベル: を使用するか、SQL Server 2016 以降のバージョンのデータベース互換性レベルを変更します。

QO の修正により、オプティマイザーがプラン探索で別のパスを取得する可能性があります。そのため、より最適なクエリプランを選択できます。詳細については、「 SQL Server クエリオプティマイザー修正プログラムトレースフラグ 4199 サービスモデルを参照してください。

手順 7: クエリを絞り込む

一時テーブルを使用して、単一のマルチテーブルクエリを複数の個別のクエリに分割することを検討してください。クエリを分割することは、オプティマイザーのタスクを簡略化する方法の 1 つに過ぎません。次の例を参照してください。

SELECT ...
  FROM t1
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...
  JOIN t5 ON ...
  JOIN t6 ON ...
  JOIN t7 ON ...
  JOIN t8 ON ...

クエリを最適化するには、結合結果の一部を一時テーブルに挿入して、1 つのクエリを 2 つのクエリに分割します。

SELECT ...
  INTO #temp1
  FROM t1 
  JOIN t2 ON ...
  JOIN t3 ON ...
  JOIN t4 ON ...

GO

SELECT ...
  FROM #temp1
  JOIN t5 ON ...
  JOIN t6 ON ...
  JOIN t7 ON ...
  JOIN t8 ON ...

次の方法で共有

クエリオプティマイザーのタイムアウトの影響を受ける低速クエリのトラブルシューティング

オプティマイザータイムアウトとは

オプティマイザーのタイムアウトを検出する方法

オプティマイザータイムアウトの原因

要因を考慮する方法の例

単純なクエリでオプティマイザータイムアウトが表示される理由

解決策

手順 1: ベースラインを確立する

手順 2: オプティマイザーのタイムアウトにつながる "非表示" 条件を探す

サブクエリまたは派生テーブル

共通テーブル式 (CTE)

ビュー

テーブル値関数 (TVF)

Union (結合)

手順 3: より高速に実行されるベースラインクエリがある場合は、そのクエリプランを使用する

手順 4: プランの選択肢を減らす

JOIN 順序を強制する

JOIN の可能性を減らす

手順 5: CE の構成を変更する

手順 6: オプティマイザーの修正を有効にする

手順 7: クエリを絞り込む

フィードバック

その他のリソース

次の方法で共有

クエリ オプティマイザーのタイムアウトの影響を受ける低速クエリのトラブルシューティング

オプティマイザー タイムアウトとは

オプティマイザーのタイムアウトを検出する方法

オプティマイザー タイムアウトの原因

要因を考慮する方法の例

単純なクエリでオプティマイザー タイムアウトが表示される理由

解決策

手順 1: ベースラインを確立する

手順 2: オプティマイザーのタイムアウトにつながる "非表示" 条件を探す

サブクエリまたは派生テーブル

共通テーブル式 (CTE)

ビュー

テーブル値関数 (TVF)

Union (結合)

手順 3: より高速に実行されるベースライン クエリがある場合は、そのクエリ プランを使用する

手順 4: プランの選択肢を減らす

JOIN 順序を強制する

JOIN の可能性を減らす

手順 5: CE の構成を変更する

手順 6: オプティマイザーの修正を有効にする

手順 7: クエリを絞り込む

フィードバック

その他のリソース

クエリオプティマイザーのタイムアウトの影響を受ける低速クエリのトラブルシューティング

オプティマイザータイムアウトとは

オプティマイザータイムアウトの原因

単純なクエリでオプティマイザータイムアウトが表示される理由

手順 3: より高速に実行されるベースラインクエリがある場合は、そのクエリプランを使用する