Rebuild and Re-index Database Gradually

  • Article

I had a problem in our live environment - the database was fragmented - badly.

A solution had to be written to defragment the database, whilst providing the least downtime - and the script below was born.

I create a function that will defrag the top X (in this case 20) indexes and created a SQL job that runs it every weekday - and another functions that defrags the top 60 indexes on Friday and Saturday nights. 

I am sure there is room for improvement - so let me know.

SQL SCRIPT:
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DB_Maint_Indexes]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[DB_Maint_Indexes]
GO
CREATE PROCEDURE [dbo].[DB_Maint_Indexes]
AS
BEGIN
-- This will work on MS SQL Server 2005+
-- Based on the websites:
-- http://msdn.microsoft.com/en-us/library/ms189858.aspx
-- http://msdn.microsoft.com/en-us/library/ms189858(v=SQL.105).aspx
-- http://msdn.microsoft.com/en-us/library/ms189858(v=SQL.90).aspx
--> 5% and < = 30% -  ALTER INDEX REORGANIZE 
--> 30% -  ALTER INDEX REBUILD WITH (ONLINE = ON)*

-- update all stats... use at your own discretion
--EXEC sp_updatestats;

DECLARE indexes_cur CURSOR
FOR
-- Change the top to the number of indexes you want to update
SELECT TOP 20
      CASE WHEN avg_fragmentation_in_percent > 30 THEN
      'ALTER INDEX ' + b.name + ' ON [' + s.name + '].[' + t.name 
      + '] REBUILD;'
      ELSE
      'ALTER INDEX ' + b.name + ' ON [' + s.name + '].[' + t.name 
      + '] REORGANIZE;' 
      END AS [sSQL]
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.tables t on t.object_id = b.object_id 
      INNER JOIN sys.schemas s on t.schema_id = s.schema_id 
WHERE NOT b.name IS NULL
      AND avg_fragmentation_in_percent > 5
ORDER BY avg_fragmentation_in_percent DESC;

OPEN indexes_cur;
DECLARE @sSQL varchar(1024);
FETCH NEXT FROM indexes_cur INTO @sSQL;
WHILE (@@FETCH_STATUS = 0)
BEGIN
      -- EXECUTE THE SQL TO REBUILD THE INDEXES
      EXECUTE ('' + @sSQL + ';');
      FETCH NEXT FROM indexes_cur INTO @sSQL   
END;
CLOSE indexes_cur;
DEALLOCATE indexes_cur;
END

GO