みなさん、こんにちは。私は今トレーニングでシアトルに来ています。日本は寒いでしょうか？こちらも結構寒いです。 マリナーズの試合がある時期なら 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 サポート 中村 憲一郎
みなさん、こんにちは。
さて 3 回目となる今回は、どの列をインデックスに含めれば良いかということを考えたいと思います。
クラスタ化インデックス
Dynamics CRM では、クラスタ化インデックスは既定で作成されます。基本的には列を一意に特定でき、かつ更新が 極力ない列、または、そのような列の組み合わせをクラスタ化インデックスにすると良いとされています。そのような列が テーブルに無い場合は、guid 列を作成してクラスタ化インデックスにすると良いでしょう。並べ替えが発生しないように、 guid を作成する場合は newsequentialid() という関数が使えますので、参考にしてください。
非クラスタ化インデックス
さて、問題となるのが非クラスタ化インデックスですが、どのように含める列を考えればいいのでしょうか。インデックスを 作成する理由は、データを並べ替えておいて検索時のパフォーマンスをあげることが目的ですから、基本的には Where 句 に含まれる列のうち、インデックスを有効に使えるものを含めることになります。しかしそれ以外の目的で列を含めることも あります。
非クラスタ化インデックスに列を含める場所は、実は2箇所あります。まず非クラスタ化インデックスの構造を見てみましょう。 非クラスタ化インデックスは、以下のようなツリーの形式をとっています。 （図は MSDN より）
上記に示されるように、非クラスタ化インデックスはルート ノードがあり、最下部にリーフ ノードがあります。またデータの 数によっては、複数の中間ノードが、ルートとリーフ��間に存在します。インデックスに列を含めるということは、基本的には 全てのページに、対象となる列のデータが含まれます。つまり、列A、列B、列C を非クラスタ化インデックスに含めると、 全てのノードの全てのページに、この 3 つの列のデータが含まれます。
この一方で、一番下の層であるリーフ ノードにだけ列を含めるテクニックも存在します。それが付加列インデックスです。 ではなぜそのようなテクニックが必要か、以下に具体例を挙げて説明します。
付加列インデックスが有効な理由
以下のクエリを考えて見ましょう。
select firstname, lastname, address, phone from contact where lastname = “中村”
この場合、通常は lastname 列に非クラスタ化インデックスを作成します。では、その状態で、SQL Server はどのような 動きをするでしょうか。基本的には、以下のような動作を行います。
1. lastname 列に作成された非クラスタ化インデックスを検索して、中村に該当する列のキー列を取得する。
2. contact テーブルに対して、取得したキー列を使って表示に必要な列のデータを取得する。
ある意味シンプルな動作ですが、ここで SQL Server は非クラスタ化インデックスのデータと、実際のテーブルのデータの 2 箇所を検索しています。この動作を Key Lookup 操作と言います。
では、非クラスタ化インデックス自体に、表示する列も含めるとどうなるでしょうか。答えは簡単で、実際のテーブルへの 検索は行いません。なぜなら、結果の表示に必要な全ての情報が、非クラスタ化インデックスに存在するからです。
しかし、前述したとおり、非クラスタ化インデックスに列を含めるということは、ページに実データを持つこととなります。 今回の場合、検索を絞り込むためには lastname 列しか使っていないため、他の列までルートや中間ノードにデータを 持っても、意味がありません。そこでこれらの列を付加列インデックスとして追加することで、ルートや中間ノードには、 lastname のデータだけを、リーフ ノードには、lastname 列と他の列のデータを含めることができます。
全ての表示列を付加列インデックスに含めるべき？
インデックスに列を含めるということは、実際のデータをインデックスのデータに持つということですから、例えば select 文が テーブルのほぼ全ての列を含んでいるとすると、インデックスのデータ量は、実際のテーブルのデータ量に匹敵します。 よってバランスが必要になります。また含める列が増えれば増えるほど、更新時などにメンテナンスが必要となる可能性が 高くなりますので、そのあたりも考慮に入れましょう。
今回はここまでです。
次回は実際に Dynamics CRM にクエリを投げてみて、どのようなインデックスがパフォーマンスを改善するか具体的に 紹介したいと思います。
参考URL：
非クラスタ化インデックスの構造 http://msdn.microsoft.com/ja-jp/library/ms177484.aspx
付加列インデックス http://msdn.microsoft.com/ja-jp/library/ms190806.aspx
NEWSEQUENTIALID() http://msdn.microsoft.com/ja-jp/library/ms189786(SQL.90).aspx
Key Lookup プラン表示操作 http://msdn.microsoft.com/ja-jp/library/bb326635(SQL.90).aspx
今週は、東京でまた雪が降りましたね。寒すぎて、桜がどうなるか既に心配です。今回は、前回に引き続きインデックスの パート 2 ということで、インデックスの良い面と悪い面を紹介したいと思います。では早速。
インデックスの良い面
インデックスは、特定のレコードを検索するために活用できます。よって select 処理はもちろん、特定の行を更新する update 処理や、特定の行を削除する delete でもその効果を発揮します。また、複数のテーブルを結合する Join 処理 などでも、列を特定する必要があるため、効果が期待できます。
インデックスの悪い面
その一方でデータが書き換わる処理、つまり insert、update、delete が行われた場合には、関連するインデックスが 更新される必要があります。
前回に引き続き、以下のテーブルを考えて見ます。(携帯番号列を追加しています。）
<メンバーテーブル>
また非クラスタ化インデックスが、前回同様、電話番号にあるとします。
さてこの場合、以下のクエリを実行すると仮定しましょう。
insert into メンバーテーブル (名前、電話番号、携帯番号) values (‘河越 弘典’, ‘03-6666-xxxx’, ‘090-6666-xxxx’)
この場合、当然メンバーテーブルは新しい行が追加されるため、SQL Server によってメンテナンスされます。また同時に 電話番号列に作成した、非クラスタ化インデックスもメンテナンスされることになります。また電話番号列を更新するような update が処理された場合も同様です。
では、次のクエリではどうでしょうか。
update メンバーテーブル set 携帯番号 = ‘090-2345-xxxx’ where 名前 = ‘中村 憲一郎’
この場合、携帯番号の更新のため、電話番号列には更新がなく、非クラスタ化インデックスはメンテナンスされません。
このように状況に応じて内容は変わるものの、非クラスタ化インデックスを持つ場合は、実際のテーブル以外にインデックス もメンテナンスされる場合がありますし、クラスタ化インデックスがあるテーブルでは、insert されるたびに、順番を確認して データを保存することとなり、パフォーマンスに影響が出ます。
では、どうすればいいの？
対処の方針は、そのシステムによって変わります。一般的にレポートや分析サービスのように、更新が少なく読み取りが 多いシステムの場合には、インデックスが効果を発揮します。その一方で、トランザクションが多いシステムの場合には、 インデックスがあることで処理が低下する恐れもあります。そのため、必要最小限のインデックスを作成を心がけてください。
他の注意点は？
インデックスを作成した場合、並び替えなどの処理はSQL Server が自動で行いますが、インデックスのデータに発生した フラグメントは解消してくれません。そのため、インデックスのデフラグメントが必要となります。デフラグメントの方法は いくつかありますので、以下の URL を参考にしてください。
効果的なデータベース メンテナンスのヒント http://technet.microsoft.com/ja-jp/magazine/2008.08.database.aspx#id0230032
さて次回は、非クラスタ化インデックスに含めるべき列や、その手法に関して紹介したいと思います。お楽しみに。
東京は最近すっかり寒いのですが、いかがお過ごしでしょうか？春が待ち遠しい今日この頃です。さて、今日は パフォーマンスチューニングに関して、データ���ースのインデックスを取り上げます。全 4 回の予定です。第 1 回は、インデックスとは何かを簡単におさらいします。
インデックスとは？
すでにご存知の方も多いと思いますが、今回は簡単にインデックスの話をします。インデックスは、各テーブルに対して 作成するもので、テーブルに格納されている情報の一部分を抜き出したものです。この一部分の情報を整理しておき、 実際のデータと結びつけることで、データベース処理を高速化することが目的です。データの読み取り時にインデックスを 利用することで、無駄な処理を省略することが可能となります。
以下に例を見てみましょう。
まず例として、以下のようなテーブルを考えます。またインデックスは今のところありません。 （例は3行ですが、例えばこのようなデータが1万件あるとします）
このテーブルに対して、次のクエリを実行するとしましょう。
select 名前 from メンバーテーブル where ID = 2
このクエリを実行した場合 SQL Server はどのような処理を実行するでしょう？上から 2 行目の結果が欲しいことは 人間の目からはすぐに分かりますが、結果として SQL Server は全てのデータを検索します。(この場合 1 万件） 理由は、2 行目以外の行にも、 ID が 2 の行があるかもしれないからです。テーブルを設計した人から見れば、ID は 1 ずつ大きくなることが分かっているのですが、 SQL Server にはそれが分かりません。そこでインデックスの登場です。
では、インデックスを ID 列につける場合を考えましょう。この場合、実際のテーブルとは別にインデックスの情報を SQL Server が持つことになります。（※非クラスタ化インデックスの場合）
インデックスは情報が順番に並んでいることが保障されているため、SQL Server はインデックスの中から、全ての 2 の データを見つけた後、それ以降は 3 以上のデータしかないと分かるため検索を終了します。その後、インデックスの情報 から、実際のテーブルの行を読み取り、結果を返します。
よって、SQL Server はインデックスを読み取る作業と、実際のテーブルを読み取る作業をするわけです。もしこの作業が 実際のテーブルを全て読み取るより、結果として速くなると判断した場合、SQL Server はインデックスを利用しますし、 実際のテーブルを全て読み取って確認したほうが速いと判断した場合、インデックスを使いません。
インデックスの種類
インデックスには、クラスタ化インデックスと非クラスタ化インデックスの 2 種類があります。
[クラスタ化インデックス]
クラスタ化インデックスは、テーブルに 1 つしか作成できません。なぜならクラスタ化インデックスとは、テーブルそのもの だからです。たとえとして、例のメンバーテーブルを取り上げましょう。
クラスタ化インデックスがない場合、以下の順番でデータを挿入したらどうなるでしょう。
1. ID 1 のレコードを挿入
2. ID 3 のレコードを挿入
3. ID 2 のレコードを挿入
この場合、テーブルは以下のようにデータを保持します。
一方で、ID 列にクラスタ化インデックスを作成した場合には、ID 列の順番を保守しながらデータの挿入を行うため、 以下のようにデータを保持します。
[非クラスタ化インデックス]
非クラスタ化インデックスは、実際のテーブル以外に、インデックスのデータを作成します。よって各テーブルに複数の インデックスを作成することが可能です。その場合、複数のインデックスデータを作成します。例えば、電話番号に 非クラスタ化インデックスを作成した場合には、以下のようなデータを保持します。
1. 実際のテーブルのデータ （1万件）
2. 電話番号列の非クラスタ化インデックスのデータ （1万件）
この状態で、次のクエリを実行するとしましょう。
select 名前 from メンバーテーブル where 電話番号 = ’03-3333-xxxx’
この場合、SQL Server はまずインデックスのデータを検索します。そこで ID が 3 の列がそのデータを持っていると判断し、 実際のテーブルの ID が 3 の列のデータを取得します。ここで明らかなのですが、非クラスタ化インデックスを作成するには クラスタ化インデックスが必要となります。ここの例では、クラスタ化インデックスが ID 列にあると想定しています。
尚、インデックスの無いテーブルは、ヒープと呼ばれます。他にもインデックスを作成するテクニックとして複数の行を指定 する方法や、データの持ち方を設定する方法などありますが、それはまた次回以降で詳しく説明したいと思います。
さて、今回はパート 1 ということでインデックスの紹介をしたところで終わりにします。 次回は、インデックスのプラスとマイナスを紹介しますので、お楽しみに。
参考: SQL Server 2005 Books Online インデックスについて http://msdn.microsoft.com/ja-jp/library/ms179613(SQL.90).aspx
SQL Server 2008 Books Online インデックスについて http://msdn.microsoft.com/ja-jp/library/ms179613.aspx