クエリチューニングアシスタントを使用したデータベースのアップグレード

適用対象: SQL Server 2016 (13.x) 以降のバージョン

以前のバージョンの SQL Server から SQL Server 2014 (12.x) 以降のバージョンに移行し、データベース互換性レベルを使用可能な最新バージョンにアップグレードすると、ワークロードがパフォーマンス低下のリスクにさらされる可能性があります。程度はかなり低くなりますが、SQL Server 2014 (12.x)と新しいバージョン間のアップグレード時にもその可能性はあります。

SQL Server 2014 (12.x) 以降のバージョンでは、すべてのクエリオプティマイザーの変更が最新のデータベース互換性レベルに制限されるため、アップグレード時に実行プランが変更されるのではなく、ユーザーが COMPATIBILITY_LEVEL データベースオプションを最新の使用可能な状態に変更した場合。 SQL Server 2014 (12.x) で導入されたクエリオプティマイザーの変更の詳細については、「カーディナリティ推定 (SQL Server)」を参照してください。互換性レベルとそれがアップグレードにどのように影響する可能性があるかについて詳しくは、「互換性レベルとデータベースエンジンのアップグレード」を参照してください。

データベース互換性レベルによって提供されるこのゲーティング機能は、クエリストアと組み合わせて使用することで、アップグレードが次の図に示す推奨されるワークフローに従っている場合に、アップグレードプロセスのクエリパフォーマンスを優れたレベルで制御できます。互換性レベルをアップグレードするための推奨ワークフローの詳細については、「データベース互換性レベルを変更してクエリストアを使用する」を参照してください。

アップグレードに対するこの制御は、自動チューニングが導入された SQL Server 2017 (14.x) によってさらに強化され、推奨されるワークフローの最後の手順を自動化できるようになりました。

SQL Server Management Studio v18 以降、クエリチューニングアシスタント (QTA) 機能は、「クエリ ストアの使用シナリオの新しい SQL Server へのアップグレード中にパフォーマンスの安定性を維持する」セクションに記載されているように、新しい SQL Server バージョンへのアップグレード中にパフォーマンスの安定性を維持するために、推奨されるワークフローをユーザーに案内します。ただし、推奨されるワークフローの最後の手順で示されているように、QTA は以前の既知の適切なプランにロールバックしません。代わりに、QTA はクエリストア の回帰 クエリビューで見つかった回帰を追跡し、適用可能なオプティマイザーモデルバリエーションの可能な順列を反復処理して、新しいより良いプランを作成できるようにします。

重要

QTA では、ユーザーワークロードは生成されません。アプリケーションで使用されていない環境で QTA を実行する場合は、別の方法でターゲットとなる SQL Server データベースエンジンで代表的なテストワークロードを引き続き確実に実行できるようにします。

クエリ調整アシスタントのワークフロー

QTA の開始点では、以前のバージョンの SQL Server のデータベースが ( データベースのアタッチまたは RESTORE ステートメントを使用して) 新しいバージョンの SQL Server データベースエンジンに移動され、アップグレード前のデータベース互換性レベルが直ちに変更されることはありません。 QTA では、次の手順がガイドされます。

ユーザーが設定したワークロード期間 (日単位) の推奨される設定に従って、クエリストアを構成します。一般的なビジネスサイクルに一致するワークロード期間について考えてみます。
クエリストアでワークロードデータのベースラインを収集できるように (まだ使用できるものがない場合)、必要なワークロードの開始を要求します。
ユーザーが選択したターゲットのデータベース互換レベルにアップグレードします。
比較と機能低下の検出のために、ワークロードデータの 2 番目の受け渡しが収集されるように要求します。
クエリストアの [機能低下したクエリ] ビューに基づいて検出された機能低下を繰り返し処理し、適用可能なオプティマイザーモデルバリエーションの可能な順列で実行時統計を収集して実験し、その結果を測定します。
測定された改善について報告し、必要に応じて、プランガイドを使用して変更を保持できるようにします。

データベースのアタッチの詳細については、データベースのデタッチとアタッチに関するページを参照してください。

