開発者のための SQL Server パフォーマンス チューニング/最適化講座

第 2 部 ‐ クエリ オプティマイザ

DAT 411
Presented at Tech-Ed 97

Adam Shapiro
Program Manager
Microsoft Corporation

目次

概要
目的
クエリ オプティマイザの概要
フェーズ 1: クエリの分析
フェーズ 2: インデックスの選択
フェーズ 3: 結合の選択
最適なプランの選択
クエリ処理フェーズのまとめ
SHOWPLAN の出力
STATISTICS IO の出力
オプティマイザの選択内容の分析
オプティマイザの無効化

概要

目的

• クエリの処理ステップを理解する。

• 検索引数と非検索引数の違いを理解する。

• クエリ オプティマイザがインデックス統計情報をどのように使用してクエリ プランを生成するかを理解する。

• クエリ オプティマイザがクエリのコストをどのように計算するかを理解する。

• 結合がどのように処理されるかを理解する。

クエリ オプティマイザの概要

クエリ オプティマイザは、各 SQL ステートメントを評価して、最適な実行プランを決定します。

パフォーマンスの高いクエリと有用なインデックスを作成するには、クエリ オプティマイザのしくみを理解することが重要です。

クエリオプティマイザの目的

最も効率的なプランの決定
クエリ オプティマイザは、データの取得と SELECT、INSERT、UPDATE、DELETE などのデータ操作クエリの実行順序に関する最適なプランを決定します。クエリ オプティマイザが生成するクエリ プランには、クエリの実行に必要なステップの順序が示されます。クエリ オプティマイザで最適化できるのは、行の検索、結合、および並べ替えの処理手順だけです。

コストベースの最適化
SQL Server では、コストベースの最適化を行います。コストベースのオプティマイザは、生成された各クエリ プランを評価し、そのプランのコストを見積もります。コストは、返される行の数と各リレーショナル操作に必要な物理ディスク I/O の量に基づいて決定されます。

コストベースのオプティマイザは、テーブル サイズ、テーブル構造、利用可能なインデックスなど、データおよび記憶の基本的な構造に関する情報に基づいて動作し、インデックスごとに維持されている統計情報に基づいて各リレーショナル操作の選択度を見積もります。

コストベースのオプティマイザは、結果セットの生成に必要なリレーショナル操作をさまざまな順序で組み合わせて評価し、システムのオーバーヘッドの点で最もコストが低くなる実行プランを決定します。

コストの見積もりの正確さは、各リレーショナル操作の選択度の見積もりに使用できる統計データの正確さに制限されます。

クエリのパフォーマンスは、個々のクエリ要素の速度と選択した結合順序の効率性に依存します。クエリ オプティマイザは、実行時間が妥当な範囲に収まるように選択肢を絞り込んで最適化を進めます。

論理ページ アクセスと物理ページ アクセスの量もパフォーマンスの尺度となります。

クエリ オプティマイザは、論理アクセスと物理アクセスの両方に基づいて、クエリ プランのコストを評価します。さらに、固定割合のページがキャッシュ内に維持されていることも考慮されます。

さらに、クエリ オプティマイザでは、クエリ プランを評価して、作業量 (CPU 時間と I/O 時間の累積) が最も小さいものを求めます。物理 I/O がこの評価の尺度として使用されます。この評価では、物理 I/O の量を低減することが目標となります。

パフォーマンスの高いクエリを作成し、有用なインデックスを選定するとともにパフォーマンス上の問題を検出するには、クエリ オプティマイザのしくみを理解することが重要です。

クエリの最適化が行われるタイミング

クエリを実行すると、そのクエリをオプティマイザで最適化できる形式に変換するための処理が行われます。ここでは、クエリを処理して結果セットを返す前に必要となる処理の概要を示します。

解析処理
解析処理では、クライアントから発行されたクエリの構文をチェックし、構文をリレーショナル データベース管理システムで認識される構成要素に分解します。この処理の結果、解析済みクエリ ツリーが出力されます。

標準化処理
標準化処理では、ネットワークを最適化可能な形式に変換します。構文上、冗長な句は自動的に削除されます。サブクエリは、可能な限りフラット化されます。この処理の結果、標準化済みクエリ ツリーが出力されます。

クエリの最適化
クエリ オプティマイザでは、クエリを効率的に処理するクエリ プランを生成します。このフェーズでは、クエリの分析、インデックスの選択、および結合の選択の 3 段階の処理を行います。実行プランの選択肢の数は、自動的に絞り込まれます。この処理の結果、実行プランまたはクエリ プランが出力されます。

コンパイル
この処理では、コードを実行可能コードにコンパイルします。

データベースアクセスルーチン
オプティマイザが決定した最適なデータ アクセス方法 (テーブル スキャンとインデックスのどちらを使用するか) は、ここで適用されます。

クエリオプティマイザ情報

クエリ オプティマイザは、収集した情報を分析して、最適なクエリ プランを決定します。

sysindexes テーブル
オプティマイザでは、sysindexes テーブル内の情報のうち、次の表に示す情報を使用できます。

列名	説明
indid	インデックスの ID。有効な値は、次のとおりです。 0: テーブル (非クラスタ化テーブル) 1: クラスタ化インデックス >1: 非クラスタ化 255: テキスト データまたはイメージ データを格納するテーブルのエントリ
dpages	indid = 0 または indid = 1 の場合は、使用されているデータのページ数を示します。indid = 255 の場合は、rows が 0 に設定されています。それ以外の場合は、リーフ レベル インデックス ページの数を示します。
rows	indid = 0 および indid = 1 に基づくデータレベルでの行数。indid > 1 の場合は、この値が反復されます。indid = 255 の場合、rows は 0 に設定されます。
distribution	ディストリビューション ページへのポインタ (エントリがインデックスの場合)。
rowpage	1 ページあたりの最大行数。
minlen	行の最大サイズ。
maxlen	行の最小サイズ。
maxirow	非リーフ インデックス行の最大サイズ。
keys1	キー列の記述 (エントリがインデックスの場合)。
keys2	キー列の記述 (エントリがインデックスの場合)。
soid	インデックス作成時の並べ替え順 ID。キーが文字データを含まない場合は 0 になります。
csid	インデックス作成時の文字セット ID。キーが文字データを含まない場合は 0 になります。

