SQL Server/INDEX再構築
をテンプレートにして作成
Search in
this wiki
and
or
[
トップ
] [
新規
|
一覧
|
単語検索
|
最終更新
|
ヘルプ
]
開始行:
*** 指定したDB内で、30%以上断片化しているINDEXを再構築す...
SELECT a.object_id
,c.name as tablename
,a.index_id
,b.name as indexname
,cast(avg_fragmentation_in_percent as decimal(5,2)...
,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 @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, ...
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('ALTER INDEX ' + @indexname + ' ON ' + @tablen...
FETCH NEXT FROM fragmented_index_cursor
INTO @object_id, @tablename, @index_id, @indexna...
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
終了行:
*** 指定したDB内で、30%以上断片化しているINDEXを再構築す...
SELECT a.object_id
,c.name as tablename
,a.index_id
,b.name as indexname
,cast(avg_fragmentation_in_percent as decimal(5,2)...
,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 @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, ...
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('ALTER INDEX ' + @indexname + ' ON ' + @tablen...
FETCH NEXT FROM fragmented_index_cursor
INTO @object_id, @tablename, @index_id, @indexna...
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
ページ名:
![[Return to Top Page]](image//logo.png "[Return to Top Page]"){kind=logo}
