- 追加された行はこの色です。
- 削除された行はこの色です。
*** 指定した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