キー値の統計分布
この情報は、ディストリビューション ページに含まれています。

実行対象のクエリ
利用可能なインデックスがある場合、オプティマイザは、クエリに含まれている選択条件に基づいて、どのインデックスが最も有用となるかを決定します。オプティマイザの決定は、クエリ内で行の一致条件がどのように指定されているかに依存します。

SHOWPLAN
オプティマイザの最終的な決定内容を調べるには、SET ステートメントの SHOWPLAN オプションを使います。各テーブルに対して選択されたインデックス、テーブルの結合順序、および選択された更新モードを確認できます。SHOWPLAN の出力には、作業テーブルなどの情報も含まれます。

フェーズ 1: クエリの分析

クエリ オプティマイザの第 1 フェーズは、クエリの分析と呼ばれます。このフェーズでは、オプティマイザが解析済みの各句を評価し、最適化が可能かどうかを判断します。最適化可能と判断されるのは、検索引数や結合句などによってスキャンを制限している句です。最適化可能な句については、適切なインデックスが存在するかどうかもチェックされます。

検索引数

要求する情報を検索引数で指定している場合は、検索対象が制限されます。検索引数には、完全一致を指定するもの、値の範囲を指定するもの、および AND 演算子で複数の項目を組み合わせたものがあります。検索引数には、演算子を使用して列を評価する定数式が含まれます。

• 検索引数の形式は、次のとおりです。

  <column> <inclusive operator> <constant> [AND...]

  OR
  <constant> <inclusive operator> <column> [AND...]

• >、<、=、BETWEEN、LIKE の各演算子がよく使用されます。

• すべての列は、同じテーブルに所属している必要があります。

• 検索引数には、複数の条件を AND で結合して含めることができます。

例

name = 'jones' 
salary > 40000 
60000 < salary 
department = 'sales' 
name = 'jones' AND salary > 100000 

非検索引数

非検索引数とは、検索対象を制限しない式を意味します。特定の行を対象に含めるのではなく、対象から除外する式は、非検索引数となります。

たとえば、等しくないことを評価する式 (!=) では、まず最初にすべてのデータを評価しなければ、検索条件に一致しない式を決定できません。

また、次の例のように列を比較する式も、非検索引数となります。

salary = commission

両方の列がテーブル自体に格納されているので、インデックスがあまり役立ちません。

さらに、データ アクセスの前に計算を行う式も、非検索引数となります。例を次に示します。

salary * 12 > 36000

この場合、まず最初に salary 列にアクセスして計算を実行しなければ、行が条件を満たすかどうかを判断できません。

例題
次のクエリでは、どれが検索引数になるでしょうか。

SELECT COUNT(*) 
FROM dept, empl, job 
WHERE empl.age > 30 
AND (dept.floor = 2 OR dept.floor = 3) 
AND job.rate > $20.00 
AND empl.jobno = job.jobno 

非検索引数の変換

多くの場合、非検索引数は検索引数に書き換えることができます。クエリに検索引数を使用すれば、オプティマイザによってインデックスが選択される可能性が高くなります。

列に対する計算を含む式は、列を分離すると検索引数に変換できます。

・	非検索引数	WHERE price * 12 = 100
・	検索引数	WHERE price = 100/12

ヒント
クエリを作成するときは、列情報を演算子の一方の側に置き、検索条件を反対側に置きます。

BETWEEN や LIKE を含む式のように、クエリ オプティマイザによって内部的に検索引数に修正される式もあります。

• BETWEEN 句は、> = と < = を使用して上限と下限を定義する範囲式と等価です。

• name LIKE 'jo%' のように、最初の文字が一定の LIKE 式は、インデックスで処理できます。これは、name >= 'jo' AND name < 'jp' と等価です。name LIKE '%jo' のように先頭の文字が定まっていない LIKE 式は、検索対象を制限しないため、検索引数として扱われません。

インデックスの使用を避けたい場合の非検索引数の使用
特定のインデックスをクエリ オプティマイザに選択させたくない場合は、次のように列に 0 を加算するなどの方法で、検索句に非検索引数を使用することができます。

 salary + 0 > 30000

このステートメントの場合、オプティマイザは salary のインデックスを評価しません。

OR 句

クエリの分析中には、OR 句も検出されます。OR 句については、この講座で後ほど詳しく学びます。

結合句

複数のテーブルからデータを取得するには、結合句が必要です。結合句では、同じデータベース内の複数のテーブルのデータをリンクできるほか、異なるデータベース間でデータをリンクすることもできます。

自己結合も、結合句の一例です。

例

SELECT e1.manager_name, e2.name 
FROM empl e1, empl e2 
WHERE e1.emplno = e2.manager_no 

フェーズ 2: インデックスの選択

クエリ最適化の第 2 フェーズは、インデックスの選択です。クエリ オプティマイザは、このフェーズでは、句に対応するインデックスの有無をチェックし、句の選択度 (返される行の数) に基づいてインデックスの有用性を評価した上で、条件を満たす行を検索するために必要な論理ページ アクセスおよび物理ページ アクセスの数を見積もります。

有用なインデックスが存在するかどうかのチェック

クエリ オプティマイザは、有用なインデックスが存在するかどうかを判断するために、まず句に対応するインデックスの有無をチェックします。

インデックスが有用となるのは、次の場合です。

