get index fragmentation status of SQL Server 2005 database
here is good trick that you can obtain fragmentation status of each table in any selected database
SELECT c.name as TableName, b.name as IndexName, avg_fragmentation_in_percent as Fragmentation
FROM sys.dm_db_index_physical_stats (DB_ID(), null, NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id, sys.tables as c
where c.object_id=a.object_id
order by avg_fragmentation_in_percent;
Results ;
Fragmentation > 5 AND < 30 : Reorganize Index - ALTER INDEX REORGANIZE
to do
Sp_MSForEachTable @command1 = "PRINT 'ALTER INDEX ALL ON TABLE ? WITH Reorganize'", @command2= "ALTER INDEX ALL ON ? REORGANIZE" for every table on selected database in SQL Server 2005.
Fragmentation > 30 Rebuild Index - ALTER INDEX REBUILD
to do
EXEC Sp_MSForEachTable @command1 = "PRINT 'ALTER INDEX ALL ON TABLE ? REBUILD' ", @command2="ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = 80, ONLINE = OFF,SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF)"
for every table on selected database in SQL Server 2005.
good lucks
SELECT c.name as TableName, b.name as IndexName, avg_fragmentation_in_percent as Fragmentation
FROM sys.dm_db_index_physical_stats (DB_ID(), null, NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id, sys.tables as c
where c.object_id=a.object_id
order by avg_fragmentation_in_percent;
Results ;
Fragmentation > 5 AND < 30 : Reorganize Index - ALTER INDEX REORGANIZE
to do
Sp_MSForEachTable @command1 = "PRINT 'ALTER INDEX ALL ON TABLE ? WITH Reorganize'", @command2= "ALTER INDEX ALL ON ? REORGANIZE" for every table on selected database in SQL Server 2005.
Fragmentation > 30 Rebuild Index - ALTER INDEX REBUILD
to do
EXEC Sp_MSForEachTable @command1 = "PRINT 'ALTER INDEX ALL ON TABLE ? REBUILD' ", @command2="ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = 80, ONLINE = OFF,SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF)"
for every table on selected database in SQL Server 2005.
good lucks
Comments
Post a Comment