次の図は、QTA が、前に示したクエリストアを使用して互換性レベルをアップグレードするための推奨ワークフローの最後の手順のみを変更する方法を示しています。 QTA では、現在非効率的な実行プランと既知の適切な実行プランのどちらを選択する代わりに、選択した後退したクエリに固有のチューニングオプションが提示され、チューニングされた実行プランで新しい改善された状態が作成されます。

QTA での内部検索領域の調整

QTA では、クエリストアから実行できる SELECT クエリのみがターゲットとなります。コンパイル済みのパラメーターがわかっている場合は、パラメーター化されたクエリが対象となります。一時テーブルやテーブル変数などの実行時の構造に依存するクエリは、ここでは対象となりません。

QTA は、カーディナリティ推定 (SQL Server) バージョンの変更によるクエリ回帰の既知の可能性のあるパターンを対象とします。たとえば、データベースを SQL Server 2012 (11.x) とデータベース互換性レベル 110 から SQL Server 2017 (14.x) およびデータベース互換性レベル 140 にアップグレードすると、SQL Server 2012 (11.x) (CE 70) に存在する CE バージョンで動作するように特別に設計されているため、一部のクエリが低下することがあります。これは、CE 140 から CE 70 に戻すことが唯一のオプションであるという意味ではありません。新しいバージョンの特定の変更のみが回帰を導入している場合は、そのクエリで、特定のクエリに対してより適切に動作していた以前の CE バージョンの関連部分のみを使用し、新しい CE バージョンの他のすべての機能強化を引き続き使用するように、そのクエリをヒントできます。また、機能が低下していない、ワークロード内の他のクエリでは、新しい CE の機能強化による利点が得られます。

QTA で検索される CE パターンは次のとおりです。

独立と相関関係: 独立の前提が特定のクエリに対してより適切な見積もりを提供する場合、クエリヒント USE HINT ('ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES') により、相関関係を考慮するフィルターの述語 AND 推定するときに、最小限の選択性を使用して SQL Server によって実行プランが生成されます。詳細については、USE HINT クエリヒントに関する記述と、「CE のバージョン」を参照してください。
単純包含と基本包含: 別の結合包含が特定のクエリに対してより適切な見積もりを提供する場合、クエリヒント USE HINT ('ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS') により、既定の基本包含の前提条件ではなく単純包含の前提条件を使用して SQL Server によって実行プランが生成されます。詳細については、USE HINT クエリヒントに関する記述と、「CE のバージョン」を参照してください。
100 行の 複数ステートメントのテーブル値関数 (MSTVF) の固定カーディナリティ推定 vs.1行：100行のTVFsの既定の固定推定が、(SQL Server 2008 R2 (10.50.x) と以前のバージョンの、クエリオプティマイザー CE モデルでの既定値に対応する) 1 行の TVF の固定推定を使用する場合よりも効率的なプランが得られない場合、実行プランを生成するためにクエリヒント QUERYTRACEON 9488 が使用されます。 MSTVF の詳細については、「ユーザー定義関数の作成 (データベースエンジン)」を参照してください。

最後の手段として、狭いスコープのヒントで、対象となるクエリパターンに対して十分良い結果が得られない場合は、実行プランを生成するためにクエリヒント USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION') を使用して、CE 70 を最大限に活用することも考慮されます。

重要

任意のヒントにより、将来の SQL Server 更新プログラムで対処される可能性のある特定の動作が強制されます。他のオプションが存在しない場合にのみヒントを適用し、新しいアップグレードのたびにヒントコードに再度アクセスするよう計画することをお勧めします。動作を強制することにより、ワークロードが新しいバージョンの SQL Server で導入された拡張機能の恩恵を受けられなくなる可能性があります。

データベースアップグレードのためのクエリチューニングアシスタントの起動

QTA はセッションベースの機能であり、セッションが初めて作成される、ユーザーデータベースの msqta スキーマにセッション状態を格納します。複数の調整セッションを単一データベースに時間をかけて作成することはできますが、特定のデータベースに存在できるのは 1 つのアクティブセッションのみです。

データベースのアップグレードセッションの作成

