第 3 回 「クエリのパフォーマンスチューニング方法を理解する」～ システム構築 ～
NEC
Eラーニング事業部
鈴木 智行
2001年12月14日
目次
.gif){kind=icon}
3-1. 統計情報の更新
3-2. 実行プランのキャッシュ
3-3. コスト
3-4. コスト制限
3-5. 検索引数
3-6. クエリをカバーするインデックス
3-7. インデックス付きビュー
3-8. インデックスチューニングウィザード
3-1. 統計情報の更新
クエリオプティマイザが最適な判断をするためには、正確な統計情報をクエリオプティマイザに提供することが必要です。統計情報を作成することはもちろん大事ですが、更新系操作によってデータが多数の行にわたって更新された場合には、統計情報が実際のデータを反映していない危険性が考えられます。
通常 SQL Server のデータベースは auto update statistics（統計の自動更新）データベースオプションが ON であり、統計情報が古くなった場合にクエリ最適化の段階で統計情報を自動更新します。
ただし、この場合には SQL Server が自動的に統計情報を保守する必要があるため、多少のオーバーヘッドが発生します。これがクエリのパフォーマンスに影響を与える可能性は少なからずあるでしょう。
そういった場合は統計情報の手動更新を選択できます。インデックスの統計情報を自動更新したくない場合には Create Index ステートメントの STATISTICS_NORECOMPUTE オプションを指定してください。またインデックスを持たない列の統計情報に関しては、CREATE STATISTICS ステートメントの NORECOMPUTE オプションを指定すれば、統計の自動更新は行いません。
図 3-1-1 のように autostats 列が OFF の統計を更新するためには UPDATE STATISTICS ステートメントを発行します。データベース内のすべてのユーザー定義のテーブルに対してまとめて更新したい場合には sp_updatestats システムストアドプロシージャを使用してください。
.gif)
図 3-1-1 手動更新が必要な統計は autostats 列が OFF である
統計情報は上記の方法で更新のタイミングを手動で制御することができます。しかし、更新作業を怠った場合にはクエリオプティマイザに適切な情報を与えることができません。
その場合クエリオプティマイザは誤った判断を行い、最適な実行プランを用いてクエリ実行できない可能性があります。オプティマイザヒントを使用せずに SQL Server に統計情報の保守を任せる場合には十分に注意してください。
3-2. 実行プランのキャッシュ
クエリ最適化フェーズで最適な実行プランを決定すると、実行コードにコンパイルされてメモリ上のプロシージャキャッシュに格納されます。実行プランは複数の接続が同じプランを同時に実行できるように、プランの一部（クエリプランと呼ぶ）が再入可能な読みとり専用のデータ構造体になっています。SQL Server は新しいクエリが発行されると、最初にプロシージャキャッシュ内を調べ、同じクエリに関する既存の実行プランがないかどうかを探します。したがって最適な実行プランがプロシージャキャッシュ上に既にある場合には、それを再利用することで再コンパイルするオーバーヘッドが低減し、パフォーマンスが向上します。
しかし新しいクエリに対する既存の実行プランと照合するアルゴリズムでは、すべてのオブジェクト参照が完全に修飾されている必要があります。例えば次の 2 つのステートメントは同じ実行プランを実行しません。
select * from Orders
select * from Northwind.dbo.Orders
したがって実行プランの再利用によってパフォーマンスを向上したい場合には、SQL ステートメントを記述する際に、オブジェクト参照に完全修飾名を使用するようにしなければいけません。
次に、実行プランは保存期間がありコンパイルにかかるコスト係数とオブジェクト参照回数の積で決定される時効フィールドを持っています。レイジーライタープロセスが定期的に時効フィールドを減少して 0 になるとオブジェクトの割り当てを解除します（これをエージング方式といいます）。割り当てが解除されるとプロシージャキャッシュから解放される可能性が高くなり、解放された場合は次回のクエリ実行時に新しい実行プランがコンパイルされます。同様に統計情報が新しくなったときなどにも既存の実行プランには無効のマークがつき、コンパイルされます。この動作は SQL Server が自動で行いますが、ストアドプロシージャでクエリを実現している場合は、手動で実行プランを再コンパイルすることができます。
Create Procedure ステートメントに With Recompile オプションを指定する
Execute ステートメントに With Recompile オプションを指定する
sp_recompile システムストアドプロシージャを実行する
再コンパイルを少なくすることがパフォーマンスの向上につながります。必要ない場合には再コンパイルを明示的におこなうストアドプロシージャは検討し、再コンパイルを最低限に抑えるようにしてください。
3-3. コスト
第 2 回で紹介したとおり、クエリオプティマイザは与えられたクエリでの最適な実行プランを生成し、パフォーマンスの向上に寄与します。しかし同じ結果を得ようと思った場合には、Transact-SQL ステートメントの記述方法は 1 つではなくいろいろな方法があるはずです。いつも同じような方法で記述するのではなく他の方法と比較して、より高いパフォーマンスを提供するクエリを記述できるように検討する必要があります。
比較材料として I/O コスト、CPU コストを利用することができます。テキストベースでは set showplan_text ではなく、set showplan_all ステートメントを使用してください。その結果出力の中の EstimateIO 列、EstimateCPU 列および TotalSubtreeCost 列を比較検討してください。
今回はグラフィカルな実行プランを用いて比較をしてみます。
CopyNorthwind データベース（インデックスが設定されていない）に対し、同じ結果を返す以下の 2 つのクエリをクエリアナライザを使って実行します。
クエリ 1（サブクエリを使用）（図 3-3-1）
select LastName,EmployeeID from Employees as em
where exists (select * from orders as ord where em.EmployeeID = ord.EmployeeID and OrderDate = '97/9/5')
.gif)
**図** **3-3-1** **サブクエリを用いて実行した結果**
クエリ 2（結合を使用）（図 3-3-2）
select Distinct LastName,em.EmployeeID from Employees as em
Inner Join Orders as ord on em.EmployeeID = ord.EmployeeIDsu where OrderDate = '97/9/5'
.gif)
**図** **3-3-2** **結合を用いて実行した結果**
コストはバッチ毎に計算されます。その総コストを 100% として各ステップは相対 % として表示されます。コストの単位は公表されていません。各アイコンにマウスを位置付けると、詳細なコスト情報（I/O コスト、CPU コスト、サブツリーコストなど）が表示されます。総コストを低減するには、特に高コストなステップに対し検討を加えてください。クエリ 1 とクエリ 2 の orders テーブルのテーブルスキャンは同じコスト値でした。したがって構造の変更が許されるのであれば、インデックスの使用を検討してさらにインデックスを設定した場合と比較する必要があります。
今回はクエリ 1 とクエリ 2 の総コストを見てみましょう。一番左にある select 実行プランアイコンにマウスを位置づけます。図 3-3-1 がクエリ 1 で図 3-3-2 がクエリ 2 のものです。
.gif)
図 3-3-3 サブクエリを用いたクエリの総コスト
.gif)
図 3-3-4 結合を用いたクエリの総コスト
クエリ 1 のサブツリーコスト（これが総コストにあたる）は 0.103、クエリ 2 では 0.114 であることがわかります。したがって構造の変更が許されない場合にはクエリ 1 のように記述をした方がよいでしょう。
しかし、いつもサブクエリ（クエリ 1）の方が、結合（クエリ 2）よりも低コストの結果を返すとは限りません。置かれている環境では結果が異なってくる場合があります。
いつも同じ方法で記述するのではなく、他に記述方法はないか？そしてそれはパフォーマンスの向上につながるのか？これをコストベースで比較検討することが重要です。
3-4. コスト制限
SQL Server 上では複数のクエリが同時実行されます。もしその中に CPUやI/O などを浪費するようなクエリがあったとしたら、他のクエリに十分なシステムリソースが与えられず、パフォーマンスが上がらないケースが考えられます。そういったクエリは 3-3 で紹介したとおり、コスト比較を行ってより低コストで実現するようにクエリを再作成したり、スケールアップによって十分なリソースを与えるなどの対処を行うべきです。
その他に SQL Server ではコスト制限を設定して時間のかかるクエリを実行せず、システムリソースの消費を抑えることも可能です。特に緊急度が高くないクエリの場合に他のクエリへの影響を最小限に抑えることができます。
コスト制限の指定はクエリを実行するために必要な最大推定経過時間（秒）を指定します。例えば set query_governor_cost_limit 1 と実行すると、クエリオプティマイザが現在のセッションの中でクエリが 1 秒かかると判断した場合には、そのクエリは実行されません。コスト制限は実際の経過時間でなく、見積コストを基にしているので実行時のオーバーヘッドがかからないという利点があります。
またコスト制限はサーバー単位でも指定できます。
sp_configure システムストアドプロシージャの query governor cost limit オプションで上記と同様に指定します。ただしこのオプションは Show Advanced Options オプションを 1 に設定しないと、表示／変更しません。
また、コスト制限の指定は適切な権限（例えばsysadmin固定サーバーロールのメンバーであるなど）を持っていないと上記のステートメントは実行できないので注意してください。
3-5. 検索引数
クエリオプティマイザはクエリの分析フェーズでクエリの検索の基準を識別します。このとき条件句が対象データを制限しない場合は基準として利用されません。
しかし対象データを制限するかしないかは条件句の記述方法によって大きく変わります。このとき対象データを制限する（すなわち役に立つ）条件句のことを検索引数（SARG:Search Argumennt）と呼び、クエリを記述する際には SARG となれるように条件句を記述することでパフォーマンスを向上することが可能です。
以下のケースなどは検索引数として使用できません。
salary ! > 50000（否定演算子が含まれている場合）
substring(CompanyName,1,1) = N'A'（関数が含まれている場合）（図 3-5-1）
price * 1.03 > 41200（計算式が含まれている場合）
name LIKE ‘%Taro’（ワイルドカードが先頭にある場合）
.gif)
図 3-5-1 SARG として使用できない場合の実行結果。 CompanyName 列に非クラスタ化インデックスがあるのだが使用していない （コストは 0.0392 ）
しかし上記の 1～3 は以下のように記述し直すことによって、検索引数として使用できます。
salary <= 50000
CompanyName Like N'A%'（図 3-5-2）
price > 40000
.gif)
SQL Server 7.0 からは上記の中で計算式を含む 3 の場合に限り、インデックスの有用性を測定するためにクエリオプティマイザが単純化された形式を内部利用するようになりました。だからといって計算式を含んで良いというわけではありませんが、インデックスを使用することでパフォーマンス向上が見込める場合は、検索引数として利用できるように記述してクエリオプティマイザの選択肢を増やすようにしてください。
3-6. クエリをカバーするインデックス
非クラスタ化インデックスのデータアクセス方法は第 1 回でご紹介したとおりであり、最終段階ではリーフレベルからポインタを使ってデータページへアクセスします（この部分はグラフィカルな実行プランで Bookmark Lookup アイコンとして表示されます）。
しかし、もしリーフレベルに検索に必要なデータが全て入っている場合はどうでしょうか？インデックスに必要なデータページ数は増加しますが、データページをアクセスする必要がなくなるため、I/O の量が減少します。こういったインデックスはクエリをカバーするインデックス（カバリングインデックス）といいます。
例えば以下の場合を考えてみましょう。
今回は CopyNorthwind データベース（インデックスのないデータベース）の Ordersテーブルに対し OrderID 列の非クラスタ化インデックスを作成します。このテーブルに対し、以下のクエリを実行します。
クエリ
select OrderID, CustomerID from Orders
where OrderID = 10450
.gif)
図 3-6-1 非クラスタ化インデックスがクエリをカバーしていない場合の実行プラン
図 3-6-1 の実行プランでは Bookmark Lookup アイコンによるインデックスのリーフレベルからデータページへジャンプしている様子がわかります。
では今度は先ほどの OrderID 列の非クラスタ化インデックスを削除し、OrderID 列と CustomerID 列の複合インデックスを作成します。このテーブルに対し同じクエリを実行します。
.gif)
図 3-6-2 非クラスタ化インデックスがクエリをカバーしている場合の実行プラン
図 3-6-2 の実行プランをみると Bookmark Lookup アイコンがなくなっていることがわかります。
必要なデータがインデックス内に含まれているため、クエリオプティマイザはデータページにアクセスしません。すなわち I/O の量が減少するためパフォーマンス向上に寄与することができます。
カバリングインデックスはクエリ内で参照される全ての列を複合インデックスとして含める必要があるので、列のデータ型やデータ量、クエリで必要な列数によっては、インデックスページが非常に多くなり、逆に I/O の量が増加する可能性もあります。コストや I/O の量などを比較して使用の検討をしてください
3-7. インデックス付きビュー
SQL Server 2000 からはビューに対してインデックスを作成できるようになりました。
通常のビューは定義情報のみで実際のデータを持ちませんが、インデックス付きのビューは実際のデータを格納します。したがって通常のビューよりもインデックス付きビューを使用するとパフォーマンス向上に寄与できます。しかし、ベーステーブル（ビューの基になるテーブル）が変更されるとインデックス付きビューのデータもインデックスも変更されるため、インデックス付きビューはあまり OLTP 環境では有効に働きません。どちらかというと OLAP 環境で有効活用できます。
ただし、全てのビューにインデックスが作成できるわけではなく、インデックス付きビューを作成する場合は以下の制限があります（代表的なものを列挙します）。
ビューは、SCHEMABINDING オプションを使用して作成する必要があります
ビューが参照できるのはベース テーブルのみで、ほかのビューは参照できません
テーブルとユーザー定義関数は、2 部構成の名前で参照される必要があります
初めに作成するインデックスは一意のクラスタ化インデックスでなければいけません
この他にも細かい制限はありますが、簡単にインデックス付きビューとして作成できるかできないかを OBJECTPROPERTY 関数の IsIndexable プロパティで確認可能です（図 3-7-1）。
.gif)
図 3-7-1 OBJECTPROPERTY 関数の IsIndexable プロパティが 1 を返せば、インデックス付きビューとして作成できます
インデックス付きビューを利用する利点はもう 1 つあります。FROM 句でビューを直接指定していないクエリでも、クエリオプティマイザがインデックス付きビューを有用だと判断した場合にはインデックス付きビューを使って効率よくデータを取得します。
通常よりもパフォーマンスの向上が期待できます。
インデックス付きビューはデータを効率良く取得できますが、その分保守コストが増加します。検索のパフォーマンス向上だけに重点をおくのであれば構いませんが、業務によってはオーバーヘッドを増やしてしまう可能性もあるのでご注意ください。
3-8. インデックスチューニングウィザード
適切なインデックスを適切な列に作成することは、非常に重要ですが非常に難しい作業です。一朝一夕でうまくいくようなものではなく、SQL Server のアーキテクチャやデータベースの設計および業務分析などの幅広く詳細な知識がないと、結局はパフォーマンスに大きな影響（悪い意味での）を与える結果になりかねません。Try and Error のように泥臭く、とにかくテストを繰り返して比較しコストを低減するアプローチもあるでしょう。ただしそれには長い時間とたくさんの労力を必要とします。
このような問題を解決するために、SQL Server にはバージョン 7.0 から簡単に最適なインデックスを提案して作成／変更する機能があります。その設定に用いるのがインデックスチューニングウィザードです。
インデックスチューニングウィザードを使用するためには、ワークロードと呼ばれる分析データが必要です。ワークロードは SQLプロファイラトレースで構成することができます。トレースによって通常のデータベース利用状況の代表的なサンプルを取得すると、インデックスチューニングウィザードがそのワークロードを分析し、データベースのパフォーマンスを向上させることができるデータベースに最適のインデックス セットを推奨します。
.gif)
図 3-8-1 インデックスチューニングウィザードの推奨結果。
推奨されたインデックスを実装すれば 56% のパフォーマンス向上が見込まれる
図 3-8-1 のように推奨されたインデックスのセットはすぐに実装することが可能です。
初級ユーザーおよび上級ユーザーに対しても、インデックスチューニングウィザードは使用でき、パフォーマンスに大きな影響を与えるインデックスを適切に設定できます。初期作成の時点でも、インデックス保守する時点でもいろいろなタイミングで利用できます。
インデックスチューニングウィザードは開発／管理工数削減に有効なツールです、簡単に扱えるのでぜひ利用し、パフォーマンス向上に役立ててください。
.gif)
鈴木 智行 : NEC Eラーニング事業部に所属。入社以来、インストラクタとして教育業務に従事。汎用機、UNIX を経て、1994 年より マイクロソフト認定トレーナー (MCT) として、管理者向け教育を担当。SQL Server は 4.21a から携わっており、現在は主に SQL Server 2000 に関わるデータベース教育を中心に担当。 Windows 2000 および SQL Server 2000 での MCSE,MCDBA を取得しており、情報処理技術者試験のテクニカルエンジニア（データベース）も取得済。 SQL Server の優位性をアピールできるように Oracle Gold も取得した。今後 Oracle Plutinum を取得予定し、日々データベースを極めることに努力している。