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

declare @databaseName nvarchar(255)
set @databaseName = 'LOKMSTIDX'

EXEC ('USE ' + @databaseName)

-- 処理前
SELECT c.name as tablename
      ,b.name as indexname
      ,avg_fragmentation_in_percent
 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
 INNER JOIN sys.objects AS c
 ON a.object_id = c.object_id
 AND is_ms_shipped = 0
GO


DECLARE @indexname varchar(255)
       ,@tablename varchar(255)
DECLARE fragmented_index_cursor CURSOR FOR 
SELECT b.name as indexname
      ,c.name as tablename
 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
 INNER JOIN sys.objects AS c
 ON a.object_id = c.object_id
 AND is_ms_shipped = 0
 WHERE avg_fragmentation_in_percent > 30

OPEN fragmented_index_cursor

FETCH NEXT FROM fragmented_index_cursor
    INTO @indexname, @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT '@indexname : ' + @indexname + ' @tablename : ' + @tablename
    
    EXEC ('ALTER INDEX ' + @indexname
       + ' ON ' + @databaseName + '..' + @tablename + ' REBUILD')
    FETCH NEXT FROM fragmented_index_cursor
        INTO @indexname, @tablename
END

CLOSE fragmented_index_cursor
DEALLOCATE fragmented_index_cursor
GO

-- 処理後
SELECT c.name as tablename
      ,b.name as indexname
      ,avg_fragmentation_in_percent
 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
 INNER JOIN sys.objects AS c
 ON a.object_id = c.object_id
 AND is_ms_shipped = 0
GO
トップ   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS