SQL Server 2012 以前から 2014 以降へのアップグレード後のクエリ パフォーマンスの低下

SQL Serverを 2012 以前のバージョンから 2014 以降のバージョンにアップグレードした後、次の問題が発生する可能性があります。元のクエリのほとんどは適切に実行されますが、クエリの一部は以前のバージョンよりも実行速度が遅くなります。 考えられる原因と要因は多数ありますが、比較的一般的な原因の 1 つは、アップグレード後の カーディナリティ推定 (CE) モデルの変更です。 2014 年SQL Serverから CE モデルに大幅な変更が導入されました。

この記事では、既定の CE を使用するときに発生するが、従来の CE を使用する場合には発生しないクエリ パフォーマンスの問題のトラブルシューティング手順と解決策について説明します。

注:

アップグレード後にすべてのクエリの実行速度が遅い場合、この記事で紹介するトラブルシューティング手順は、状況に適用されない可能性があります。

トラブルシューティング: CE の変更が問題であるかどうかを特定し、その理由を確認する

手順 1: 既定の CE が使用されているかどうかを特定する

1. アップグレード後に実行速度が低下するクエリを選択します。
2. クエリを実行し、 実行プランを収集します。
3. 実行プランのプロパティ ウィンドウから、CardinalityEstimationModelVersion をチェックします。
4. 値 70 はレガシ CE を示し、値 120 以上は既定の CE の使用を示します。

レガシ CE が使用されている場合、CE の変更はパフォーマンスの問題の原因ではありません。 既定の CE が使用されている場合は、次の手順に進みます。

手順 2: クエリ オプティマイザーが従来の CE を使用してより優れたプランを生成できるかどうかを特定する

レガシ CE を使用してクエリを実行します。 既定の CE を使用するよりもパフォーマンスが優れている場合は、次の手順に進みます。 パフォーマンスが向上しない場合は、CE の変更が原因ではありません。

手順 3: レガシ CE でクエリのパフォーマンスが向上する理由を確認する

クエリのさまざまな CE 関連 のクエリ ヒント をテストします。 SQL Server 2014 の場合は、対応するトレース フラグ 4137、9472、4139 を使用してクエリをテストします。 これらのテストに基づいて、パフォーマンスにプラスの影響を与えるヒントまたはトレース フラグを決定します。

解決方法

この問題を解決するには、次のいずれかの方法を試してください。

• クエリを最適化します。

  当然ながら、クエリを書き換えることができるとは限りませんが、特に書き換え可能なクエリが少ない場合は、この方法が最初の選択である必要があります。 CE のバージョンに関係なく、最適に記述されたクエリのパフォーマンスが向上します。

• 手順 3 で特定したクエリ ヒントを使用します。

  このターゲットアプローチにより、他のワークロードは、既定の CE の前提条件と改善の恩恵を受けることができます。 さらに、プラン ガイドを作成するよりも堅牢なオプションです。 また、プランの強制 (最も堅牢なオプション) とは異なり、クエリ ストア (QDS) は必要ありません。

• 適切な計画を強制する。

  これは適切なオプションであり、特定のクエリのターゲットに使用できます。 プランの強制は、 プラン ガイド または QDS を使用して行うことができます。 QDS の方が一般的に使いやすいです。

• データベース スコープの構成を使用して、レガシ CE を強制します。

  これはデータベース全体の設定であり、このデータベースに対するすべてのクエリに適用されるため、あまり好ましい方法ではありません。 それでも、ターゲットを絞ったアプローチが実現できない場合は、必要になることがあります。 それは確かに実装する最も簡単なオプションです。

• トレース フラグ 9841 を使用して、レガシ CE をグローバルに強制します。 これを行うには、 DBCC TRACEON を 使用するか、トレース フラグを スタートアップ パラメーターとして設定します。

  これは最もターゲットが絞り込まれていないアプローチであり、他のオプションを適用できない場合にのみ、一時的な軽減策として使用する必要があります。

レガシ CE を有効にするオプション

クエリ レベル: クエリ ヒントまたは QUERYTRACEON オプションを使用する

• SQL Server 2016 SP1 以降のバージョンの場合は、クエリにヒントFORCE_LEGACY_CARDINALITY_ESTIMATIONを使用します(例:

  SELECT * FROM Table1
  WHERE Col1 = 10
  OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));
  

• トレース フラグ 9481 を有効にして、従来の CE プランを強制します。 次に例を示します:

  SELECT * FROM Table1
  WHERE Col1 = 10
  OPTION (QUERYTRACEON 9481)
  

データベース レベル: スコープ付き構成または互換性レベルを設定する

• SQL Server 2016 以降のバージョンの場合は、データベース スコープの構成を変更します。

    --Force a specific database to use legacy CE
    ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;
  
    -- Validate what databases use legacy CE
    SELECT name, value
        FROM sys.database_scoped_configurations 
    WHERE name = 'LEGACY_CARDINALITY_ESTIMATION';
  

• データベースの互換性レベルを変更します。 これは、SQL Server 2014 で使用できる唯一のデータベース レベルのオプションです。 この変更は、CE 以上の影響を与える点に注意してください。 互換性レベルの変更の影響を判断するには、 ALTER DATABASE 互換性レベル (Transact-SQL) に移動し、その中の "相違点" テーブルを調べます。

  ALTER DATABASE <YourDatabase>
  SET COMPATIBILITY_LEVEL = 110  -- set it to SQL Server 2012 level
  

注:

この変更は、オーバーライドするトレース フラグまたはクエリ ヒントを使用しない限り、構成が変更されたデータベースのコンテキスト内で実行されるすべてのクエリに影響します。 既定の CE が原因でパフォーマンスが向上するクエリは、低下する可能性があります。

サーバー レベル: トレース フラグを使用する

トレース フラグ 9481 を使用して、サーバー全体のレガシ CE を強制します。

--Turn on 
DBCC TRACEON(9481, -1)
--Validate
DBCC TRACESTATUS

注:

この変更は、オーバーライドするトレース フラグまたはクエリ ヒントを使用しない限り、SQL Server インスタンスのコンテキスト内で実行されるすべてのクエリに影響します。 既定の CE が原因でパフォーマンスが向上するクエリは、低下する可能性があります。

よく寄せられる質問

Q1: より新しいバージョンのSQL Serverにアップグレードすることに興味があり、カーディナリティ推定機能のパフォーマンス低下が心配です。 問題を最小限に抑えるために推奨されるアップグレード計画

より低い互換性レベルで実行されている既存のデータベースの場合、クエリ プロセッサをより高い互換性レベルにアップグレードするための推奨されるワークフローについては、「データベース互換性モードの変更」および「クエリ ストアを使用する」および「クエリ ストア使用シナリオ」を参照してください。 この記事で紹介する手法は、SQL Server と Azure SQL Database の 130 以上への移動に適用されます。

Q2: CE の変更をテストする時間がありません。 この場合、何ができますか?

既存のアプリケーションとワークロードの場合、十分な回帰テストが実行されるまで、既定の CE に移行することはお勧めしません。 それでも疑問がある場合は、引き続きSQL Serverアップグレードし、使用可能な最新の互換性レベルに移行することをお勧めします。 予防措置として、SQL Server 2014 のトレース フラグ 9481 を有効にするか、テストする機会が得るまで、SQL Server 2016 以降のバージョンのLEGACY_CARDINALITY_ESTIMATION データベース スコープ構成を構成ONします。

Q3: 従来の CE を永続的に使用する場合の欠点はありますか?

今後のカーディナリティ推定に関連する機能強化と修正は、より新しいバージョンを中心にしています。 バージョン 70 は、許容される中間状態です。 ただし、慎重にテストした後は、最終的に最新の CE バージョンに移行して、最新の CE 修正の恩恵を受けるようにすることをお勧めします。 従来の CE から移行するとクエリ プランが変更される可能性が高いため、運用システムに変更を加える前にテストします。 この変更により、多くの場合、クエリのパフォーマンスが向上しますが、場合によってはクエリのパフォーマンスが低下する可能性があります。

重要

既定の CE は、将来の投資と長期的なテストカバレッジを受け取るメインコード パスであるため、レガシ CE を無期限に使用することを計画しないでください。

Q4:何千ものデータベースがあり、それぞれに対して手動でLEGACY_CARDINALITY_ESTIMATIONをオンにしたくありません。 別の方法はありますか?

SQL Server 2014 の場合は、互換性レベルに関係なく、トレース フラグ 9481 を有効にして、すべてのデータベースにレガシ CE を使用します。 SQL Server 2016 以降のバージョンの場合は、次のクエリを実行してデータベースを反復処理します。 この設定は、データベースが復元または別のサーバーに接続されている場合でも有効になります。

SELECT [name], 0 AS [isdone]
INTO #tmpDatabases
FROM master.sys.databases WITH (NOLOCK)
WHERE database_id > 4 AND source_database_id IS NULL AND is_read_only = 0

DECLARE @dbname sysname, @sqlcmd NVARCHAR(500);

