SQL Server 入門 SQL Server 入門

ホーム > 便利なT-SQL&クエリー集 > 断片化の状態によってインデックス (index) を再構成 (Reorganize) または再構築 (Rebuild) する方法

断片化の状態によってインデックス (index) を再構成 (Reorganize) または再構築 (Rebuild) する方法

断片化の状態によってインデックス (index) を再構成 (Reorganize) または再構築 (Rebuild) するには?

インデックスが断片化してしまうと、クエリーのパフォーマンスが低下してきますよね。

インデックスの再構成 (Reorganize) や再構築 (Rebuild)を行うと、断片化が解消されますので、定期的にインデックスの再構成 (Reorganize) や再構築 (Rebuild) をすると良いですね。

ですが、やみくもにデータベース内の全てのインデックスを再構成 (Reorganize) や再構築 (Rebuild) すると、データベースが大きくなればなるほど、実行にすごく時間がかかってしまったり、ログファイルが大きくなりすぎてしまったりします。

ここでは、断片化の状態によって、インデックス (index) を再構成 (Reorganize) または再構築 (Rebuild) する方法をご紹介します。


インデックス (index) の断片化の状態を調べる

まず、インデックス (index) の断片化の状態は sys.dm_db_index_physical_stats を使って調べることができます。

sys.dm_db_index_physical_stats が返す avg_fragmentation_in_percent は論理的な断片化 (インデックス内で順序が乱れたページ) の割合を示していて、再構成 (Reorganize) または再構築 (Rebuild) をするべきかどうかの判断の指標とすることができます。

例えば、以下のようなクエリーでスキーマ名、インデックスが属するオブジェクト名、インデックス名、断片化の割合を取得することができます。

SELECT   QUOTENAME(S.name) AS SchemaName,
         QUOTENAME(O.name) AS ObjectName,
         QUOTENAME(I.name) AS IndexName,
         PS.avg_fragmentation_in_percent
FROM     sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') AS PS
             INNER JOIN sys.objects AS O
                ON PS.object_id = O.object_id
             INNER JOIN sys.schemas AS S
                ON O.schema_id = S.schema_id
             INNER JOIN sys.indexes AS I
                ON PS.object_id = I.object_id
                   AND PS.index_id = I.index_id
WHERE    avg_fragmentation_in_percent >= 10.0
         AND PS.index_id > 0;

インデックス (index) の断片化の状態

avg_fragmentation_in_percent >= 10.0 を指定することで、断片化が 10 % 以上のインデックスを取得しています。


断片化の状態によってインデックス (index) を再構成 (Reorganize) または再構築 (Rebuild) する

インデックスの断片化の状態がわかったら、それによってインデックス (index) を再構成 (Reorganize)するか、または再構築 (Rebuild) するか決定します。

例えば、10 % 以上 30 % 未満であれば再構成 (Reorganize)、 30% 以上であれば再構築 (Rebuild) するとすれば、上記のクエリーでカーソルを作ってループし、以下のような文字列 @Sql を作成し、EXEC やsp_executesql でダイナミッククエリーとして実行すれば OK です。

------------------ カーソルのループ内------------------

IF @avg_fragmentation_in_percent < 30.0
BEGIN

         SET @Sql = N'ALTER INDEX ' + @IndexName + N' ON ' + @SchemaName + N'.' + @ObjectName + N' REORGANIZE';

END
ELSE
BEGIN

         SET @Sql = N'ALTER INDEX ' + @IndexName + N' ON ' + @SchemaName + N'.' + @ObjectName + N' REBUILD';

END

EXEC (@Sql);

----------------------------------------------------

※ パーティションを分けている場合はパーティションも指定して下さい。

SQL Server 関連の人気書籍
ホーム > 便利なT-SQL&クエリー集 > 断片化の状態によってインデックス (index) を再構成 (Reorganize) または再構築 (Rebuild) する方法