◆インデックスの保守

• 断片化とは
• 断片化の調査： dm_db_index_physical_stats
• 断片化の解消（インデックスの再構築と再構成）
• 断片化の事前防止策： FILLFACTOR

断片化とは

インデックスのリーフ ページは、Index Scan を高速化するために、次のようにそれぞれのページ同士がリンクしています。

リーフ ページは、物理的に連続して格納されている場合は、先読み（先行読み取り）機能が効果的に働き、パフォーマンス良くデータを取得することができます。
断片化（fragmentation: フラグメンテーション）は、次のようにリーフ ページが連続的ではなく断片的に格納された状態です。

このように、リンクしているページが物理的に離れた場所にある状態が断片化が発生した状態です。このままでは、先読み機能が効果的に働かなくなり、Index Scan のパフォーマンスが大きく低下します。したがって、インデックスを作成した後は、断片化を事前防止する、あるいは断片化を解消するための保守を行うことが非常に重要になります。

断片化の原因（ページ分割）

デフォルトでは、ページ内は、追加または更新されたデータによって満杯に埋まっています。この状態で、間に割り込む値が追加されると、「ページ分割」が発生します。たとえば、次の図は、「姓」列へ作成したインデックスのリーフ ページが満杯に埋まっている状態を表していますが、このとき「Akiba」さんという間に割り込む値が INSERT されたとすると、ページには入りきらないので、物理的に離れた場所へ新しいページが作成されます。

この動作（ページ分割）は、インデックスが常に昇順に並べ替えられた状態に保たれている必要があるために行われています。間に割り込む値が追加されると、その値によって溢れ出たデータを格納するための新しいページが必要になるのです。また、このページには、溢れ出た1行のみが格納されるのではなく、元のページと新しいページが半分半分になるように調整されます。これは、もう一度間に割り込む値が追加されたとしても、簡単にページ分割を発生させないようにするための処置です（ページ分割を連続して発生させないための処置です）。このようなページ分割は、元のページと新しいページに半分半分にデータを割り当てることから、「50-50ページ分割」とも呼ばれています。
50-50ページ分割によって作成された新しいページは、物理的には、最後の空きページが利用される場合がほとんどです。これによって、リーフ ページが断片化した状態が生まれます。

断片化の調査： dm_db_index_physical_stats

断片化が発生しているかどうかを調べるには、Step3 でインデックスの階層数と使用ページ数を調べるために利用した dm_db_index_physical_stats 動的管理関数を利用します。構文は次のとおりです。

この関数の出力結果のうち、主なものは次のとおりです。

avg_fragmentation_in_percent という列で、断片化の割合を確認することができます。

スキャン モード

dm_db_index_physical_stats 関数の第5引数で指定するスキャン モードは、次の 3種類があります。

LIMITED モードを使用すると、最も高速に断片化を調査することができます。

LIMITED モードが高速な理由

dm_db_index_physical_stats 関数の LIMITED モードが高速な理由は、インデックスのリーフ レベルをスキャンせずに、ルート ページと中間ページのみを調査して、断片化の割合を算出するためです。次の図のように、中間ページにはリーフ ページのページ番号が格納されているので、ページ番号が連続か不連続であるかをチェックするだけで、断片化の割合を調べることができます。

ただし、LIMITED モードの場合には、avg_page_space_used_in_percent（ページの平均使用密度）や、record_count（ページ内の行数）など、結果を取得できない列もあります。詳しくは、オンライン ブックの次の場所を参考にしてください。

Let's Try

1. まずは、前の Step で作成した付加列インデックス「index_姓名」の、インデックス ID を調べましょう。
   

   SELECT name, index_id, * FROM sys.indexes WHERE object_id = OBJECT_ID('社員')

   
   インデックスID（index_id）が「3」であることを確認できます。
2. 続いて、dm_db_index_physical_stats 関数を利用して、「index_姓名」の断片化の状態をチェックしてみましょう。
   

   SELECT avg_fragmentation_in_percent, index_id, index_level, page_count FROM sys.dm_db_index_physical_stats (DB_ID('sampleDB'), OBJECT_ID('社員'), 3, NULL , 'DETAILED') ORDER BY index_id, index_level DESC

   
   
   「avg_fragmentation_in_percent」列は、すべての階層（index_level）で、「0」となっていることから、断片化が全く発生していないことを確認できます。また、このときの、リーフ レベル（index_level が 0）のページ数（page_count 列）が、「35」ページであることも確認できます。
3. 次に、社員テーブルに対して、データを 1件 INSERT してみましょう。
   

   INSERT INTO 社員 VALUES(10001, 'Aiba', 'Jiro', '男性', '03-1234-xxxx', '')