• インデックスの最初の列が検索引数に使用されている場合

• 上限と下限の一方または両方が検索引数に指定されており、検索対象が制限されている場合

考慮事項
WHERE 句に上位の列が指定されていれば、クエリ オプティマイザは非クラスタ化インデックスの使用を評価できます。

カバリング インデックスについては、インデックス付けした列が WHERE 句に指定されているかどうかに関係なく、常にクエリ オプティマイザによる評価が可能です。

句の選択度の判定

統計情報が利用可能な場合
句に対応する有用なインデックスが見つかると、そのインデックスの有用性が句の選択度に基づいて評価されます。有用なインデックスが存在する場合でも、クエリ オプティマイザがインデックスによるアクセスを最善な方法と判断しなければ、インデックスは使用されません。選択度は、句を満足する行の数に基づいて判定されます。統計情報が利用可能であれば、サーバーはディストリビューション ステップを使用してインデックスを評価します。

統計情報が利用できない場合
統計情報が利用できなければ、サーバーは演算子ごとに固定のパーセンテージを使用します。

利用可能な統計情報がない場合にオプティマイザが使用する既定値は、次のとおりです。

演算子	行の見積もりパーセンテージ
=	10%
>	33%
<	33%
BETWEEN	25%

例外として、WHERE 句が等価で、インデックスが一意な場合には、完全に一致する行が 1 つだけ返されるので、オプティマイザが統計情報を使用する必要はありません。

テーブル内にデータが存在しないときに作成されたインデックスや、切り捨てられたテーブルの場合には、利用可能な統計情報は存在しません。

インデックスの統計情報

オプティマイザでは、インデックスが検索範囲の制限にどれほど有用かを評価するときや、複数テーブルのクエリに対して結合順序を決定するときに統計情報を使用します。各インデックスには、そのインデックス内の値の分布に関する統計情報が維持されます。

SQL Server では、インデックス統計情報に対して、値の均等な分布が維持されます。キー値の範囲が変更されても、ステップあたりの行数は常に一定に保たれます。範囲あたりの数が変更されてもキー値の範囲が一定に保たれる標準的な分布との違いに注意してください。均等な分布が維持されているので、クエリ オプティマイザでは、テーブル内の行の総数に対するパーセンテージとして条件を満たす行の数を見積もることにより、クエリの選択度を簡単に判定できます。

ディストリビューションページ

ディストリビューション ページは、インデックス内に含まれている値をサンプリングしたものです。

インデックスに対してディストリビューション ページが作成されているかどうかは、sysindexes テーブルの distribution 列の値で判断できます。distribution 列の値が 0 であれば、そのインデックスに対応する利用可能な統計情報は存在しません。0 以外の場合は、その数の示す位置にディストリビューション ページが存在します。

UPDATE STATISTICS を実行すると、テーブル上の各インデックスに対応するディストリビューション ページが作成されます。

密度とは、重複の平均数を意味します。複合インデックスでは、左ベースの列のサブセットのそれぞれについて、個別の値が維持されます。

ディストリビューションステップ

インデックス キーのサイズによって、各インデックス キーのディストリビューション ステップの総数が決まります。ディストリビューション ページに格納できる値の数によって制限されます。インデックス内の最初のキーと最後のキーの値は、常に含まれます。

• データは、同じ行数からなる複数のステップに均等に分割されます。

• ステップ数は、インデックス キーのサイズによって決まります。

• 1 ステップあたりのインデックス キー数は 1 つです。

注
ディストリビューション ページには最初のインデックス キーが常に含まれているので、ディストリビューション ステップの総数は、1 ページあたりのインデックス キー数から 1 を引いたものに等しくなります。

ディストリビューション ステップの総数でインデックス キーの総数を割った数が、各ステップに含まれるキーの数になります。ディストリビューション ページには、ステップごとにインデックス キーが 1 つずつ記録されます。

SQL Server では、ステップのサイズを次のように計算します。

1 ページあたりのインデックス キーの数から 1 を引くと、ディストリビューション ステップの数になります。ステップ数が多いほど、情報が正確になります。また、インデックスのキーが小さいほど、統計情報は正確になります。ステップ数が行の総数と等しければ、すべての情報がディストリビューション ページに含まれていることになります。

複合インデックスの場合は、最初の列のキーだけを使用してディストリビューション ステップが決定されます。

ディストリビューションステップ : 例

ディストリビューションステップ数の計算

インデックス キーのサイズ = 250 バイト

8 (1 ページあたりのインデックス キー数) - 1 = 7 (ディストリビューション ステップ数)

インデックス キーの総数 = 22

合計 7 つのステップがあり、1 ステップあたりのキー数は 3 です。

各ステップは、テーブルの 7 分の 1 に対応します。

ディストリビューション ページには、ステップごとに (3 行ごとに) インデックス キーが 1 つずつ記録されます。

例題
インデックス キーのサイズ = 18 バイト

1 ページあたりのインデックス キーの数は__________________________________

ディストリビューション ステップの数は_____________________________________

インデックス キーの総数 = 94.795

1 ステップあたりのキーの数は__________________________________

各ステップにはテーブルの何分の 1 が含まれていますか______________________________

ディストリビューションステップの参照

構文

DBCC SHOW_STATISTICS (table_name, index_name)

指定されたテーブル (table_name) 上のインデックス (index_name) に対応するディストリビューション ページの統計情報をすべて表示します。このコマンドが返す密度から、インデックスの選択度を求めることができます。密度が低いほど、選択度が高くなります。これに基づいて、インデックスがオプティマイザに有用かどうかを判断できます。

Enterprise Manager
SQL Enterprise Manager でデータベース内のインデックスを調べるには、[管理] メニューの [インデックス] をクリックするか、またはテーブルの名前を展開して右クリックし、[インデックス] をクリックします。どちらの操作を行った場合も、ダイアログ ボックスが表示され、データベース内の任意のインデックスを調べることができます。

