CRM データベースチューニング - インデックスはつけるべき？ パート 5

  • Article

みなさん、こんにちは。

無事日本に帰ってきましたが、桜がもう散り始めていて、悲しい気分です。みなさんはお花見されましたか？
東京では、今週末くらいがもう最後ですね。私の勤務する調布オフィスの前にも桜がありますが、もう花が少ないです。
いよいよ 4 月となり、日本の企業では新期になったのではないでしょうか。今期もよろしくお願いします。

さて、今回はパート 5 ということで、今までとは異なり、管理の面での追加情報を紹介します。

- 不要なインデックス -

インデックスは使用されてこそ意味があります。しかしシステムの使われ方、データの持ち方は常に変化するため、
状況によっては、使用されなくなったインデックスが出てきます。そこで SQL Server 2005 からの新機能である
DMV (動的管理ビュー）を使って、インデックスの使用頻度を調べてみましょう。

動的管理ビューとは、SQL Server のインスタンスが開始されてからのデータを、こつこつと採取して保持している
ビューで、使用状況を把握するために活用できます。SQL Server 2000 まででは取得しづらかったデータも簡単に
取得できます。今回使う DMV は sys.dm_db_index_usage_stats です。

select
    user_seeks + user_scans + user_lookups as scan -- スキャンされた回数
    ,TableName = OBJECT_NAME(s.[object_id]) -- テーブル名
    ,IndexName = i.name  -- インデックス名
        -- ,* -- 全てみたい場合
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON  s.[object_id] = i.[object_id]
    AND s.index_id = i.index_id
    WHERE  s.database_id = DB_ID() -- 現在のデータベース
    AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
  --  AND user_seeks = 0
  --  AND user_scans = 0 
  --  AND user_lookups = 0
ORDER BY 1 DESC

こちらのクエリを、組織 DB で実行すると、実際に使われたクエリが表示されます。どうでしょう。予想通りの結果が
出ていますでしょうか。また、条件でコメントアウトされた箇所を有効にすることで、検索に使用されていないインデックスを
調べることも可能です。

尚、こちらのクエリを実行した結果を考察する場合に、注意点があります。( 2 番重要です！)

1. この結果は SQL が最後に開始された以降のデータです。再起動から時間が経っていない場合、欲しい結果は出ません。

2. 結果が悪くても、CRM が既定で作成しているインデックスは、削除、変更しないでください。

こちらのクエリを定期的に実行することで、どのようなインデックスが使われているか判断が可能です。カスタムインデックスで
使われていないものは、削除や変更を検討してください。

- インデックスの断片化 -

インデックスは、テーブルと同様断片化していきます。断片化した場合、パフォーマンスに影響があります。インデックスの
断片化は、sys.dm_db_index_physical_stats DMV で調査が可能です。

具体的なクエリは、リンク先にいくつかありますので参考にしてみてください。また断片化の状況次第で、再構成または
再構築をすることをお勧めします。定期的にする必要があるので、スクリプトにするかデータベースメンテナンスジョブを
構成すると良いでしょう。

- データーベースエンジンチューニングアドバイザ -

どのようなインデックスが必要かを自動で判定してくれるツールが、データーベースエンジンチューニングアドバイザです。
使用方法は簡単で、具体的なクエリを渡すか、一定時間取得したプロファイラトレースをロードデータとして渡すことで
検討してくれます。ただし過信は禁物ですから、実際の内容は人間の目で判定すべきだと思います。

詳細は以下を確認してください。

チュートリアル : データベース エンジン チューニング アドバイザ
https://msdn.microsoft.com/ja-jp/library/ms166575.aspx

- ミッシングインデックス -

DMV には、他にも不足しているインデックスを検索してくれるものがあります。それが sys.dm_db_missing_index 系の
DMV です。いくつかの種類があり、組み合わせることで不足しているインデックスを特定してくれます。詳細は以下を参照
してください。

インデックス関連の動的管理ビューおよび関数 
https://msdn.microsoft.com/ja-jp/library/ms187974(SQL.90).aspx

尚、SQL Server 2008 を使用すれば、SQL Server Management Studio より実行プラン付きでクエリを実行することで
必要なインデックスの情報が表示されます。一度お試しください。

- フィルタ選択されたインデックス -

最後に、SQL Server 2008 の新機能として、フィルタ選択されたインデックスというものがあります。これは、列のすべての
データにインデックスをつけるのではなく、特定の値や範囲にのみインデックスを作成して、不要な情報を持たさないように
するための機能です。詳細は、以下をご参照ください。

フィルタ選択されたインデックスのデザイン ガイドライン
https://msdn.microsoft.com/ja-jp/library/cc280372.aspx

さて、全 5 回で簡単にインデックスの機能を紹介してきましたが、みなさんの Dynamics CRM 環境で役に立つことを
願っています。追加で質問や不明な点があれば、遠慮なくコメントからご質問ください。では。

参考:
隠れたデータを明らかにしてアプリケーションのパフォーマンスを最適化する
https://msdn.microsoft.com/ja-jp/magazine/cc135978.aspx

- Dynamics CRM サポート 中村 憲一郎