SQL Server/INDEX再構築

指定したDB内で、30%以上断片化しているINDEXを再構築するクエリ †

SELECT a.object_id
      ,c.name as tablename
      ,a.index_id
      ,b.name as indexname
      ,cast(avg_fragmentation_in_percent as decimal(5,2)) as before
      ,cast(null as decimal(5,2)) as after 
 INTO #FRAG_INDEX
 FROM sys.dm_db_index_physical_stats 
        (DB_ID(), NULL, NULL, NULL, NULL) AS a
 INNER JOIN sys.indexes AS b
 ON a.object_id = b.object_id
 AND a.index_id = b.index_id
 AND b.name IS NOT NULL
 INNER JOIN sys.objects AS c
 ON a.object_id = c.object_id
 AND is_ms_shipped = 0
 WHERE avg_fragmentation_in_percent > 30

-- 処理前
SELECT * FROM #FRAG_INDEX
GO

DECLARE @sql nvarchar(4000)
       ,@object_id varchar(255)
       ,@tablename varchar(255)
       ,@index_id varchar(255)
       ,@indexname varchar(255)
       ,@before decimal(5,2)
       ,@after decimal(5,2)
DECLARE fragmented_index_cursor CURSOR FOR 
 SELECT * FROM #FRAG_INDEX

OPEN fragmented_index_cursor

FETCH NEXT FROM fragmented_index_cursor
    INTO @object_id, @tablename, @index_id, @indexname, @before, @after
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC ('ALTER INDEX ' + @indexname + ' ON ' + @tablename + ' REBUILD')

    FETCH NEXT FROM fragmented_index_cursor
        INTO @object_id, @tablename, @index_id, @indexname, @before, @after
END

CLOSE fragmented_index_cursor
DEALLOCATE fragmented_index_cursor

UPDATE x
  SET after = a.avg_fragmentation_in_percent
  FROM #FRAG_INDEX as x
  INNER JOIN sys.dm_db_index_physical_stats
         (DB_ID(), NULL, NULL, NULL, NULL) as a
    ON a.object_id = x.object_id
    AND a.index_id = x.index_id

-- 処理後
SELECT * FROM #FRAG_INDEX

DROP TABLE #FRAG_INDEX