Microsoft SQL Server 2016 実践シリーズ （HTML 版）
「SQL Server 2016 への移行とアップグレードの実践」

3.13　クエリストアで互換性レベルの違いによる実行プランの比較

互換性レベルを変更したことによる性能調査（実行プランの比較）には、SQL Server 2016 から提供された「クエリ ストア」（Query Store）機能を利用するのがお勧めです。クエリ ストアは、Store（蓄積）という名のとおり、クエリの実行履歴／実行プランを保存できる機能です。

これまでのバージョンでも query_stats という動的管理ビュー（DMV）を参照すれば、クエリの実行履歴を確認することができましたが、この情報は、SQL Server を再起動したり、プロシージャ キャッシュのサイズが圧迫された場合には、参照できない（クリアされてしまう）という性質のものでした。これに対して、クエリ ストアであれば、クエリの実行履歴を永続化することができるので、SQL Server を再起動しても、過去に遡ってクエリの実行履歴を確認することができます。

クエリ ストアでは、クエリの実行履歴だけでなく、実行プランも記録しておくことができ、かつ異なる実行プランで実行された場合には、その複数の実行プランを比較することができます。

同じクエリに対して、複数の実行プランがある場合には、どちらを利用させたいのかを指定（データベース管理者によるプラン強制／固定）することもできるので、互換性レベルを変更する前後で、性能が良いほうの実行プランを強制利用させる、といった使い方ができます。

◆ 互換性レベルの違いを評価する手順

互換性レベルを変更するにあたっては、次のようにクエリ ストアを利用するのがお勧めです。

1．互換性レベルはアップグレードした直後のものを利用する（SQL Server 2008／2008 R2 なら 100、SQL Server 2012 なら 110、SQL Server 2014 なら 120 をそのまま利用しておく）

2．データベースでクエリ ストアを有効化する

3．アプリケーションを実行して、データベースに対してクエリを発行する、あるいは、アプリケーションから発行されるクエリ（トレースで取得したものや .sql ファイルなど、ワークロードをスクリプト化したもの）をデータベースに対して実行する

4．互換性レベルを 130（SQL Server 2016 レベル）に上げる

5．手順 3 と同じクエリ（ワークロード）をデータベースに対して実行する

6．クエリ ストアを参照して、実行プランの違いを確認する

7．複数の実行プランがあるものは、最適な実行プランを利用するように、必要に応じてプラン強制を実施する

◆ クエリ ストアの有効化

クエリ ストアを有効化するには、次のようにデータベースを右クリックして、［プロパティ］をクリックします。

［データベースのプロパティ］ダイアログでは、［クエリ ストア］ページを開いて、［操作モード（要求時）］を［読み取り/書き込み］に変更し、［OK］ボタンをクリックします。

以上でクエリ ストアの設定が完了です。これでクエリ ストアが有効になって、クエリの実行履歴が自動的に記録されていくようになります。

クエリ ストアを有効化すると、次のようにデータベース内に［クエリ ストア］フォルダーが作成されて、記録されたクエリを確認することができます。

クエリ ストアの設定後は、アプリケーションを実行して、データベースに対してクエリを発行、あるいは、アプリケーションから発行されるクエリ（トレースで取得したものや .sql ファイルなど、ワークロードをスクリプト化したもの）をデータベースに対して実行します。

実行が完了したら、データベースの互換性レベルを 130（SQL Server 2016 レベル）に変更します。

互換性レベルを変更したら、互換性レベルを変更する前と同じクエリ（ワークロード）をデータベースに対して実行します。

実行が完了したら、データベース内の［クエリ ストア］フォルダーを展開して、［リソースを消費するクエリの上位］をクリックします。

左側のグラフは、合計実行時間の長い順にクエリが表示されて、右側のグラフには、選択したクエリの実行プランが表示されます。右側のグラフの Y軸は、合計実行時間になっていて、実行プランごとの合計実行時間（各実行プランで実行されたクエリの合計の実行時間）で、どの実行プランが速く実行できたのかを一目瞭然で分かるようになっています。

この実行プランのグラフに、複数の実行プランが表示されているということは、互換性レベルを変更する前後で異なる実行プランで実行されていたということにもなります。

これに対して、次のように 1つの実行プランしか表示されない場合は、互換性レベルを変更しても全く同じ実行プランが利用されたということが分かります。

複数の実行プランがある場合（互換性レベルの影響があった場合）は、次のように Shift キーを押しながら 2つのプランID をクリックして選択して、ツールバーの［選択したクエリのプランを、別々のウィンドウで比較します］をクリックすることで、実行プランを並べて比較することもできます。

このように並べて表示することで、どこが異なるのかを非常に分かりやすく確認することができます。これで、合計実行時間や、内部的な実行プランを確認できるので、このうち、効率の良い実行プランを確実に利用させるようにするには、実行プランのグラフの上部に［プランの強制］ボタンがあるので、これをクリックするだけで行うことができます。

このように、クエリ ストア機能を利用すれば、互換性レベルの違いによる性能差を簡単に確認することができるので、ぜひ利用してみてください。

なお、その他のクエリ ストアの利用方法については、SQL Server 2016 自習書シリーズの No.1「SQL Server 2016 からの新機能の概要」編でも説明しているので、こちらもぜひご覧いただければと思います。

その他、互換性レベルに応じた実行プランについては、オンライン ブックの以下のトピックも参考になると思います。

クエリ プランの移行
http://msdn.microsoft.com/ja-jp/library/bb895281.aspx

◆ データベース単位での動作変更（データベース スコープ構成）

SQL Server 2016 からは、いくつかのクエリ プロセッサの動作をデータベース単位で変更できるようになっています。これは「データベース スコープ構成」と呼ばれて、次のようにデータベースのプロパティの「オプション」ページで設定できます。

データベース スコープ構成では、MAXDOP（並列クエリ時の利用する CPUコア数の制限）や CE（基数見積）の設定を変更できるので便利です。CE は、SQL Server 2014（互換性レベル 120）から変更された新しいアーキテクチャを利用する場合は「レガシ基数見積」を「オフ」、SQL Server 2012 以前と同じ CE を利用したい場合は「オン」に設定します。

データベース スコープ構成は、ALTER DATABASE SCOPED CONFIGURATION ステートメントを利用して、次のように設定することもできます。

これらの設定を変更した場合も、クエリ ストア機能を利用すれば、設定前後での実行プランを簡単に比較することができるので、その設定が妥当なのかどうかをチェックできて、大変便利です。