*** 指定したDB内で、30%以上断片化しているINDEXを再構築するクエリ [#qeb99ba7]
 declare @databaseName nvarchar(255)
 set @databaseName = 'LOKMSTIDX'
 
 EXEC ('USE ' + @databaseName)
 
 -- 処理前
 SELECT c.name as tablename
 SELECT a.object_id
       ,c.name as tablename
       ,a.index_id
       ,b.name as indexname
       ,avg_fragmentation_in_percent
       ,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 @indexname varchar(255)
 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 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
  SELECT * FROM #FRAG_INDEX
 
 OPEN fragmented_index_cursor
 
 FETCH NEXT FROM fragmented_index_cursor
     INTO @indexname, @tablename
     INTO @object_id, @tablename, @index_id, @indexname, @before, @after
 WHILE @@FETCH_STATUS = 0
 BEGIN
     PRINT '@indexname : ' + @indexname + ' @tablename : ' + @tablename
     
     EXEC ('ALTER INDEX ' + @indexname
        + ' ON ' + @databaseName + '..' + @tablename + ' REBUILD')
     EXEC ('ALTER INDEX ' + @indexname + ' ON ' + @tablename + ' REBUILD')
 
     FETCH NEXT FROM fragmented_index_cursor
         INTO @indexname, @tablename
         INTO @object_id, @tablename, @index_id, @indexname, @before, @after
 END
 
 CLOSE fragmented_index_cursor
 DEALLOCATE fragmented_index_cursor
 GO
 
 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 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
 SELECT * FROM #FRAG_INDEX
 
 DROP TABLE #FRAG_INDEX
トップ   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS