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

SQL Server を 2012 以前のバージョンから 2014 以降のバージョンにアップグレードすると、次の問題が発生する可能性があります。元のクエリのほとんどは適切に実行されますが、一部のクエリの実行速度は以前のバージョンよりも遅くなります。 多くの原因と要因が考えられますが、比較的一般的な原因の 1 つは、アップグレード後の Cardinality Estimation (CE) モデルの変更です。 SQL Server 2014 以降の 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 関連のさまざまな query-hints をテストします。 SQL Server 2014 では、対応するトレース フラグ 4137、 9472、および 4139 を使用してクエリをテストします。 これらのテストに基づいて、パフォーマンスにプラスの影響を与えるヒントまたはトレース フラグを決定します。

解決方法

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

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

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

• Step 3 で識別されたクエリ ヒントを使用します。

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

• 適切な計画を強制します。

  これは好ましいオプションであり、特定のクエリを対象とするために使用できます。 プランの強制は、 プラン ガイド または QDS を使用して行うことができます。 QDS は一般に使いやすいです。

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

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

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

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

レガシ 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
  

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

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

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

よく寄せられる質問

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 から移行するときにクエリ プランが変更される可能性が高いため、運用システムに変更を加える前にテストしてください。 この変更により、多くの場合、クエリのパフォーマンスが向上しますが、場合によってはクエリのパフォーマンスが低下する可能性があります。

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

SQL Server 2014 では、互換性レベルに関係なく、すべてのデータベースでレガシ CE を使用するようにトレース フラグ 9481 を有効にします。 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 コンストラクトを使用することです。 詳細については、SQL Server 2016 の USE HINT クエリ ヒント引数および USE HINT を参照してください。

USE HINT FORCE_LEGACY_CARDINALITY_ESTIMATION では、データベースの互換性レベルに関係なく、クエリ オプティマイザー CE モデルをバージョン 70 に設定できます。 「 Query レベル: クエリ ヒントまたは 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 での変更点」セクションを参照してください。