Index Advisorを用いたパフォーマンス チューニング方法

  • Article

Microsoft SQL Server/Microsoft Azure SQL Database サポートチーム
サポート エンジニア 清水 磨

[概要]
Index Advisor はSQL Databaseに新しく追加された機能の１つで、SQL データベースの使用パターンを分析し、現在のクエリパフォーマンスを向上できる推奨インデックスを提供します。これにより、ユーザーはデーターベースに関する深い知識がなくとも、インデックスを用いたチューニングが可能になります。また、推奨インデックスを自動的に実装することも設定可能であり、V12サーバーをご利用のお客様は、Azureポータル上からインデックスの追加を数クリックで実現可能になりました。
今回はこの Index Advisor の使用方法についてご案内します。

 

[前提状況]
・V11サーバー、V12サーバーそれぞれで使用可能です。ただし、Azure ポータルサイトからのインデックス操作はV12でのみ、サポートされています。
・推奨インデックスの自動作成、削除を有効化するためには、クエリストアを有効化しておく必要があります。

・推奨インデックスを表示するには、リーダー権限と SQL DB の共同作成者権限が必要です。
・インデックスの作成またはドロップおよびインデックス作成のキャンセルなどのアクションを実行するには、所有者権限と SQL DB の共同作成者権限が必要です。
※権限の確認、設定は、Azure のロールベースのアクセス制御 の 「Azure ポータルを使用したアクセス権の管理」の手順で行います。

 

[設定方法]
1. Azure ポータルにサインインします。
2. [参照] - [SQL データベース] の順にクリックし、ご使用のデータベースを選択します。
3. 画面中段にある[インデックス アドバイザー]のアイコンを選択する、もしくは[すべての設定] - [Index Advisor] の順にクリックし、[インデックス アドバイザー]の項目を選択します。
clip_image002clip_image004

なお、データベース作成直後などは推奨されるインデックスは表示されません。これは、[概要]に記載した通り、該当データベースにおけるクエリパターンがある程度、蓄積される必要があるためです。
目安として約1週間程度、またこの期間内に一貫性のある処理を実行することで推奨インデックスが表示されやすくなります。

// インデックスが表示されない場合
clip_image006

// インデックスが表示された場合
パフォーマンスに与える影響ごとに4つのカテゴリに分かれて表示されます。
image

4. 推奨されたインデックスを選択し、[適用]を選択することで適用することができます。[インデックス操作] からはインデックスの作成状況も確認できます。
また、推奨インデックスを無視することや、スクリプト化することが可能です。
image

 

5. Index Advisor を使用してインデックスを作成した場合、パフォーマンスへの悪影響が見つかるとインデックスは自動的に元に戻されます。
[インデックス操作] から該当のインデックスを選択し、手動で行うことも可能です。
image

 

6. またIndex Advisor が自動的にインデックス作成、削除を行うように設定することも可能です。（クエリストアの有効化が必要）
clip_image014

[注意事項]
以下の場合には推奨インデックスが表示されません。
・十分な利用履歴がない場合。例えば、データベースが最近作成されたり、しばらくデータベースが使われていない場合です。
・該当データベースにて、スキーマや実行されるクエリ内容に大幅な変更があった場合。
・該当データベースにて必要なインデックスがすでに作成済みの場合。

 

[補足情報]
- アーキテクチャについて
Index Advisor は該当データベースの使用パターンを学習し、機械学習のモデルによって、推奨されるインデックスを提案します。Index Advisorは数日前の使用パターンを基に推奨インデックスを提案するため、データベースの作成後、ある程度、処理を実行し、使用パターンを蓄積させる必要があります。
また、Index Advisor は世界中のデータベースにおける使用パターンを基に、様々なパターンをAzure Machine Learningにより機械学習させ、日々、そのモデルの精度を向上させています。ユーザーによって向上されるサービスであるため、SQL Databaseのユーザーには無償でIndex Advisorが提供されています。なお、Index Advisorのための計算処理は、SQL Database外にて行われています。
上記のため、処理内容やスキーマが変更された場合でも、Index Advisorは継続的にデータベースを監視しつづけているため、新たな情報が収集された後、必要に応じて推奨インデックスを提案します。

 

- インデックス作成時の動作
インデックスの作成を行った場合、以下の流れでインデックスの作成が行われます。
推奨インデックスがすぐに作成されない場合もありますが、以下のいずれかで時間がかかっていることが考えられるため、必要に応じて[インデックスの操作]を確認します。

1. 該当インデックスにおけるパフォーマンスのベースラインを収集します。（”保留中” の状態になります）
2. インデックスを作成します。（”実行しています" の状態になります）
3. 再度、該当インデックスにおけるパフォーマンスのベースラインを収集します。（”確認中” の状態になります）
4. もし、該当インデックスが悪影響を及ぼした場合、自動的に元に戻します。（”復元中” の状態になります）
5. インデックス操作が完了し、”成功”, ”復元”, もしくはエラーのステータスを返します。

 

[参考情報]
SQL Database Index Advisor
https://azure.microsoft.com/ja-jp/documentation/articles/sql-database-index-advisor/