[分布] をクリックすると、DBCC SHOW_STATISTICS の出力と同じ情報を詳細に表示できます。

UPDATE STATISTICS

UPDATE STATISTICS [[database.]owner.]table_name [index_name]

table_name パラメータには、インデックスが関連付けられているテーブルを指定します。SQL Server では、インデックス名をデータベース全体で一意にする必要がないため、このパラメータでテーブルを指定する必要があります。

index_name パラメータには、更新対象のインデックスを指定します。インデックス名を省略すると、指定したテーブルのすべてのインデックスに関する分布統計情報が更新されます。インデックスの名前と説明のリストを表示するには、テーブル名を指定して sp_helpindex システム ストアド プロシージャを実行します。

構文
STATS_DATE (table_id, index_id)

この関数は、指定されたインデックス (index_id) の統計情報が最後に更新された日付を返します。

例
テーブル上のすべてのインデックスのステートメントが最後に更新された日付を確認するには、次のステートメントを使用します。

SELECT 'Index Name' = i.name, 'Statistics Updated' = 
stats_date(i.id, i.indid) 
FROM sysobjects o, sysindexes i 
WHERE o.name = 'charge' AND o.id = i.id 

統計情報が使用されないケース

統計情報が利用できない場合や、WHERE 句に未知の値がある場合は、統計情報が使用されません。統計情報を利用できなくなるのは、テーブル内にデータが存在しないときに作成されたインデックスに対して UPDATE STATISTICS がまだ実行されていない場合か、またはテーブルが切り捨てられた場合です。

未知の値

例

DECLARE @var int 
SELECT @var = 15 
SELECT X FROM Y WHERE col = @var 

この例では、WHERE 句に未知の値が含まれているため、インデックス統計情報のキー値を使用できません。ただし、演算子が = であれば、密度情報を使用して条件を満たす行の数を見積もることができます。

統計情報が存在しない場合、固定のパーセンテージは、既定の値と若干異なります。

演算子	行の見積もりパーセンテージ
=	密度による見積もり
<, >, BETWEEN	33%

注
利用可能な統計情報が存在しても、それが最新の情報であるとは限りません。

ページアクセスの量の見積もり

クエリ オプティマイザでは、句の選択度判定の第 2 段階として、行数の見積もりに基いて論理ページ数を見積もります。これにより、特定の句に対して最適なインデックスを決定します。ページ数の見積もりは、クラスタ化インデックスの場合と非クラスタ化インデックスの場合とで大幅に異なることがあります。非クラスタ化インデックスに対しては、それぞれの行が異なるページに見つかるという最悪のケースのシナリオが適用されます。クエリ オプティマイザは、このシナリオをコストベースの最適化計算に加味します。

インデックスがない場合
論理ページ アクセス = テーブル内のデータ ページの総数

クラスタ化インデックスの場合
論理ページ アクセス = インデックス内のレベル数 + スキャン対象のデータ ページ数 (データ ページ数 = 条件を満たす行/データ ページあたりの行数)

非クラスタ化インデックスの場合
論理ページ アクセス = インデックス内のレベル数 + リーフ ページ数 (条件を満たす行/リーフ ページあたりの行数) + 条件を満たす行の数 (各行が別々のページにある場合を想定)

カバリングインデックスの場合
論理ページ アクセス = インデックス内のレベル数 + リーフ ページ数 (条件を満たす行/リーフ ページあたりの行数)

一意なインデックスの場合
一意なインデックスのキー全体が完全に一致する行を検索するクエリの場合は、インデックス レベル数に 1 を加算したものが論理ページ アクセス数となります。

フェーズ 3: 結合の選択

クエリ最適化の第 3 フェーズは、結合の選択です。オプティマイザが結合の選択を評価するのは、複数テーブルのクエリや自己結合が存在する場合です。オプティマイザは、何通りかの句の順序を比較し、論理ページ I/O の点で見積もりコストが最も低くなる結合プランを選択します。

結合句の選択度の判定

テーブル A からテーブル B の単一の行に結合される行の数は、結合の選択度によって決まります。検索引数に一致する行の数を決定する句の選択度との違いに注意してください。結合の選択度は、結合の処理順序を決定するときに有用な要素です。

統計情報が利用可能であれば、インデックスの密度に基づいて結合の選択度が判定されます。統計情報が利用できない場合は、小さい方のテーブルに含まれる行の数で 1 を割った数が使用されます。

結合の選択度は、結合句から予想される行の数を示します。この選択度は、計算で求めるか、または密度 (重複行の平均パーセンテージ) に基づいて判定することができます。

結合句の例

WHERE dept.deptno = empl.deptno

仮定:

従業員数 = 1,000

部署数 = 100

直観的に判断するなら、1 部署あたりの従業員数は 10 人と見積もることができるかもしれませんが、クエリ オプティマイザでは論理的な計算を通じて選択度を見積もる必要があります。

上記の句の選択度は、1/100 (0.01) となります。

employee テーブル内の行のうち、department テーブル内の 1 つの行に接続される行の数は、次のように求められます。

1,000 * .01 = 10

department テーブル内の行のうち、employee テーブル内の 1 つの行に接続される行の数は、次のように求められます。

100 * .01 = 1

結合のネスト化反復処理

クエリに結合句が含まれている場合、オプティマイザは表、インデックス、および結合の数を評価して、ネスト化反復処理の最適な順序を決定します。

方針

• テーブルを 4 つずつ評価して、最適な結合順序を決定します。

• 外部テーブル内の条件を満たす次の行の値を内部クエリ内に見積もります。

• 外部テーブルには検索範囲を制限する検索引数があり、この引数にインデックスを使用できます。

• 前述の方法で単一テーブルのクエリを処理します。

ガイドライン

• オプティマイザがクエリ プランの決定時に評価する選択肢の数を増やすには、結合句を追加します。

• 冗長な句を追加します。

結合のネスト化反復処理 : 例

オプティマイザは、結合のネスト化反復処理を実行するプランを選択することがあります。この場合、SQL Server は、最初のテーブルの行を検索してネストしたループのセットを構築し、その行を使用して次のテーブルをスキャンします。一致する結果を使用して最後のテーブルをスキャンするまで、これを繰り返します。反復を繰り返してテーブルからテーブルへスキャンするにつれて、結果セットが絞り込まれて行きます。

クエリ プランには、使用するネスト テーブルの順序付きセットが指定されます。プランの選択肢の数は、テーブル、インデックス、および結合の数に関連します。

titles テーブルから titleauthor テーブルへの結合 : 例

例

SELECT title 
FROM titles, titleauthor 
WHERE titles.title_id = titleauthor.title_id 
AND titleauthor.royaltyper > 50 

処理ステップ

1. titles テーブルの次の行を取得します。

2. title_id の値を取得します。

   title_id のインデックスを使用して、titleauthor 内の行のうち、一致する各行を検索します。

   royaltyper の値を比較し、値が 50 より大きい行を返します。

3. 外部テーブル内の条件を満たす行すべてにアクセスするまでステップ 1 とステップ 2 を繰り返します。

titleauthor テーブルから titles テーブルへの結合 : 例

Example

SELECT title 
FROM titles, titleauthor 
WHERE titles.title_id = titleauthor.title_id 
AND titleauthor.royaltyper > 50 

処理ステップ

1. titles テーブルから、royaltyper > 50 を満たす次の行を取得します。

2. title_idのインデックスを使用して、titles を検索し、行を返します。

3. 外部テーブル内の条件を満たす行すべてにアクセスするまでステップ 1 とステップ 2 を繰り返します。

3 つのテーブルの結合 : 例

例

SELECT t.title, a.au_lname 
FROM titles t, authors a, titleauthor ta 
WHERE t.title_id = ta.title_id 
AND a.au_id = ta.au_id 
AND a.au_lname = 'Green' 

例 1

titles 、 ta (titleauthor) 、 authors ( 上記 ) の順の結合

3 つのタイトルがあるので、titleauthor を 3 回検索し、authors を 6 回検索します。

例 2

authors 、 ta (titleauthor) 、 titles ( 上記 ) の順の結合

author = Green を満たす行は 1 つだけなので、titleauthor を 1 回検索し、titles を 2 回検索します。

このように、最初の例では authors が 6 回検索されるのに対し、2 番めの例では 1 回だけ検索されます。

重要なポイント

• テーブル数が増えると、内部テーブルの反復回数が増えます。

• テーブル検索のコストは、有効なインデックスの有無に依存します。

• 検索のコストは、テーブルのサイズだけでなく、各レベルから返される行数にも依存します。

最適なプランの選択

総コストの計算

• クエリ オプティマイザは、それぞれの順列について、どのインデックスと結合方法が最適かを評価します。

  クエリ オプティマイザは、各結合順序のコストを計算します。

• クエリ オプティマイザは、テーブルごとに、論理ページ アクセスの数を計算します。

  インデックスが一意であり WHERE 句が指定値に一致する場合には、クエリ オプティマイザは、常にインデックスのレベル数に 1 ページ アクセスを加算したものとしてコストを算出します。

• クエリ オプティマイザは、キャッシュに対する各テーブルの相対的なサイズを考慮します。

クエリ処理フェーズのまとめ

SHOWPLAN の出力

SET SHOWPLAN ステートメントの出力には、クエリ オプティマイザがクエリの処理用に選択する、最終的なアクセス方法の詳細が示されます。出力メッセージの例を次に示します。

STEP n
SHOWPLAN の出力には、この情報がクエリごとに示されます。SQL Server が結果を 1 ステップで取得できない場合は、クエリ プランが複数のステップに分割されます。

The type of query is <query type>
各ステップで使用するクエリの種類 (SELECT、INSERT、UPDATE、または DELETE) を示します。ほかのコマンドの発行中に SHOWPLAN をオンにすると、発行したコマンドが <query type> に反映されます。

The update mode is deferred
選択した更新モードが遅延されていることを示します。

The update mode is direct
選択した更新モードが直接適用されていることを示します。

GROUP BY
クエリに GROUP BY 句が含まれていることを示します。GROUP BY 句では、常に、条件を満たす行を作業テーブル内に選択してグループ化するステップと、結果を返すステップの 2 つが必要です。

Scalar Aggregate
SELECT ステートメントで集計関数が使用されたことを示します。行の数に関係なく、単一の値が返されるので、最初のステップで集計値を計算し、2 番めのステップで最終的な値を返します。

Vector Aggregate
GROUP BY 句が集計関数とともに使用されている場合、クエリ オプティマイザはベクタ集計値を使用します。グループごとに単一の値が返されます。

FROM TABLE
クエリがアクセスするテーブルの名前を示します。FROM TABLE の後ろには、クエリを処理するためにテーブルが結合された順にテーブルのリストが示されます。

TO TABLE
修正対象のターゲット テーブルを示します。データベース内の物理テーブルを使用せずに、作業テーブルを使用することもあります。

Worktable
クエリの中間結果を保持するための一時テーブル (作業テーブル) が作成されたことを示します。作業テーブルは、行の保存が必要な場合に作成されます。作業テーブルは常に tempdb データベース内に作成され、結果を返した後で自動的に削除されます。

Worktable created for <query type>
クエリを処理するための作業テーブルが作成されたことを示します。query type には、SELECT_INTO、DISTINCT、または ORDER BY が示されます。また、再フォーマットの目的でテーブルが作成された場合は REFORMATTING が示されます。

