Sunday, 16 May 2010

Defragmenting SQL Server 2008+ using ALTER INDEX

In SQL 2009 DBCC INDEXDEFRAG will no longer work as Microsoft have removed the command. You need to user ALTER INDEX instead:

CREATE PROCEDURE sjh_defragment_indexes_2008
(
    @maxfrag FLOAT
)
AS
 
/* Simon Hughes 15 May 2010
 
For SQL 2008+
This stored procedure checks index fragmentation in a database and defragments
indexes whose fragmentation fall above a specified threshold: @maxfrag
 
Must be run in the database to be defragmented.
 
exec sjh_defragment_indexes_2008 5
*/
 
SET NOCOUNT ON
SET XACT_ABORT ON
 
-- Write start time for information purposes
PRINT 'Started defragmenting at: ' + CONVERT(VARCHAR, GETDATE())
 
-- Check fragmentation
DECLARE @execstr NVARCHAR(255)
DECLARE lcsr CURSOR local fast_forward READ_ONLY FOR
    SELECT  'ALTER INDEX [' + i.name + '] ON [' + so.name + '] REORGANIZE;' AS execstr
    FROM    sys.sysobjects AS so
            INNER JOIN sys.dm_db_index_physical_stats(DB_ID('bybox'), NULL, NULL, NULL, 'LIMITED') AS phystat
            INNER JOIN sys.indexes AS i
                ON i.object_id = phystat.object_id
                   AND i.index_id = phystat.index_id
                ON so.name = OBJECT_NAME(i.object_id)
    WHERE   (i.name IS NOT NULL)
            AND (so.type = 'U')
            AND (phystat.avg_fragmentation_in_percent > @maxfrag)
 
OPEN lcsr
FETCH NEXT FROM lcsr INTO @execstr
 
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sp_executesql @execstr
    FETCH NEXT FROM lcsr INTO @execstr
END
 
CLOSE lcsr
DEALLOCATE lcsr
 
-- Report on finish time for information purposes
PRINT 'Finished defragmenting at: ' + CONVERT(VARCHAR, GETDATE())
GO

Download code here.