最適なデータアクセスを行うための仕組み

SQL ServerでTransact-SQL文を実行すると、解析→標準化→クエリの最適化→コンパイル→実行のようなプロセスをたどって結果が返されます。このプロセス中の「クエリの最適化」において、「クエリオプティマイザ」という機能が最適な実行プランの決定を行います。Oracle ServerもSQL Serverも少々の言葉の違いこそあれ、同じように最適なデータアクセスを行おうとする仕組みがあるのです。

Oracle Serverのオプティマイザは「コストベース」または「ルールベース」で最適な実行計画を選択します。ルールベースは「where句の書き方次第でインデックス使用の有無が決まる」など、あらかじめ定義されたルールに従って実行計画を選択するものです。このルールはOracle V6の頃に作られたものですが、今も使われています。コストベースは実際のデータ量やデータ分布を基にした統計情報に従ってコストを算出し、もっともコストの低い、つまり効率の良いアクセス方法を決定するものです。

ルールベースの使用には制限事項も多く、また現在のデータ状態を反映するものではないため、Oracle社はコストベースの使用を推奨しています。SQL Serverでは、実行プランの選択に「コストベース」のみを使用します。

Oracle Serverで実行計画を確認

それでは実際に実行計画（実行プラン）を確認する方法を見てみましょう。実行計画を確認するために、Oracle Serverでは「EXPLAIN PLAN」命令を使用します。EXPLAIN PLAN はPLAN_TABLE表に内容の出力をおこなうため、事前にPLAN_TABLE表の作成などの準備が必要です。

EXPLAIN PLANの使用例は以下のとおりです。

●EXPLAIN PLAN命令の実行 → PLAN_TABLE表への出力

explain plan for
select * from employees
where employee_id=100;

●PLAN_TABLE表の内容確認

@?\rdbms\admin\utlxpls.sql

●PLAN_TABLE表の内容確認結果

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
| Id | Operation                   | Name          | Rows | Bytes | Cost |
-------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |               | 1    | 68    | 2    |
|  1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     | 1    | 68    | 2    |
|* 2 |   INDEX UNIQUE SCAN         | EMP_EMP_ID_PK | 107  |       | 1    |
---------------------------------------------------------------------------

結果は表示階層の深い方から読みます。上の例では、Id列の2番目の結果は索引からROWIDの取得をしており、Id列の1番目の結果はROWIDをもとに表からデータを読んでいます。

もしテーブルをフルスキャンしていれば、結果には「TABLE ACCESS FULL テーブル名」のような出力があるはずです。このようにして、データアクセスのためにどのような処理ステップが踏まれるのかを確認できます。

SQL Serverで実行プランを表示

SQL Serverでは、クエリアナライザを使用して、容易に、視覚的にわかりやすく実行プランの表示をすることができます。SQL クエリクエリアナライザにて以下のいずれかを行うことで、実行プランの確認ができます。

	・	クエリを入力後に[クエリ]-[実行プランの表示]メニューをクリックしてクエリを実行 　　→実行結果および、[実行プラン]タブに実行プランが表示される。
	・	クエリを入力後に[クエリ]-[推定実行プランの表示]メニューをクリック（クエリの実行はせずに、実行プランのみ表示） 　　→[推定実行プラン]タブに実行プランが表示される。

■ 画面1　クエリアナライザで推定実行プランを表示した状態

グラフィカルな実行プランの出力は右から左、そして上から下に向かって読みます。各アイコンはクエリを処理するために使用される作業単位(ステップ)を表しています。アイコンを結ぶ矢印が処理の順番になります。さらに「Ordersテーブルの全件検索処理に全体の27%のコストを費やしている」など、各ステップのコストが合計コストに対する比率として表示されます。これにより、どのステップが負荷の高い処理なのかも把握できます。

次の表に、クエリ オプティマイザが使用する処理のアイコンの主なものを示します。