This step involves sorting
クエリの中間結果を並べ替えてからユーザーに返す必要があることを示します。中間結果の並べ替えが必要になるのは、DISTINCT を指定しているクエリか、ORDER BY を含むクエリに対して有用なインデックスが存在しない場合です。

Using GETSORTED
結果セットの行を並べ替えるための一時作業テーブルが作成されたことを示します。並べ替え順で行を返すクエリでも、このステップが使用されないことがあります。

Nested iteration
オプティマイザの既定テクニックであるネスト化反復処理が使用されている場合は、このフレーズが SHOWPLAN の出力に表示されます。

EXISTS TABLE: nested iteration
存在検査中に使用されるテーブル上のネスト化反復処理を示します。Transact-SQL では、存在検査を EXISTS、IN、または =ANY として表現できます。

Table Scan
クエリ オプティマイザが、結果の取得方法としてテーブル スキャンを選択したことを示します。

Using Clustered Index
クエリ オプティマイザが、結果の取得方法としてクラスタ化インデックスを使用していることを示します。

Index: <index name>
クエリ オプティマイザが、結果の取得方法として使用している非クラスタ化インデックスの名前を示します。

Using Dynamic Index
オプティマイザが、OR の処理に独自のインデックスを使用していることを示します。

STATISTICS IO の出力

STATISTICS IO の出力には、次の値が含まれます。

• Logical Reads
  このクエリを処理するためにアクセスしたページの総数を示します。すべてのページ アクセスは、データ キャッシュを通じて行われるので、キャッシュにまだ含まれていないページは、ディスクから読み取る必要があります。

• Physical Reads
  このクエリを処理するためにディスクから読み取ったページの総数を示します。常に Logical Reads 以下の値になります。

  Cache Hit Ratio の値は、上の 2 つの値から次のように計算されます。

  Cache Hit Ratio = (Logical Reads - Physical Reads) / Logical Reads

• Read Ahead Reads
  先読みマネージャがキャッシュに読み込んだページの総数を示します。この値が大きいほど、物理読み取り数 (Physical Reads) が小さくなります。キャッシュ ヒット率 (Cache Hit Ratio) は、先読みを有効にした方が高くなります。

• Scan Count
  対応するテーブルにアクセスした回数を示します。外部テーブルの場合、Scan Count は常に 1 になります。内部テーブルの場合は、Scan Count にスキャンごとのアクセス ページ数を掛けたものが論理読み取り数 (Logical Reads) になります。

目的

• オプティマイザが最適なプランを選択したかどうかを確認する。

• 利用可能な統計情報を確認する。

• トレース フラグを使用して、オプティマイザがプランをどのように決定したかを確認する。

• オプティマイザ ヒントをどのように使用できるか、また、どのような場合にオプティマイザ ヒントが必要になるかを理解する。

• FORCEPLAN を使用すると、オプティマイザが決定する結合順序とは異なる順序を強制的に適用できます。

オプティマイザの選択内容の分析

ほとんどの場合、オプティマイザは処理対象のクエリに最適なインデックスと結合順序を選択します。

オプティマイザの選択が最適ではない可能性があると思われる場合は、いくつかのツールを使用して、オプティマイザがインデックスや結合順序を選択した理由を分析できます。理由を知るだけで、オプティマイザの選択が最適であると十分納得できる場合もあります。

理由を見ても納得できないのであれば、いくつかのツールを使用して、オプティマイザの選択とは異なる設定を適用することもできます。これらのツールでは、指定した設定がオプティマイザの選択よりも実際に優れているかどうかを確認できます。

統計管理ツール

DBCC UPDATEUSAGE
このコマンドは、sp_spaceused システム ストアド プロシージャによる領域使用状況レポートが不正な値を返すような sysindexes テーブル内の情報の不一致を報告し、これを訂正します。

DBCC UPDATEUSAGE は、種類 U (ユーザー定義テーブル) および種類 S (システム テーブル) のオブジェクト上のクラスタ化インデックスに対応する sysindexes テーブルの used 列、reserved 列、および dpages 列を訂正します。非クラスタ化インデックスでは、サイズ情報は管理されていません。このステートメントを使用すると、正確な使用状況情報が返されるように sysindexes 内の領域使用状況カウンタを同期させることができます。database_name の代わりに 0 を指定すると、現在のデータベースに対して更新が実行されます。

構文

DBCC UPDATEUSAGE ({0 | database_name} [, table_name [, index_id]])

[WITH COUNT_ROWS]

WITH COUNT_ROWS オプションを指定すると、sysindexes の rows 列が更新され、テーブル内の行数の現在のカウント値が反映されます。ただし、これは、index_id が 0 または 1 の sysindexes 行に対してのみ適用されます。サイズの大きいテーブルに対してこのオプションを使用すると、パフォーマンスが影響を受けることがあります。

注
sp_spaceused ストアド プロシージャを @updateusage 修飾子とともに使用すると、DBCC UPDATEUSAGE を使用した場合と同じ結果が得られます。ただし、sp_spaceused ストアド プロシージャの方が実行に時間がかかります。このオプションでは、テーブル内の各行をカウントするため、テーブルのサイズが大きい場合は完了するまでに時間がかかることがあります。

DBCC SHOW_STATISTICS
このコマンドは、指定されたテーブル上のインデックスに対応するディストリビューション ページの統計情報をすべて表示します。このコマンドが返す密度から、インデックスの選択度を求めることができます。密度が低いほど、選択度が高くなります。これに基づいて、インデックスがオプティマイザに有用かどうかを判断できます。インデックスのディストリビューション ステップ数に基づく結果が返されます。

構文
DBCC SHOW_STATISTICS (table_name, index_name)