4. データの追加後、もう一度同じクエリを実行して、index_姓名の断片化の状態をチェックしましょう。
   
   今度は、リーフ レベルで、「5.555…」パーセントの断片化が発生し、ページ数がデータを追加する前より 1ページ増えて、「36」ページになっていることを確認できます。
5. 続いて、さらに、社員テーブルに対して、9 件のデータを追加してみましょう。
   

   INSERT INTO 社員 VALUES(10002, 'Eto', 'Goro', '男性', '03-1234-xxxx', '') INSERT INTO 社員 VALUES(10003, 'Fujiwara', 'Goro', '男性', '03-1234-xxxx', '') INSERT INTO 社員 VALUES(10004, 'Goto', 'Goro', '男性', '03-1234-xxxx', '') INSERT INTO 社員 VALUES(10005, 'Inoue', 'Goro', '男性', '03-1234-xxxx', '') INSERT INTO 社員 VALUES(10006, 'Kato', 'Goro', '男性', '03-1234-xxxx', '') INSERT INTO 社員 VALUES(10007, 'Matsumoto', 'Goro', '男性', '03-1234-xxxx', '') INSERT INTO 社員 VALUES(10008, 'Oshima', 'Goro', '男性', '03-1234-xxxx', '') INSERT INTO 社員 VALUES(10009, 'Saito', 'Goro', '男性', '03-1234-xxxx', '') INSERT INTO 社員 VALUES(10010, 'Yamamoto', 'Goro', '男性', '03-1234-xxxx', '')

6. データの追加後、もう一度断片化の状態をチェックします。
   
   今度は、リーフ レベルで、「37.20…」パーセントの断片化が発生していることを確認できます。また、ページ数も「43」ページへ増えています。

断片化の解消（インデックスの再構築と再構成）

断片化を解消するには、次の 3つの方法があります。

• インデックスのオフライン再構築
• インデックスのオンライン再構築（Enterprise エディション のみで利用可能）
• インデックスの再編成

これらは、いずれも ALTER INDEX ステートメントを利用して実行することができます。

インデックスのオフライン再構築（REBUILD）

インデックスの再構築（オフライン）は、内部的には新しい領域へインデックスを再作成し、古いインデックスを削除することで、断片化を解消します。したがって、インデックスの再構築中は、インデックス全体がロックされるので、再構築中は、別のトランザクションからはインデックスに対して一切アクセスすることができません。再構築が完了するまでは、ユーザー操作は待ち状態になります。これが「オフライン」と呼ばれる理由です。
クラスター化インデックスの場合は、実際のデータそのものをインデックス内へ格納しているので、再構築中にはテーブル データすべてがアクセスできないことに注意する必要があります。

Note： 再構築にかかる時間
再構築にかかる時間は、インデックスが使用するページ数（ディスク容量）が大きければそれだけ時間がかかります。使用するページ数が大きいのは、行サイズが大きいインデックスで、クラスター化インデックス、カバリングインデックスなどです。特にクラスター化インデックスは、実際のデータそのものを格納しているため、再構築には非常に時間がかかります。

インデックスをオフライン再構築（REBUILD）するには、ALTER INDEX ステートメントを次のように利用します。

ALTER INDEX インデックス名 ON テーブル REBUILD

Let's Try

1. 社員テーブルへ作成した付加列インデックス「index_姓名」を再構築してみましょう。
   

   ALTER INDEX index_姓名 ON 社員 REBUILD

   
   「コマンドは正常に完了しました」と表示されれば、再構築が完了しています。
2. 次に、dm_db_index_physical_stats 関数をクエリして、断片化の状態をチェックしてみましょう。
   
   リーフ レベルの avg_fragmentation_in_percent が「0」% になり、断片化が完全に解消されたことを確認できます。また、page_count も「35」ページ（断片化する前と同じ）へ戻っていることを確認できます。

インデックスのオンライン再構築

インデックスの再構築は、オンラインで行うことも可能です。これは、SQL Server 2005 から提供された機能で、Enterprise エディションでのみ利用することができます。インデックスの再構築をオンラインで実行した場合は、再構築中にユーザーがデータへアクセスすることができます。
インデックスの再構築をオンラインで行うには、ALTER INDEX ステートメントを次のように実行します。

ALTER INDEX インデックス名 ON テーブル REBUILD WITH ONLINE = ON

インデックスの再編成（REORGANIZE）

インデックスの再編成は、SQL Server 2000 までは、DBCC INDEXDEFRAG コマンドとして提供されていた機能です。インデックスの再編成は、リーフ ページの断片化のみを解消し、再編成の実行中もユーザーがアクセスすることができます。ただし、ロック中のページはスキップされて、また、断片化の度合いがひどい場合には、インデックスの再構築（REBUILD）よりも実行時間のかかってしまうので注意してください。
インデックスの再編成を実行するには、ALTER INDEX ステートメントを次のように実行します。

