断片化の状態によってインデックス (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) をするべきかどうかの判断の指標とすることができます。

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

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

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

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

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

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

ご参考までに、マイクロソフトのサイトで紹介されているスクリプトを載せておきます。

「sys.dm_db_index_physical_stats (Transact-SQL)」の「D. sys.dm_db_index_physical_stats をスクリプトで使用してインデックスを再構築または再構成する」より抜粋

-- Ensure a USE <databasename> statement has been executed first.  
SET NOCOUNT ON;  
DECLARE @objectid int;  
DECLARE @indexid int;  
DECLARE @partitioncount bigint;  
DECLARE @schemaname nvarchar(130);   
DECLARE @objectname nvarchar(130);   
DECLARE @indexname nvarchar(130);   
DECLARE @partitionnum bigint;  
DECLARE @partitions bigint;  
DECLARE @frag float;  
DECLARE @command nvarchar(4000);   
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function   
-- and convert object and index IDs to names.  
SELECT  
    object_id AS objectid,  
    index_id AS indexid,  
    partition_number AS partitionnum,  
    avg_fragmentation_in_percent AS frag  
INTO #work_to_do  
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')  
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;  
  
-- Declare the cursor for the list of partitions to be processed.  
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;  
  
-- Open the cursor.  
OPEN partitions;  
  
-- Loop through the partitions.  
WHILE (1=1)  
    BEGIN;  
        FETCH NEXT  
           FROM partitions  
           INTO @objectid, @indexid, @partitionnum, @frag;  
        IF @@FETCH_STATUS < 0 BREAK;  
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)  
        FROM sys.objects AS o  
        JOIN sys.schemas as s ON s.schema_id = o.schema_id  
        WHERE o.object_id = @objectid;  
        SELECT @indexname = QUOTENAME(name)  
        FROM sys.indexes  
        WHERE  object_id = @objectid AND index_id = @indexid;  
        SELECT @partitioncount = count (*)  
        FROM sys.partitions  
        WHERE object_id = @objectid AND index_id = @indexid;  
  
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.  
        IF @frag < 30.0  
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';  
        IF @frag >= 30.0  
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';  
        IF @partitioncount > 1  
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));  
        EXEC (@command);  
        PRINT N'Executed: ' + @command;  
    END;  
  
-- Close and deallocate the cursor.  
CLOSE partitions;  
DEALLOCATE partitions;  
  
-- Drop the temporary table.  
DROP TABLE #work_to_do;  
GO 

以上、断片化の状態によってインデックス (index) を再構成 (Reorganize) または再構築 (Rebuild) する方法についてご説明しました。