WHILE (SELECT COUNT([name]) FROM #tmpDatabases WHERE isdone = 0) > 0
BEGIN
    SELECT TOP 1 @dbname = [name] FROM #tmpDatabases WHERE isdone = 0

    SET @sqlcmd = 'USE ' + QUOTENAME(@dbname) + '; 
        IF (SELECT [value] FROM sys.database_scoped_configurations WHERE [name] = ''LEGACY_CARDINALITY_ESTIMATION'') = 0
        ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;'
 
    BEGIN TRY
        EXECUTE sp_executesql @sqlcmd
    END TRY
    BEGIN CATCH
        SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity,
            ERROR_STATE() AS ErrorState, ERROR_MESSAGE() AS ErrorMessage;
    END CATCH

    UPDATE #tmpDatabases
    SET isdone = 1
    WHERE [name] = @dbname
END;

Azure SQL Database では、サポート チケットを作成して、このトレース フラグをサブスクリプション レベルで有効にできますが、サーバー レベルでは有効にすることはできません。

Q5: レガシ CE を使用して実行すると、新機能にアクセスできなくなりますか?

LEGACY_CARDINALITY_ESTIMATIONが有効になっている場合でも、SQL Serverのバージョンと関連するデータベース互換性レベルに含まれる最新の機能に引き続きアクセスできます。 たとえば、SQL Server 2017 でデータベース互換性レベル 140 で実行LEGACY_CARDINALITY_ESTIMATIONが有効になっているデータベースは、アダプティブ クエリ処理機能ファミリの恩恵を受けることができます。

Q6: レガシ CE はいつサポート対象外になりますか?

現時点では、レガシ CE のサポートを停止する予定はありません。 ただし、今後のカーディナリティ推定に関連する機能強化と修正は、CE のより新しいバージョンを中心にしています。

Q7: 既定の CE で回帰するクエリはごくわずかですが、ほとんどのクエリ パフォーマンスは同じか改善されています。 どうすればよいですか?

サーバースコープトレースフラグ9481またはLEGACY_CARDINALITY_ESTIMATIONデータベーススコープ構成のより詳細な代替手段は、クエリスコープの USE HINT コンストラクトを使用することです。 詳細については、「USE HINT query hint argument in SQL Server 2016」および「USE HINT」を参照してください。

注:

トレース フラグ 9481 のオプションもありますが QUERYTRACEON 、代わりに を使用 USE HINT することを検討する必要があります。これは意味的にわかりやすく、特別なアクセス許可は必要ないためです。

USE HINT FORCE_LEGACY_CARDINALITY_ESTIMATION では、データベースの互換性レベルに関係なく、クエリ オプティマイザー CE モデルをバージョン 70 に設定できます。 「クエリ レベル: クエリ ヒントまたは QUERYTRACEON オプションを使用する」を参照してください。

または、既定の CE に問題があるクエリが 1 つだけある場合は、クエリ ストアに格納されている従来の CE プランを強制するか、プラン ガイドと組み合わせて使用FORCE_LEGACY_CARDINALITY_ESTIMATIONすることもできます。

Q8: 既定の CE を使用する場合に、大幅な超過または見積もり不足に関連するプランの変更によってクエリのパフォーマンスが低下した場合、問題は製品で修正されますか?

CE は複雑な問題であり、アルゴリズムは、テーブルやインデックスの統計など、見積もりに使用できる完全ではないデータに依存しています。 テーブル値関数 (TVF) や、多くの仮定 (述語と列の相関関係や独立性、均一なデータ分布、包含など) に基づくモデルなど、モデル外のコンストラクトに関する情報はありません。

顧客のスキーマ、データ、ワークロードが無制限に組み合わされているため、すべてのケースで機能するモデルを選択することはほとんど不可能です。 既定の CE の一部の変更には(他のソフトウェアと同様に) バグが含まれており、修正できますが、他の問題はモデルの変更によって引き起こされます。

CE バージョンの変更 (特に 70 から 120) には、使用されるモデルに対してさまざまな選択肢が含まれます。 たとえば、フィルターを見積もる場合は、述語間にある程度の相関関係があると仮定します。実際には、そのような相関関係が頻繁に存在し、CE モデル 70 はそのような場合に結果を過小評価するためです。 これらの変更は多くのワークロードでテストされ、多くのクエリが改善されましたが、他のクエリではレガシ CE の方が一致し、既定の CE ではパフォーマンスの低下が観察される場合があります。

残念ながら、バグとは見なされません。 このような場合は、クエリのパフォーマンスが許容できない場合にレガシ CE で実行する必要がある場合や、以前の CE モデルまたは特定の実行プランを強制する場合と同様に、クエリのチューニングなどの回避策を使用します。

Q9: 既定の CE のカーディナリティの変更とクエリ パフォーマンスへの影響の詳細を学習するためのリソースはありますか?

詳細については、「SQL Server 2014 カーディナリティ推定ツールを使用したクエリ プランの最適化」を参照し、「SQL Server 2014 で変更された内容」セクションを参照してください。