Database Reference
In-Depth Information
OPEN cList;
FETCH NEXT FROM cList
INTO @DBName, @TableName, @SchemaName, @IndexName, @PctFrag;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @PctFrag BETWEEN 20.0 AND 40.0
BEGIN
SET @Defrag = N'ALTER INDEX ' + @IndexName + ' ON ' + @DBName +
'.' + @SchemaName + '.' + @TableName + ' REORGANIZE';
EXEC sp_executesql @Defrag;
PRINT 'Reorganize index: ' + @DBName + '.' + @SchemaName + '.' +
@TableName + '.' + @IndexName;
END
ELSE
IF @PctFrag > 40.0
BEGIN
SET @Defrag = N'ALTER INDEX ' + @IndexName + ' ON ' +
@DBName + '.' + @SchemaName + '.' + @TableName +
' REBUILD';
EXEC sp_executesql @Defrag;
PRINT 'Rebuild index: ' + @DBName + '.' + @SchemaName +
'.' + @TableName + '.' + @IndexName;
END
FETCH NEXT FROM cList
INTO @DBName, @TableName, @SchemaName, @IndexName, @PctFrag;
END
CLOSE cList;
DEALLOCATE cList;
DROP TABLE #Frag;
GO
After defragging the indexes on the database, rerun the query against
sys.dm_db_index_ physicalstats
for all
five tables. This will let you determine the changes in the index defragmentation, if any (see Figure
25-6
).