SQL Server Management Studio でオブジェクトエクスプローラーを開き、データベースエンジンに接続します。
データベース互換レベルをアップグレードすることを目的とするデータベースでは、データベース名を右クリックして、[タスク]、[データベースのアップグレード] の順に選択し、[新しいデータベースのアップグレードセッション] をクリックします。
QTA ウィザードウィンドウで、セッションを構成するには、以下の 2 つのステップが必要です。
1. [セットアップ] ウィンドウで、分析して調整するワークロードデータの 1 つの完全なビジネスサイクルに相当するものをキャプチャするように、クエリストアを構成します。
  - 予期されるワークロード期間を日単位で入力します (最小値は 1 日)。これは、ベースライン全体の収集を仮に許可するために推奨されるクエリストア設定を提案するために使用されます。適切なベースラインのキャプチャは、データベース互換レベルの変更後に検出される機能低下したクエリを確実に分析できるようにするために重要なことです。
  - QTA ワークフローが完了した後、ユーザーデータベースで使用する必要がある、目的のターゲットデータベース互換レベルを設定します。
  完了したら、[次へ] を選択します。
2. [設定] ウィンドウの 2 つの列には、対象データベースのクエリストアの現在の状態と推奨設定が表示されます。
  - 既定では [推奨] 設定が選択されますが、[現在] 列のラジオボタンを選択すると、現在の設定が受け入れられます。また、現在のクエリストア構成を微調整することもできます。
  - 提案された 古いクエリしきい値 の設定は、予想されるワークロード期間の値の 2 倍 (日数) です。これは、クエリストアがベースラインワークロードとデータベースアップグレード後のワークロードに関する情報を保持する必要があるためです。
  完了したら、[次へ] を選択します。
  
  重要
  
  提案される 最大サイズ は、短い時間のワークロードに適した任意の値です。ただし、多くの異なるプランが生成される可能性がある場合、集中型ワークロードのベースラインワークロードとデータベースアップグレード後ワークロードに関する情報を保持するには不十分な場合があります。このようなことが予想される場合は、適切な高い値を入力します。
[調整] ウィンドウでセッション構成が終了し、次のステップでセッションを開いて続行するよう指示されます。完了したら、[完了] を選択します。

$[New database upgrade tuning]$新しいデータベースアップグレードチューニング$ ウィンドウのスクリーンショット。$

データベースのアップグレードワークフローの実行

データベース互換レベルをアップグレードすることを目的とするデータベースでは、データベース名を右クリックして、[タスク]、[データベースのアップグレード] の順に選択し、[モニターセッション] をクリックします。
セッション管理ページには、スコープ内のデータベースの現在および過去のセッションがリストされます。目的のセッションを選択し、[詳細] を選択します。

Note

現在のセッションが存在しない場合は、[更新] ボタンを選択します。

このリストには、次の情報が含まれています。
- セッション ID
- セッション名: データベースの名前とセッションの作成日時で構成された、システムによって生成される名前。
- 状態: セッションの状態 (アクティブまたは終了)。
- 説明: システムによって生成され、ユーザーが選択したターゲットデータベース互換レベルと、ビジネスサイクルワークロードの日数で構成されます。
- 開始時刻: セッションが作成された日時。
Note

[セッションの削除] では、選択されたセッションの格納データがすべて削除されます。しかし、終了したセッションを削除しても、以前に展開されたプランガイドは削除されません。プランガイドをデプロイしたセッションを削除した場合、QTA を使用してロールバックすることはできません。代わりに、sys.plan_guides システムテーブルを使用してプランガイドを検索し、sp_control_plan_guide を使用して手動で削除します。
新しいセッションのエントリポイントは、データの収集ステップです。

Note

[セッション] ボタンを押すと、セッション管理ページに戻り、アクティブなセッションはそのままになります。

この手順には、次の 3 つのサブステップがあります。
1. [ベースラインデータの収集] では、クエリストアでベースラインを収集できるように、ユーザーに代表的なワークロードサイクルの実行が要求されます。そのワークロードが完了したら、[ワークロードの 実行で完了] を確認し、[ 次へ] を選択します。
  
  Note
  
  ワークロードの実行中に、QTA ウィンドウを閉じてもかまいません。後でアクティブな状態のままのセッションに戻ると、中断されたのと同じ手順から再開されます。
2. データベースのアップグレード では、データベース互換性レベルを目的のターゲットにアップグレードするためのアクセス許可を求められます。次のサブステップに進むには、[はい] を選択します。
  
  次のページでは、データベース互換レベルが正常にアップグレードされたことを確認します。