アイコン 演算子の説明 テーブルスキャン処理。テーブルからすべての行を取得します。WHERE句がある場合、適合する行だけが返されます。 インデックススキャン処理。インデックスからすべての行を取得します。WHERE句がある場合、適合する行だけが返されます。 インデックスシーク処理。インデックスのシーク機能を使用して行を取得します。 フィルタ処理。条件を満たす行だけを返します。 ソート処理。受け取ったすべての行を並べ替えます。 ネスト化ループ処理。通常はインデックスを使用して、内部テーブルを検索し、外部テーブルの各行を探す結合処理。 ブックマーク (RID またはクラスタ化キー) を使用して、テーブルまたはクラスタ化インデックス内の対応する行を参照します。

※すべてのアイコンの一覧と詳細については、SQL Server Books Online で、「SQL クエリ アナライザを使用した実行プランのグラフィカル表示」を検索してください。

実行プランの出力内で、アイコンの上にマウスポインタを置くと、ポイントした操作に関する追加情報が次の例のように表示されます。情報中の「コスト（そのステップのコスト）」、「サブツリーコスト（そのステップ以前のコスト）」は特別な計算値ですが、ここでいう「コスト」とは、処理にかかった「おおよその秒」とみなしてよいものです。

■ 画面2　実行プランの出力・追加情報の表示

以上のように実行プランを表示することで、作成したインデックスがきちんと使用されているか、どのような結合方法を使っているか、どの処理に負荷がかかっているか、などを把握することができます。

SQL Serverで実行プランをテキスト出力

グラフィカルな表示は大変わかりやすいものですが、内容をドキュメントとして保存するような場合には不向きです。内容の保存やワークロードの分析のためには、Oracle ServerのExplain Planのようにテキストベースで出力できたほうがよいでしょう。SQL Serverで実行プランをテキスト出力するためには以下のステートメントを使用します。

・SET SHOWPLAN_TEXT ON ・SET SHOWPLAN_ALL ON

これらのステートメントは、統計情報を持つsysindexesというシステムテーブルを参照することにより、見積もり値に基づく実行プランを表示するよう、セッション状態を変更します。これらの設定がされていると、クエリを実行しても結果は返らず、代わりにステートメントの実行方法と実行に必要なリソースの見積もりに関する詳細情報が返ります。

「SHOWPLAN_TEXT」と「SHOWPLAN_ALL」の出力の違いは、SHOWPLAN_ALL の出力が、SHOWPLAN_TEXTより多くの情報（クエリの行数、I/Oコスト、CPUコストなどの見積もり）を返すことです。

●SHOWPLAN_ALL実行例

まず、セッション状態を変更します。

set showplan_all on
go

続いて同じセッションで、以下のSQLを実行します。これは【画面1】のグラフィカルな実行プランの図と同じSQL文です。

select * from dbo.Orders O inner join dbo.[Order Details] D
on O.OrderID = D.OrderID
inner join Products P
on P.ProductID = D.ProductID

以下のように結果が出力されます（ステップの演算説明のみ掲載します。I/Oコストなどの結果は割愛します）。

select * from dbo.Orders O inner join dbo.[Order Details] D on O.OrderID = D.OrderID inner join Products P on P.ProductID = D.ProductID

|--Hash Match(Inner Join, HASH:([P].[ProductID])=([D].[ProductID]))

|--Clustered Index Scan(OBJECT:([Northwind].[dbo].[Products].[PK_Products] AS [P])) |--Nested Loops(Inner Join, OUTER REFERENCES:([O].[OrderID]))

|--Clustered Index Scan(OBJECT:([Northwind].[dbo].[Orders].[PK_Orders] AS [O])) |--Clustered Index Seek(OBJECT:([Northwind].[dbo].[Order Details].[PK_Order_Details] AS [D]), SEEK:([D].[OrderID]=[O].[OrderID]) ORDERED FORWARD)

結果は、Oracle ServerのExplain Planと同様に表示階層の深い方から読みます。上記の例を理解するにはインデックスの知識も必要ですのでここでは詳しく説明しませんが、実行プランの詳細はマイクロソフト認定トレーニングコース「#2073 Microsoft SQL Server 2000 データベースプログラミング」で扱っています。このコースはインデックスアーキテクチャやストアドプロシージャの利用方法なども修得できますので、クエリパフォーマンスの調整をされる方には最適なコースです。