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

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

適用対象: SQL Server

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

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

SQL Serverでは、コストベースのクエリオプティマイザー (QO) が使用されます。 QO の詳細については、「クエリ処理アーキテクチャガイド」を参照してください。コストベースのクエリオプティマイザーでは、複数のクエリプランを構築して評価した後、コストが最も低いクエリ実行プランが選択されます。 SQL Serverクエリオプティマイザーの目的の 1 つは、クエリの実行と比較してクエリの最適化に適切な時間を費やすことです。クエリの最適化は、クエリを実行するよりもはるかに高速である必要があります。この目標を達成するために、QO には、最適化プロセスを停止する前に考慮するタスクのしきい値が組み込まれています。 QO が考えられるすべてのプランを考慮する前にしきい値に達すると、オプティマイザーのタイムアウト制限に達します。オプティマイザータイムアウトイベントは、クエリプランの [ステートメントの最適化の早期終了の理由] で TimeOut として報告されます。このしきい値は、クロック時間ではなく、オプティマイザーによって考慮される可能性の数に基づいていることを理解することが重要です。現在の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の左端のプラン演算子のプロパティを確認します。 ステートメントの最適化の早期終了の理由の値は TimeOut です。

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

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

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

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

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

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

クエリ述語 (句の 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 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: ベースラインを確立する

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

手順 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 つの大きなテーブル結合として最適化されます。

多数の結合を持つ 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 が計画を選択する際に考慮する必要がある可能性を減らしてください。このプロセスでは、さまざまなヒントオプションを使用してクエリをテストします。 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 以降のバージョンのデータベース互換性レベルを使用ALTER DATABASE SCOPED CONFIGURATION ..QUERY_OPTIMIZER_HOTFIXES = ONまたは変更します。

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

手順 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: より高速に実行されるベースラインクエリがある場合は、そのクエリプランを使用します