3. 監視データ収集 では、クエリストアが最適化の機会を検索するために使用される比較ベースラインを収集できるように、代表的なワークロードサイクルを再度実行するようユーザーに要求します。ワークロードが実行されたら、[更新] ボタンを使用して、機能低下したクエリ (検出された場合) のリストを更新し続けます。 [表示するクエリ] の値を変更し、表示されるクエリの数を制限します。リストの順序は、[メトリック] (期間または CpuTime) と [集計] (平均が既定値) に影響を受けます。 表示するクエリの数も選択します。そのワークロードが完了したら、[ワークロードの 実行で完了] を確認し、[ 次へ] を選択します。
  
  このリストには、次の情報が含まれています。
  - クエリ ID
  - クエリテキスト: ... ボタンを選択することで展開できる Transact-SQL ステートメント。
  - 実行: ワークロード全体の収集に対するクエリの実行回数が表示されます。
  - ベースラインメトリック: データベース互換性アップグレードの前のベースラインデータ収集に対して選択されたミリ秒単位のメトリック (期間または CpuTime)。
  - 観測されたメトリック: データベース互換性アップグレードの後のデータデータ収集に対して選択されたミリ秒単位のメトリック (期間または CpuTime)。
  - 変更 %: データベース互換性アップグレードの前と後の状態の間の選択されたメトリックの変更率。負の数は、クエリの観測された機能低下の量を表します。
  - 調整可能: クエリが実験の対象となるかどうかに応じて、True または False となります。
[分析の表示] では、実験対象のクエリを選択し、最適化の機会を見つけることができます。 [表示するクエリ] の値は、実験対象となるクエリのスコープになります。必要なクエリが確認されたら、[次へ] を選択して実験を開始します。

Tunable が False に設定されているクエリは、実験用に選択できません。

重要

QTA が実験フェーズに移行する際に、[分析の表示] ページに戻ることはできないという通知が表示されます。実験フェーズに移る前に対象となるすべてのクエリを選択しない場合は、後で新しいセッションを作成し、ワークフローを繰り返す必要があります。その場合、データベース互換レベルを元の値にリセットする必要があります。
[結果の表示] では、プランガイドとして提案された最適化を展開するクエリを選択できます。

このリストには、次の情報が含まれています。
- クエリ ID
- クエリテキスト: ... ボタンを選択することで展開できる Transact-SQL ステートメント。
- 状態: クエリの現在の実験の状態が表示されます。
- ベースラインメトリック: データベース互換性アップグレードの後の機能低下したクエリを表す、ステップ 2 のサブステップ 3 で実行されるクエリに対して選択されたミリ秒単位のメトリック (期間または CpuTime)。
- 観測されたメトリック: 十分良好な提案された最適化について、実験後のクエリに対して選択されたミリ秒単位のメトリック (期間または CpuTime)。
- % 変更: 実験の前と後の状態の間の選択されたメトリックの変化率を指定します。これは、提案された最適化を使用したクエリの測定された改善の量を表します。
- クエリオプション: クエリ実行メトリックを改善する提案されたヒントへのリンク。
- 展開可能: 提案されたクエリの最適化をプランガイドとして展開できるかどうかに応じて、True または False となります。
[検証] には、このセッションに対して前に選択されたクエリの展開状態が表示されます。このページのリストは前のページとは異なり、[展開可能] 列が [ロールバック可能] に変わっています。この列は、展開されたクエリの最適化をロールバックし、そのプランガイドを削除できるかどうかに応じて、True または False にすることができます。

後日、提案された最適化をロールバックする必要がある場合は、関連するクエリを選択し、[ ロールバック] を選択します。そのクエリプランガイドは削除され、リストはロールバックされたクエリを削除するために更新されます。以下の図でクエリ 8 が削除されていることに注目してください。

Note

終了したセッションを削除しても、以前に展開されたプランガイドは削除されません。プランガイドをデプロイしたセッションを削除した場合、QTA を使用してロールバックすることはできません。代わりに、sys.plan_guides システムテーブルを使用してプランガイドを検索し、sp_control_plan_guide を使用して手動で削除します。