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