Page History: Reducing SQL Server Index Fragmentation
Compare Page Revisions
Page Revision: 2010/06/24 00:52
To view info
if you can understand numbers you can check current defragmentation, think about it and then decide, make 2nd step or not
DECLARE @TableName sysname
DECLARE cur_showfragmentation CURSOR FOR
SELECT table_name FROM information_schema.tables WHERE table_type = 'base table'
OPEN cur_showfragmentation
FETCH NEXT FROM cur_showfragmentation INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT 'Show fragmentation for the ' + @TableName + ' table'
SET @TableName = '[' + @TableName + ']'
DBCC SHOWCONTIG (@TableName)
FETCH NEXT FROM cur_showfragmentation INTO @TableName
END
CLOSE cur_showfragmentation
DEALLOCATE cur_showfragmentation
To defragment
don't forget to change db name
use db, and in the middle there is line
IF @indid <> 255 DBCC INDEXDEFRAG ([DBNAME], @TableName, @indid
)
DECLARE @TableName sysname
DECLARE @indid int
DECLARE cur_tblfetch CURSOR FOR
SELECT table_name FROM information_schema.tables WHERE table_type = 'base table'
OPEN cur_tblfetch
FETCH NEXT FROM cur_tblfetch INTO @TableName
SET @TableName='[' + @TableName + ']'
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE cur_indfetch CURSOR FOR
SELECT indid FROM sysindexes WHERE id = OBJECT_ID (@TableName) and keycnt > 0
OPEN cur_indfetch
FETCH NEXT FROM cur_indfetch INTO @indid
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Derfagmenting index_id = ' + convert(char(3), @indid) + 'of the '
+ rtrim(@TableName) + ' table'
IF @indid <> 255 DBCC INDEXDEFRAG ([
DBNAME], @TableName, @indid)
FETCH NEXT FROM cur_indfetch INTO @indid
END
CLOSE cur_indfetch
DEALLOCATE cur_indfetch
FETCH NEXT FROM cur_tblfetch INTO @TableName
SET @TableName='[' + @TableName + ']'
END
CLOSE cur_tblfetch
DEALLOCATE cur_tblfetch