ALTER INDEX インデックス名 ON テーブル REORGANIZE

再構築と再編成の違い

インデックスの再編成は、同じ領域を再利用して、それぞれのページを比較して並び替えを行うことで、断片化を解消しています。このような内部動作の違いがあるので、再編成は断片化の割合が大きい場合には、非常に時間がかかってしまいます。
これに対して、インデックスの再構築は、新しい領域にインデックスを再作成します。

Note： 断片化の割合が 30% 未満なら再編成、それ以上なら再構築
再構築を行うか、再編成を行うのかのおおまかな指針は、断片化の割合が 30％ 未満なら再編成、それ以上なら再構築です。再編成（REORGANIZE）は、断片化の割合がひどい場合には、非常に時間がかかります。たとえば、次の 2つのグラフは、弊社のお客様のデータで、再編成と再構築の実行時間を比較したものです。

Note： 再構築と再編成の詳細比較
再構築と再編成の違いについては、次の表を参考にしてください。

断片化の事前防止策： FILLFACTOR

インデックスの再構築や再編成を実行して断片化を解消しても、データの追加や更新、削除が行われていくと、また断片化が発生します。断片化の度合いがひどくなっていくと、パフォーマンスへの悪影響も起こってきます。したがって、断片化がすぐに発生しないように事前防止策を講じておくことが重要です。これを行えるのが「FILLFACTOR」です。
FILLFACTORは、「充填率」とも呼ばれ、インデックスの再構築時にリーフ ページ内の領域をデータで占める割合を制御するためのオプションです。これは0～100%の間に設定することができます。たとえば、FILLFACTOR を 80%に設定した場合は、次のように 80%をデータで埋め、20%を空き領域として残すことができます。

このように、事前に空き領域を作成しておけば、データが追加されてもこの領域が利用されるので、（空き領域がなくなるまでは）50-50 ページ分割の発生（断片化）を防ぐことができます。

Note： FILLFACTOR のデフォルト値
FILLFACTOR は、デフォルトは 0% に設定されますが、これは 100% と同じ意味で、ページ内の領域をデータですべて埋めます。したがって、デフォルトでは、リーフページは満杯に埋められ、この状態で間に割り込むデータが追加されると 50-50 ページ分割（断片化）が発生します。

FILLFACTOR を設定したインデックスの再構築

FILLFACTOR は、インデックスの再構築時に設定することができます。これは、ALTER INDEX ステートメントを次のように記述します。

ALTER INDEX インデックス名 ON テーブル REBUILD WITH ( FILLFACTOR = 値 )

Let's Try

1. まずは、社員テーブルに対してデータを 10 件 INSERT して、断片化が発生することを確認してみましょう。（前の Step では、インデックスの再構築を実行したので、断片化が完全に解消されている状態です）。
   

   INSERT INTO 社員 VALUES(10011, 'Aiba', 'Saburo', '男性', '03-1234-xxxx', '') INSERT INTO 社員 VALUES(10012, 'Matuda', 'Saburo', '男性', '03-1234-xxxx', '') INSERT INTO 社員 VALUES(10013, 'Kato', 'Goro', '男性', '03-1234-xxxx', '') INSERT INTO 社員 VALUES(10014, 'Matsumoto', 'Goro', '男性', '03-1234-xxxx', '') INSERT INTO 社員 VALUES(10015, 'Okada', 'Ryu', '男性', '03-1234-xxxx', '') INSERT INTO 社員 VALUES(10016, 'Yamamoto', 'Hiroko', '女性', '03-1234-xxxx', '') INSERT INTO 社員 VALUES(10017, 'Oda', 'Hiroshi', '男性', '03-1234-xxxx', '') INSERT INTO 社員 VALUES(10018, 'Sakamoto', 'Kenji', '男性', '03-1234-xxxx', '') INSERT INTO 社員 VALUES(10019, 'Uchia', 'Yumi', '女性', '03-1234-xxxx', '') INSERT INTO 社員 VALUES(10020, 'Nakamura', 'Hiromi', '女性', '03-1234-xxxx', '')

2. データの追加後、dm_db_index_physical_stats 関数を利用して、断片化の状態をチェックしてみましょう。
   

   SELECT avg_fragmentation_in_percent, index_id, index_level, page_count FROM sys.dm_db_index_physical_stats (DB_ID('sampleDB'), OBJECT_ID('社員'), 3, NULL , 'DETAILED') ORDER BY index_id, index_level DESC

   
   
   リーフ ページが「37.2…」パーセントの断片化が発生していることを確認できます。このように、インデックスを再構築した後に、データが追加された場合は、また、断片化が発生してしまうのです。
