SQL Serverのクエリプラン（実行計画）の仕組みと読み方を理解する

こんにちは。最近仕事で使うようになったためSQL Serverを勉強しているのですが、今日はその中でもSQL Serverのクエリプランの種類と動作について勉強したので、分かったことをこの記事にまとめておきたいと思います。

それではまいります。

クエリプラン（実行計画）とは

クエリを効率的に実行するために、データベースで作成される実行順序の計画のことを実行計画、またはクエリプランなどと呼びます。

以下は、SQL Serverのドキュメントによる説明ですが、クエリプランという概念は、SQL Serverに限らずOracle DatabaseなどRDBMSに一般的な仕組みです。

クエリを実行するには、SQL Server データベース エンジンを分析して、必要なデータに最も効率的にアクセスする方法を決定する必要があります。 この分析は、クエリ オプティマイザーと呼ばれているコンポーネントによって処理されます。 クエリ オプティマイザーへの入力は、クエリ、データベース スキーマ (テーブル定義やインデックスの定義)、およびデータベース統計で構成されます。 クエリ オプティマイザーの出力がクエリ実行プランです。これは、クエリ プランや実行プランと呼ばれることもあります。

https://docs.microsoft.com/ja-jp/sql/relational-databases/performance/execution-plans?view=sql-server-ver15

SQL Serverにおけるクエリ実行のながれ

参考：

https://docs.microsoft.com/ja-jp/learn/modules/describe-sql-server-query-plans/2-describe-types

https://atmarkit.itmedia.co.jp/ait/articles/1703/20/news001.html

https://enterprisezine.jp/article/detail/14146

まずは、クエリが通常どのような流れで実行されるのかを理解しておく必要があります。

上のMicrosoft公式ドキュメントの内容をまとめると、以下の順序でクエリ実行が進められていくと整理できそうです。

１点、①②の解析が③より前に行われるか、あとに行われるかは、公式ドキュメントと外部サイトで記載が異なるようでした。いったんは、公式ドキュメントが正しいと考えて、以下のように整理しました。（間違っていたらご指摘いただけますと幸いです）

1. クエリの構文が解析され、データベースオブジェクトの解析ツリーが生成される
2. データベースエンジンが解析ツリーを入力に、クエリプロセッサツリーを生成する
3. データベースエンジンが、クエリプロセッサツリーをもとに、プランキャッシュと呼ばれる場所に対して該当クエリのクエリプランが既に存在しているかチェックする。（このとき、クエリハッシュと呼ばれるクエリを一意に特定するキーを用いて検索される）
4. プランがプランキャッシュに存在しない場合には、クエリオプティマイザーがクエリで使用される列、テーブル、およびインデックスに関する統計情報を利用していくつかのクエリプランを生成する。
5. 作成したクエリプランはプランキャッシュに保存する
6. ③でプランキャッシュからプルされたプラン、または④でクエリオプティマイザによって生成されたクエリプランをもとにクエリを実行する

以下のクエリを例に上のステップの流れを再確認すると以下のようになります。

SELECT orderdate
,avg(salesAmount)
FROM FactResellerSales

WHERE ShipDate = '2013-07-07'
GROUP BY orderdate;

1. クエリの構文を解析（SELECT, FROM, WHERE, GROUP BYといったSQLの構造を認識）
2. FactResellerSales テーブルに OrderDate、ShipDate、および SalesAmount 列が存在するかどうかを確認
3. これらの列が存在する場合、クエリのハッシュ値を生成し、プラン キャッシュに一致するハッシュ値があるかどうかを調べる。ハッシュが一致するクエリプランがあればそのプランの再利用を試みる。
4. プランがなければOrderDate 列と ShipDate 列に関して使用できる統計を調べて、クエリオプティマイザによりプランが生成される
5. 作成されたプランの中で一番コストの低いプランに基づいてクエリを実行する

SQL Serverにおけるクエリプランの種類

参考：https://docs.microsoft.com/ja-jp/learn/modules/describe-sql-server-query-plans/2-describe-types

SQL Serverのクエリプランには、以下の３種類があります。

• 推定実行プラン
• 実際の実行プラン
• ライブクエリ統計

それぞれ以下のような特徴があります。

推定実行プラン

• 実際にクエリを実行する前に確認できるコンパイル済プラン
• クエリオプティマイザーによって生成される

実際の実行プラン

• 推定実行プランと同じプランに、実際の実行情報も付加されたプラン
• クエリオプティマイザーによって生成される
• 推定行数と実際の行数、実行の警告、実際の並列処理の次数 (使用されたプロセッサの数)、および実行中に使用された経過時間と CPU 時間

ライブクエリ統計

• 一秒ごとに更新される実行プラン。
• 時間のかかるクエリに対して、実際の実行状況を確認したい場合などに活用できる

クエリプランを見てみる

試しに、以下のようなクエリを実行して、クエリプランを確認してみたいと思います。

SELECT BusinessEntityID, NationalIDNumber, LoginID, HireDate, JobTitle
FROM HumanResources.Employee
WHERE NationalIDNumber = 14417807;

まずは、クエリ実行前に推定の実行プランを確認してみたいと思います。

推定の実行プランは、SSMSで対象のクエリを選択して右クリック＞推定実行プランの表示を押下することで、以下のようなビジュアルで表示されます。

それぞれの処理のヒントは以下の通り。

この中身を解釈してみたいと思います。

まず、クエリプランの基本的な読み方として、以下のことを抑えておきます。

これを踏まえると、今回の処理は以下の順序で行われる予定であることが分かります。

1. Index Seek：インデックスAK_Employee_NationalIDNumberをもとに条件に合致する行（1行）を抽出（コスト約50%）
2. キー参照：抽出対象の列を抽出（コスト約50%）
3. Nested Join：①で抽出した行ごとに②の処理を実施（コスト約0%）
4. SELECT：③で絞り込んだ行・列を取得（コスト約0%）

続いて実際の実行プランを取得してみます。実際の実行プランは規定では取得されないので、SSMS上で取得を有効化しておきます。

この状態でクエリを実行すると、以下のような実行プラン情報が取得できました。

確かに、プラン自体には推定プランと同じですが、実行に関する情報（実行時間や実際の行数、など）が付加されていることが分かりますね。

今回は超基本的なクエリの例でしたが、この基本的なプランの読み方をおさえておけば、複雑なクエリのプランを読み解く際にも活かせそうです。

最適でない実行計画が作成される要因

最後に、最適でない実行計画が作成される要因の例を以下に挙げておきます。

https://docs.microsoft.com/ja-jp/learn/modules/describe-sql-server-query-plans/4-identify-problematic

• インデックス不足
• 統計情報が古い
• ハードウェアのリソース不足（Disk I/Oなど）
• 重たいクエリの実装（スカラー関数、テーブル値関数、など）
• インデックスを有効活用できない検索条件の指定方法
• クエリオプティマイザが最適でない計画を選択してしまう

勉強になりました。

おしまい

SQL Serverをもっと学ぶなら

SQL Serverの解説書少ないですよね・・・。私も何を読んで体系的に勉強しようかとても困りました。（公式ドキュメントでも良いのですが、ボリューム大きすぎて、日本語読みづらく初学者には苦痛だと思います・・・笑）

個人的には、SQL Serverの内部動作をもっと詳しく学ぶなら以下の書籍がおすすめです。