SQL Server/INDEX再構築の変更点

追加された行はこの色です。
削除された行はこの色です。
SQL Server/INDEX再構築へ行く。
SQL Server/INDEX再構築の差分を削除

*** 指定したDB内で、30%以上断片化しているINDEXを再構築するクエリ [#qeb99ba7]
 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
  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

SQL Server/INDEX再構築 の変更点

SQL Server/INDEX再構築の変更点