第 5 章 「データアクセスのチューニングと最適化」 ～ MCDBA をめざそう!! MCP 70-229 SQL Server 2000 開発編 ～
NEC
Eラーニング事業部
鈴木 智行
2002 年 8 月 8 日
目次
.gif){kind=icon}
1. 前回の復習問題の解答と解説
2. クエリ実行プランの分析
3. SQLプロファイラの使用
4. インデックス計画の作成と実装
5. インデックスチューニングウィザードによるインデックスの利用状況の分析
6. 復習問題
1. 前回の復習問題の解答と解説
問 1 の解答 エ
（解説）SQL Server 2000 では 1 つのテーブルに同じ種類のトリガを複数格納することができ、sp_settriggerorder ストアドプロシージャで最初と最後に起動するトリガを指定できます。したがって TrigerA を最初に、TrigerD を最後に起動することはできますが、2 番目と 3 番目に起動するトリガ（TrigerB、TrigerC） の順序を規定することはできません。したがってエが正解になります。
問 2 の解答 ウ
（解説）SQL Server 2000 では既定の分離レベルが READ COMMITTED に設定されています。READ COMMITTED では未コミットなデータの読み取り（ダーティリード）が発生する可能性がありません。必ず、コミットあるいはロールバックされた整合性のあるデータが参照されます。したがって最も適切な解はウとなります。
問 3 の解答 ア
（解説）WITH CHECK OPTION はビューの範囲外のデータを拒否できるオプションですが、集計関数やグルーピングを使用したビューに指定することはできません。したがってアが正解になります。
2. クエリ実行プランの分析
2.1 クエリプロセッサ
クエリプロセッサはクエリを処理するための重要なコンポーネントの 1 つですが、単一のコンポーネントではなく、いくつかの関連要素の集合です。これらの要素が相互に連携をしながらユーザーやアプリケーションが SQL Server に実行依頼したクエリを処理します。具体的には下記のステップが挙げられます
解析プロセス
クエリの解析と文法をチェックし、SQL ステートメントを分解して、コンパイラに対応したシーケンスツリーと呼ばれるデータ構造体を作成します。標準化プロセス
シーケンスツリーを正規化し、最適化に適した形式に変換します。最適化プロセス
複数の利用可能な実行プランから 1 つのプランを選択します。このフェーズには数多くのステップが含まれます。コンパイル
実行コードにコンパイルし、実行プランをキャッシュに格納します。
2.2 実行プラン
実行プランにはクエリを実行する手順をリストし、データアクセスの際の方法や結合、グループ処理、並び替えの方法などが定義されています。実行プランはキャッシュされるため、プランを再利用できる別のクエリがあればクエリをコンパイルすることなく、簡単にキャッシュ上のプランを利用して実行されます。
SQL Server 2000 ではこの実行プランを非常に簡単に表示できるので、クエリが内部でどのように実行されているかを分析することができます。実行プランはテキストベースのものとグラフィカル表示形式で確認可能です。詳しくはシステム構築分科会で私が記述した連載の第 2 回で説明していますので是非参照してください。
2.3 クエリオプティマイザ
クエリプロセッサの中で最も重要な働きをするコンポーネントが、クエリオプティマイザです。クエリオプティマイザは、統計情報を元にコスト（I/O と CPU リソースの消費度）を予測し、予想コストが小さくかつ最も結果を早く返す実行プランを選択します。
クエリオプティマイザと統計情報については 2.2 同様に記述がありますので是非参照してください。
3. SQLプロファイラの使用
3.1 トリガ動作の指定
SQL プロファイラを使用すると、デッドロックの数、重大なエラー、ストアド プロシージャと Transact-SQL ステートメントのトレース、ログインの利用状況など、サーバーおよびデータベースの利用状況を監視できます。SQL プロファイラのデータを SQL Server テーブルやファイルにキャプチャしておけば、後で分析することができます。また、SQL Server でキャプチャしたイベントをステップごとに再生して、何が起こったかを正確に確認することもできます。SQL プロファイラは、バッチやトランザクションの開始などのエンジン プロセス イベントを追跡します。
SQL プロファイラでトレースを開始するためには以下のように設定を行います。
テンプレートの選択（画面 3-1）
トレースを開始するためには SQL Server で何を取得するかを決定しなくてはなりません。この後に説明するイベントクラスやデータ列やフィルタで詳細を決定しますが、事前にその組み合わせ（テンプレート）があれば、素早くトレースを開始することができます。SQL プロファイラでは既定でシステム定義のテンプレートがありますが、ユーザーが作成することも可能です。
.gif)
画面 3-1 テンプレートの選択
イベントカテゴリ／イベントクラス（画面 3-2）
トレースする動作をイベントクラスとして選択します。イベントクラスは 13 のイベントカテゴリに分類されており、カテゴリ全体で選択することもできれば、各カテゴリ内の個々のイベントクラスを選択することもできます。例えば TSQL イベントカテゴリの中には SQL:BatchCompleted イベントクラスがあります（表 3-3）。上記で 「ブランク」 テンプレート以外を選択していれば、テンプレートで設定されたイベントクラスが既定で表示されます。もちろんカスタマイズも可能です。
.gif)
画面 3-2 イベントカテゴリ／イベントクラスの選択
イベントクラス |
説明 |
|---|---|
Exec Prepared SQL |
準備された 1 つ以上の SQL ステートメントが ODBC、OLEDB、または DB-Library によって実行された時点を示します |
Prepare SQL |
1 つ以上の SQL ステートメントが ODBC、OLEDB、または DB-Library が使用できるように準備された時点を示します |
SQL:BatchCompleted |
Transact-SQL バッチが終了しました |
SQL:BatchStarting |
Transact-SQL バッチが開始しました |
SQL:StmtCompleted |
Transact-SQL ステートメントが完了しました |
SQL:StmtStarting |
Transact-SQL ステートメントが開始しました |
Unprepare SQL |
準備された 1 つ以上の SQL ステートメントが ODBC、OLEDB、または DB-Library によって準備状態が解除された時点を示します |
表 3-3 イベントカテゴリとイベントクラスの例
データ列（画面3-4）
記録対象となるデータ項目を指定します。データ列はグルーピングもできるため、トレース結果をより見易くすることが可能です。ただし収集されるデータはイベントクラスによって決まります。例えば SQL:BatchCompleted イベントクラスを選択した場合は表 3-5 に記述しているデータ列が取得できます。EventClass（イベントクラス番号）と SPID （プロセス ID）は必ず取得されます。
.gif)
画面 3-4 データ列の選択
イベントクラス |
データ列 |
説明 |
|---|---|---|
SQL:BatchCompleted |
Duration |
イベントの期間 |
|
End Time |
イベントの終了時刻 |
|
Reads |
バッチにより発生したページの読み取り I/O 回数 |
|
Writes |
バッチにより発生したページの書き込み I/O 回数 |
|
CPU |
バッチの実行時に使用した CPU 時間 |
|
Text Data |
バッチのテキスト |
表 3-5 SQL:BatchCompleted イベントクラスのデータ列
- フィルタ（画面 3-6 ）トレースする条件を設定することができます。例えば画面 3-6 のように Windows のユーザーである SQLService ユーザーに関する情報をトレース対象から除外することができます。
.gif)
画面 3-6 フィルタの設定
4. インデックス計画の作成と実装
4-1 クラスタ化インデックス
クラスタ化インデックスは 1 テーブルに対し最大 1 個までしか設定できないインデックスで、その特徴はテーブルでクラスタ化キーを設定するとそのキー値の昇順にデータが並び替えられて、クラスタ化インデックスのリーフレベルが実際のデータページ（リンクリストと実際のデータによって構成）として構成されることです。したがって
クラスタ化キー値で範囲検索をする
クラスタ化キー値の順番でアクセスする
といったようなアプリケーションを使用する際は、クラスタ化インデックスを計画することを考慮にいれてください。
クラスタ化インデックスの構造については 2.2 同様に記述がありますので是非参照してください。
クラスタ化インデックスは CREATE CLUSTERED INDEX ステートメントで実装します。
（例）受注テーブルの受注番号列にクラスタ化インデックスを作成する
CREATE CLUSTERED INDEX CL_受注番号 ON dbo.受注(受注番号)
PRIMARY KEY 制約や UNIQUE 制約の指定によってクラスタ化インデックスが自動作成される場合がありますが、これらの制約で作成されたクラスタ化インデックスは DROP INDEX ステートメントで削除できません。制約を削除してクラスタ化インデックスを削除してください。
4-2 非クラスタ化インデックス
非クラスタ化インデックスはクラスタ化インデックスが存在しない場合と存在する場合で格納しているデータが異なります。クラスタ化インデックスが存在しない非クラスタ化インデックスではポインタとして行識別子（ファイル ID、ページ ID、行 ID）を格納し、直接検索対象データを探し出しています。クラスタ化インデックスが存在する非クラスタ化インデックスではポインタとして行識別子ではなくクラスタ化キーを格納し、クラスタ化インデックスを経由して検索対象データを探し出しています。したがって
選択度が高い検索条件を使用する（テーブルから比較的少数のデータを検索する）
ページ分割がデータページに対して行われる（クラスタ化インデックスが存在する場合）
といったようなアプリケーションを使用する際は、非クラスタ化インデックスを計画することを考慮にいれてください。
非クラスタ化インデックスの構造については 2.2 同様に記述がありますので是非参照してください。
クラスタ化インデックスが存在しない非クラスタ化インデックスの構造について
クラスタ化インデックスが存在する非クラスタ化インデックスの構造について
非クラスタ化インデックスは CREATE NONCLUSTERED INDEX ステートメントステートメントで実装します（NONCLUSTEREDキーワードは省略可能です）。
（例）受注テーブルの顧客コード列に非クラスタ化インデックスを作成する
CREATE NONCLUSTERED INDEX NCL_顧客コード ON dbo.受注(顧客コード)
PRIMARY KEY 制約や UNIQUE 制約の指定によって非クラスタ化インデックスが自動作成される場合がありますが、これらの制約で作成された非クラスタ化インデックスは、クラスタ化インデックス同様に、DROP INDEX ステートメントで削除できません。制約を削除して非クラスタ化インデックスを削除してください。
4-3 複合インデックス
複合インデックスは複数の列をインデックスのキー値として指定したインデックスです。1 つの複合インデックスには最大 16 の列を組み合わせることが可能です。特に複合インデックスは複数の条件を組み合わせた検索に有効に働きます。
（例）在庫テーブルの商品コードと倉庫番号列に非クラスタ化複合インデックスを作成する
CREATE NONCLUSTERED INDEX NCL_顧客コード ON dbo.受注(顧客コード)
上記の例で作成した複合インデックスは select * from 在庫 where 商品コード = N'AA000001' and 倉庫番号 = N'芝101' という複合キー全てを条件とした検索だけではなく、 select * from 在庫 where 商品コード = N'AA000003'といったような1番目の列で定義された検索にも有効に働きます。しかし select * from 在庫 where 倉庫番号 = N'芝101'といったような2番目の列で定義された検索にはあまり有効に働かない場合があります（画面4-3-1）。したがって複合インデックスを作成する際は、複合キーの順番を考慮に入れてください。
.gif)
画面 4-3-1 複合キーを使用した検索例
4.4 カバリングインデックス
リーフレベルに検索に必要なデータが全て入っている場合、データページをアクセスする必要がなくなるため、I/O の量が減少します。こういったインデックスはクエリをカバーするインデックス（カバリングインデックス）といいます。
カバリングインデックスについては2.2同様に記述がありますので是非参照してください。
4.5 インデックス付きビュー
SQL Server 2000 からはビューに対してインデックスを作成できるようになりました。通常のビューは定義情報のみで実際のデータを持ちませんが、インデックス付きのビューは実際のデータを格納し、パフォーマンス向上に寄与できます。
インデックス付きビューについては 2.2 同様に記述がありますので是非参照してください。
5. インデックスチューニングウィザードによるインデックスの利用状況の分析
インデックスチューニングウィザードは SQL プロファイラで取得したトレースファイル(ワークロード)を分析し、データベースのパフォーマンスを向上させることができるデータベースに最適のインデックス セットを推奨します。
インデックスチューニングウィザードについては 2.2 同様に記述がありますので是非参照してください。
6. 復習問題
解答、解説は次回に記述します、復習として考えてみてください。
問 1 統計情報について正しい記述を選択してください。
ア．グルーピングによって集計された値の情報
イ．Master データベースに格納されている SQL Server 全体に関するシステム情報
ウ．列値のサンプリングデータから生成された分布ステップ情報
エ．プロファイラによって収集されたクエリの詳細情報
問 2 SQLServer のパフォーマンスを監視していたら CPU の使用率が 100 % になってしまってレスポンスが悪化しているようだ。ボトルネックとなっているクエリを調べたい。どうすればよいか。もっとも適切な記述を選択してください。
ア．クエリアナライザの「推定実行プランの表示」を使用して最もコストが高いクエリを洗い出す
イ．SQL プロファイラで CPU データ列をグルーピングし、最も CPU 時間が多いクエリを洗い出す
ウ．システムモニタで Processor オブジェクトの %Processor Time を使用し、インスタンスで最も CPU 時間が多いクエリを洗い出す
エ．タスクマネージャのプロセスタブでCPU時間を多く使用しているクエリを洗い出す
問 3 下記の表に対して顧客コード列のインデックスを削除する SQL 文を発行しました。このSQL文について最も適切な記述を選択肢から選んでください。
表名：顧客
顧客コード ※ |
顧客名 |
所在地 |
電話番号 |
|---|
※顧客コードはPRIMARY KEY制約（デフォルト指定）によって主キーとなっています（インデックス名:PK_顧客コード）
（SQL文）
DROP INDEX PK_顧客コード
ア．PRIMARY KEY制約ではデフォルトでクラスタ化インデックスが作成されるため、DROP CLUSTERED INDEXステートメントで削除しなくてはいけない。
イ． 表には最低限1個のインデックスが必要なため、この状況では削除できない。
ウ．制約で自動的に作成されたインデックスはDROP INDEXステートメントで削除できない。
エ．特に問題なく正常終了する。
.jpg)
鈴木 智行 : NEC Eラーニング事業部に所属。 入社以来、インストラクタとして教育業務に従事。汎用機、UNIX を経て、1994 年より マイクロソフト認定トレーナー (MCT) として、管理者向け教育を担当。SQL Server は 4.21a から携わっており、現在は主に SQL Server 2000 に関わるデータベース教育を中心に担当。Windows 2000 および SQL Server 2000 での MCSA, MCSE, MCDBA を取得しており、情報処理技術者試験のテクニカルエンジニア （データベース） も取得済。最近は MCA の 3 科目 （データベース、OS／ネットワーク、アプリケーション構築） 全てに合格し、C# を勉強中。