3. 次に、FILLFACTOR を利用して、断片化を事前防止してみましょう。FILLFACTOR を設定するには、次のように ALTER INDEX ステートメントを実行して、インデックスを再構築します。
   

   ALTER INDEX index_姓名 ON 社員 REBUILD WITH ( FILLFACTOR = 70 )

   
   FILLFACTOR には、70 を指定することで、30% の空き領域を作っています。
4. 次に、dm_db_index_physical_stats 関数を利用して、断片化の状態をチェックします。
   
   断片化が「0」% になって、断片化が起きていない状態であることを確認できます。
   また、リーフ レベルの page_count が、「50」ページへ増えていることにも注目します。これは、30% の空き領域を作ったことで、使用するページ数が増えているためです。
5. dm_db_index_physical_stats 関数では、avg_page_space_used_in_percent 列を取得すると、ページの平均使用密度を調べることができます。
   

   SELECT avg_page_space_used_in_percent ,avg_fragmentation_in_percent, index_id, index_level, page_count FROM sys.dm_db_index_physical_stats (DB_ID('sampleDB'), OBJECT_ID('社員'), 3, NULL , 'DETAILED') ORDER BY index_id, index_level DESC

   
   
   FILLFACTOR を70%へ設定しているので、avg_page_space_used_in_percent（ページの平均使用密度）が「69.4…」（約70%）であることを確認できます。
6. 次に、もう一度、社員テーブルに対して、データを 10件 INSERT してみましょう。
7. データの追加後、dm_db_index_physical_stats 関数を利用して、断片化の状態をチェックしてみましょう。
   
   結果は、断片化が発生していないことを確認できます。また、page_count も増えていないことを確認できます。
   このように、FILLFACTOR を設定しておくと、今後追加されるデータを考慮して、あらかじめページに余裕をもたせて、インデックスを再構築することができるので、再構築後すぐに断片化が発生することを防ぐことができます。

おわりに

最後までこの自習書の内容を試された皆さま、いかがでしたでしょうか？
インデックスは、SQL Server のパフォーマンスを大きく左右する非常に重要な機能です。1つのテーブルに対して複数のインデックスを作成することができますが、, むやみに作成するのは良くありません。更新のオーバーヘッド（データ更新時に、実際のデータだけでなく、インデックス自体を更新する負荷）があるからです。
また、せっかくインデックスを作成しても、インデックスが活用されなかったり、検索のパフォーマンスが向上しなかったりすることもあります。インデックスは、特性をきちんと理解した上で、付加列インデックスなどを効果的に活用して、パフォーマンスの向上に役立てていただければと思います。
また、インデックスの作成後は、定期的な断片化のチェックも重要です。これを怠ると、「ある日突然遅くなった」という事態になりかねません。FILLFACTOR を設定して断片化を事前防止し、定期的にインデックスを再構築または再編成を実行して、健全な状態へ保つようにしましょう。

列ストア インデックスによる飛躍的な性能向上（2012 の新機能）

SQL Server 2012 からは、パフォーマンスを飛躍的に向上させることができる「列ストア インデックス」という新しいタイプのインデックス機能も提供されました。このインデックスは、SQL Server 2008 R2 の PowerPivot for Excel で実装されたインメモリのカラムベース エンジン（xVelocity エンジン）を RDB へ応用したものです。このエンジンでは、列単位でインデックスを格納し、それらは高度に圧縮されています。
列ストア インデックスは、大量のデータに対する集計処理時に大きな性能向上を期待できる機能で、特に、夜間バッチ処理時（夜間バッチでの日次集計や月次集計処理など）や、DWH（データ ウェアハウス）環境での集計処理時に大変役立つ機能です。弊社のお客様データ（1億2千万件の DWH）を利用して、列ストア インデックスの性能効果を検証したところ、以下のような結果が得られました。

列ストア インデックスの作成方法

列ストア インデックスの作成方法は、非常に簡単で、次のように COLUMNSTORE キーワードを追加するだけです。

CREATE NONCLUSTERED COLUMNSTORE INDEX インデックス名 ON テーブル名 (列名1, 列名2, 列名3, ～)

列ストア インデックスは、性能を向上させるために大変役立つ機能なので、ぜひ活用してみてください。本自習書シリーズの新機能編「No.3 DWH（データ ウェアハウス）関連の新機能」では、列ストア インデックスの効果を簡単に確認できるようにしたスクリプト（ステップ バイ ステップ形式での検証スクリプト）を紹介していたり、列ストア インデックスの利用時の注意点などを説明したりしているので、こちらもぜひご覧いただければと思います。