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
Richard Mueller edited Revision 1. Comment: Removed tag "SQL Server 2005 2008"
Maheshkumar S Tiwari edited Original. Comment: Added tags