STATS_DATE 関数
この関数は、指定されたインデックスの統計情報が最後に更新された日付を返します。

構文
STATS_DATE (table_id, index_id)

上記の情報は、いずれも Enterprise Manager の [インデックスの管理] ダイアログ ボックスを使用して表示できます。[管理] メニューの [インデックス] をクリックしてください。

トレースフラグ

トレースフラグ
トレース フラグを使うと、SQL Server の動作に関する詳細な情報を取得したり、主に下位互換性確保の目的で特定の動作を変更したりすることができます。トレース フラグは、問題発生時に使用できますが、問題を恒久的に解決できるまでの間の一時的な回避方法としてのみ使用してください。トレース フラグの情報は問題の診断に役立ちますが、トレース フラグはサポートされている機能のセットに含まれていないことに注意してください。つまり、今後の互換性や、今後も引き続き使用できるかどうかは保証されません。さらに、Microsoft を含め、1 次サポート プロバイダでは、トレース フラグに関する詳細情報を用意していないため、トレース フラグやその出力に関する質問に回答できません。このため、ここで示す情報は、ユーザー自身の責任で使用してください。

オプティマイザのトレースフラグ

トレース フラグ	情報
302	統計情報ページの使用の有無、実際の選択度 (選択度を取得できる場合)、インデックスに対して見積もられる物理 I/O と論理 I/O に関する情報を示します。実際の結合順序を確認するには、トレース フラグ302 をトレース フラグ 310 とともに使用します。
310	結合順序に関する情報を示します。SET SHOWPLAN ON ステートメントを使用すると、もっと読みやすい形式でインデックスの選択情報を取得できます。
325	非クラスタ化インデックスまたは並べ替えを使用して ORDER BY 句を処理する場合のコストに関する情報を出力します。
326	並べ替えの見積もりコストおよび実際のコストに関する情報を出力します。
330	SET SHOWPLAN オプションを指定した場合の完全な出力を有効にします。このオプションは結合に関する詳細な情報を提供します。
3604	トレース出力をクライアントに送信します。DBCC TRACEON および DBCC TRACEOFF でトレース フラグを設定する場合にだけ使用します。
3605	トレース出力をエラー ログに送信します。SQL Server をコマンド プロンプトから起動した場合は、出力は画面にも表示されます。

既定とは異なる起動オプション
SQL Server のインストール時には、セットアップ プログラムが既定の起動オプションを Windows NT レジストリに書き込みます。書き込み先は、次のレジストリ キーです。

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer

SQL Server をシングル ユーザー モードで起動したり、特定のセットのトレース フラグを有効にして起動するなど、既定とは異なる起動オプションをレジストリに登録するには、MSSQLServer キー (MSSQLServer の下層) を新しいキーにコピーし、新しいキーのオプションをニーズに合わせて編集します。MSSQLServer キーの Parameters エントリには、トレース フラグを含む個々の起動オプションがそれぞれ SQLArg0 や SQLArg1 などで始まる名前の個別のパラメータとして格納されます。パラメータの順序は、特に意味を持ちません。

ただし、レジストリの編集は一般にはお勧めできません。変更に不備や誤りがあると、システムの環境設定に関して深刻な問題が発生することがあります。

SingleUser という名前の新しいキーを作成したとします。この場合、

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\SingleUser\Parameters

というエントリを編集して、-m 起動オプションを含めることができます。SingleUser キーの Parameters エントリ全体は、次のようになります。

HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\SingleUser\Parameters 
SQLArg0 : REG_SZ : -dC:\SQL\DAT\MASTER.DAT 
SQLArg1 : REG_SZ : -eC:\SQL\LOG\ERRORLOG 
SQLArg2 : REG_SZ : -m 

この代替キーを使って SQL Server を起動するには、次の例に示すように、コマンド プロンプトから -m 起動オプション付きで SQL Server を起動します。

sqlservr -c -sSingleUser

オプティマイザのトレースフラグの使用

オプティマイザのトレース フラグにはさまざまな情報が示されますが、そのほとんどは Microsoft 社内のエンジニア用の情報です。主に、次のような点をチェックしたい場合にのみ使用できます。

• すべての検索引数 (SARG) に正しい演算子が指定されてるかどうか。

• すべてのインデックスが選択肢として考慮されているかどうか。

• 統計情報ページが利用可能かどうか。

• 見積もり行数が実際の行数に近いかどうか。

• 見積もりページ数が妥当かどうか。

• すべての結合順序が考慮されているかどうか。

• 結合順序ごとに適切なインデックスがテストされているかどうか。

例

ここでは、Transact-SQL コードとその出力の例を示します。上記のチェック項目に対応する部分を太字で示します。

DBCC TRACEON(3604, 302) 
SET SHOWPLAN ON 
SET NOEXEC ON 
GO  
SELECT * FROM charge 
WHERE charge_no > 99950 

DBCC execution completed. 
If DBCC printed error messages, see your System Administrator. 

_*****************************_
Leaving q_init_sclause() for table 'charge' (varno 0). 
The table has 100000 rows and 3408 pages. 
Cheapest index is index 0, costing 3408 pages per scan. 

_*****************************_
Entering q_score_index() for table 'charge' (varno 0). 
The table has 100000 rows and 3408 pages. 
Scoring the search clause: 
AND (!:0xb8e492) (andstat:0xa) 
 GT (L:0xb8e47e) (rsltype:0x38 rsllen:4 rslprec:10 rslscale:0  
 opstat:0x0) 
VAR (L:0xb8e4d0)  (varname:charge_no varno:0 colid:1 
coltype(0x38):INT4 colen:4 coloff:2 colprec:10 colscale:0 
vartypeid:101 varusecnt:2 varstat:0x4001 varlevel:0 varsubq:0) 
INT4 (R:0xb8e464)  (left:0xb8e46c len:4 maxlen:4 prec:5 scale:0 
value:99950) 

