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

みなさん、こんにちは。私は今トレーニングでシアトルに来ています。日本は寒いでしょうか？こちらも結構寒いです。
マリナーズの試合がある時期なら MLB を見たかったのですが、まだ開幕していないので今回は見れそうにありません。

前置きはこれくらいにしておいて。

さて、パート 4 の今回は、実際に Dynamics にクエリを投げた結果をもとに、クエリチューニングをしたいと思います。

[前提]

今回は取引先担当者を使って検証をして見ます。まずは SDK を利用して、1万件の取引先担当者を作成します。
内容は名前が account0 ～ account9999、取引先番号が 0 ～ 9999、電話が xx-xxxx-0000 ～ xx-xxxx-9999、
電子メール アドレス 2 が mail0000@test.local ～ mail9999@test.local、従業員数を 0 ～ 9999 とします。
また、現時点でインデックスはシステムで自動的に作成されたものだけと仮定します。実行したクエリは、SQL Profiler で
確認して、それを SQL Server Management Studio で再実行して、チューニングをして見ます。

[検索の内容]

高度な検索より、以下のような条件で検索を実施しました。

[検証]

検索実行時にキャプチャしたクエリは、以下になりました。

exec sp_executesql N'select top 51 account0.Name as ''name'', account0.AccountId as ''accountid'', account0.Telephone1 as ''telephone1'', account0.EMailAddress2 as ''emailaddress2'', account0.YomiName as ''yominame'' from Account as account0 where (account0.NumberOfEmployees = @NumberOfEmployees0 and (account0.DeletionStateCode in (@DeletionStateCode0))) order by account0.YomiName asc, account0.AccountId asc',N'@NumberOfEmployees0 int,@DeletionStateCode0 int',@NumberOfEmployees0=3452,@DeletionStateCode0=0

このクエリを、SQL Server Management Studio で、実行します。またその際、クエリメニューより、実際の実行プランを
含めるオプションを有効にしてください。またクエリの前に、以下のクエリを追加してください。

SET STATISTICS IO ON

では、実行してみましょう！結果は以下の通りです。

メッセージタブを見ると、以下のような数字が出ています。

テーブル 'AccountBase'。スキャン回数 1、論理読み取り数 1437、物理読み取り数 0、先行読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB 先行読み取り数 0。

まず、実行プランの絵からは、一番高いコストが Clustered Index Scan であることが分かります。これは、テーブルの
全ての情報を検索しているということで、インデックスが不足していることを示しています。またメッセージからは、
物理読み取り数が 0 となっています。これはデータは既にメモリ上にあり、ハードディスクの IO はないことを示します。
また論理読み取りは、メモリからの IO です。トータルコストは、1.09223 と出ました。(推定プランの一番左の絵にマウスを
当てると、この数値が確認できます。）

さて、インデックスが不足していることが分かりましたので、where 句に注目して、NumberOgEmployees に対して
インデックスを付けてみましょう。

CREATE NONCLUSTERED INDEX [ci_NumberOfEmployees] ON [dbo].[AccountBase]
(
    [NumberOfEmployees] ASC
)

これで、NumberOfEmployees 列にインデックスが作成されました。ではもう一度クエリを実行してみましょう。

今度は、一番右の絵が、Index Seek になりました。これでインデックスが使われていることが分かります。
またメッセージは、以下の通りです。

テーブル 'AccountBase'。スキャン回数 1、論理読み取り数 5、物理読み取り数 0、先行読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB 先行読み取り数 0。

論理読み取りが、1437から 5 へと激減しました。トータルコストも 0.01793 と、先ほどに比べてかなり低いですね。
さらにインデックスを改善してみましょう。一番右の下の絵を見ると、キー参照の絵が表示されています。これは、上の絵で
インデックスを使ってキーを取得した後、そのキーで実際のテーブルを見に行ったことを示しています。なぜでしょう？

それは、クエリに含まれる列が、インデックスに含まれていないからですね。ここでは以下のクエリでインデックスを
作り直してみましょう。

DROP INDEX [ci_NumberOfEmployees] ON [dbo].[AccountBase] GO
CREATE NONCLUSTERED INDEX [ci_NumberOfEmployees] ON [dbo].[AccountBase]
(
    [NumberOfEmployees] ASC,
    [DeletionStateCode] ASC
)
INCLUDE ( [Name],
[EMailAddress2],
[Telephone1],
[YomiName]) GO

これで、新しいインデックスが作られました。では再度クエリを実行してみましょう。結果は以下の通りです。

テーブル 'AccountBase'。スキャン回数 1、論理読み取り数 2、物理読み取り数 0、先行読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB 先行読み取り数 0。

トータルコスト 0.014644

それなりに改善したかと思います。どうでしょう、感じはつかめたでしょうか。

さて、全 4 回でインデックスに関して紹介してみましたが、いかがだったでしょうか。本当はこれで終わるつもりでしたが
せっかくなので、次回はパート 5 として、コストの高いクエリの見つけ方と、使われていないインデックスの見方など
紹介したいと思います。ではまた来週。

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