SQL Server 入門 SQL Server 入門

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

ホーム > 便利なT-SQL&クエリー集 > 断片化の状態によってインデックス (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) する方法