Scoring clause for index 6 
Relop bits are: 0x4000,0x80,0x10,0x1 
Qualifying stat page; pgno: 10616 steps: 332 
Search value: INT4 value:99950 
No steps for search value--qualpage for LT search value finds 
value between steps 330 and 331--use betweenSC 
Estimate: indid 6, selectivity 4.513098e-003, rows 451 pages 457 
Cheapest index is index 6, costing 457 pages and generating 451 rows 
per scan. 
Search argument selectivity is 0.004513. 
_*****************************_
STEP 1 
The type of query is SELECT 
FROM TABLE  
charge  
Nested iteration  
Index : charge_charge_amt 

オプティマイザの無効化

FORCEPLAN

FORCEPLAN は SET ステートメントのオプションです。ON または OFF に設定できます。FORCEPLAN は、いったん ON に設定すると、現在のセッションが終了するか、または明示的に OFF に設定されるまでの間、ON のままになります。

FORCEPLAN を ON に設定すると、オプティマイザが結合順序を選択しなくなり、テーブルが実際に結合される順序は、FROM 句にテーブルを指定した順序によって決まります。

FORCEPLAN の使用

例
この例では、クエリの処理時に、まず corporation テーブルにアクセスし、その後で member テーブルにアクセスします。この順序は、オプティマイザによる選択内容に関係なく適用されます。

SET FORCEPLAN ON 
GO 
SELECT * 
FROM corporation, member 
WHERE member.corp_no = corporation.corp_no 
AND member_no < 100 
GO 

インデックスの強制適用

オプティマイザのインデックス付けヒント
SQL Server には、SELECT ステートメント内でオプティマイザに渡すことのできるヒントがいくつか用意されています。ほとんどのヒントは、ロック動作に関するものです。ロック関係のヒントについては、後のモジュールで学びます。インデックス付けに関するヒントとして、INDEX ヒントがあります。SELECT ステートメント内でテーブル名の後ろにインデックス ID またはインデックス名を指定する必要があります。

部分的構文

SELECT select_list 
FROM table_name [(INDEX = {index_name | index_id})] 

このヒントでは、テーブルに使用するインデックスの名前または ID を指定します。index_id として 0 を指定すると、テーブル スキャンが実行されます。1 を指定すると、クラスタ化インデックスが使用されます (存在する場合)。

例
この例では、オプティマイザが最適として選択したインデックスに関係なく、corp_no 上のインデックスを使用してクエリを処理します。

SELECT * 
FROM member (INDEX = member_corporation_link) 
WHERE member_no < 100 
AND corp_no BETWEEN 200 AND 300 

FASTFIRSTROW
このオプションを指定すると、WHERE 句がない場合に、ORDER BY 句に一致する非クラスタ化インデックスをオプティマイザが使用するようになります。最初の行を返すまでの時間が短くなり、並べ替え用の作業テーブルが tempdb 内に作成されなくなります。ただし、先読みが行われなくなるので、クエリの処理を完了するために必要な I/O および時間が全体的に増えることがあります。クエリに WHERE 句と ORDER BY 句がともに含まれている場合は、ORDER BY 句を解決するインデックスではなく、WHERE 句を解決するインデックスが使用される可能性があります。この決定は、WHERE 句の選択度に基づいて行われますが、FASTFIRSTROW を指定した場合は、FASTFIRSTROW の影響を受けます。

その他の考慮事項

オプティマイザの無効化でパフォーマンスを向上できる場合
オプティマイザを無効にすることで、パフォーマンスを向上できているかどうかを必ず確認してください。STATISTICS IO と STATISTICS TIME をオンにすると、好影響が出ているかどうかをチェックできます。通常は、オプティマイザが最適な選択を行うので、オプティマイザを無効にしてもパフォーマンスは向上しません。

最後の手段
ほかの方法で、オプティマイザの動作を調整してみてください。統計情報が最新のものになっているかどうか、最新のストアド プロシージャを再コンパイルしたかどうか、クエリや検索引数に改善の余地がないかどうか、インデックスを修正することで効果が得られないかどうかを確認します。

ヒントを使用する理由の記録
オプティマイザの無効化が必要になった理由を書き留めておいてください。理由を記録しておかないと、最初にコードを作成してから長時間経った後でこれらの理由に変更が生じたときに、ヒントが不要かどうかを判断できなくなることがあります。

更新ごとのリセット
SQL Server オプティマイザは、継続的に改良されています。新しいバージョンのインストール後は、オプティマイザを無効化する必要がなくなる可能性もあります。ヒントとして指定した内容が、オプティマイザの選択内容に劣ることもあります。

SQL Server オプティマイザは、動的な最適化メカニズムであり、データの変更に応じて最適なプランを選択できるようになっています。オプティマイザに対して特定のプランを強制的に適用すると、動的な決定が行われなくなるため、データの変更がプランに反映されなくなります。このため、オプティマイザを無効化して処理するクエリについては、SQL Server のバージョンがアップグレードされていなくても、プランの有効性を定期的にテストすることを検討してください。

©1997 Microsoft Corporation. All rights reserved.

本書に記載されている情報は、発行時点で議論されている問題点に関する Microsoft Corporation の最新の見解を示しています。Microsoft は変化する市場状況に対処しなければならないため、本書の内容を Microsoft の確約事項として解釈してはならず、Microsoft は発行日以降に提示された情報の精度についてはいかなるものであれ保証致しません。

本書は、情報の通知のみを目的としており、Microsoft は本書に記載されている情報について明示的にも暗黙的にも一切の保証を致しません。

Microsoft、Windows NT は米国 Microsoft Corporation の米国およびその他の国における登録商標または商標です。

その他、記載されている会社名および製品名は、各社の商標および登録商標です。