Defragmenting SQL Server 2005 and 2008 using dynamic management views

You can read all about SQL defragmentation here by Satya Jayanty. The bottom line - defragmentation is bad. It causes high CPU load and poor SQL performance. Shrinking your database causes massive index fragmentation. You can read about that here, therefore you should always turn off auto-shrink (I also have a script for that here).

For SQL 2000 you must use DBCC SHOWCONTIG. You can use the SQL given here.

However, for SQL Server 2005 onwards, there is a better way using the dynamic management view sys.dm_db_index_physical_stats introduced in SQL 2005.

Note: In SQL 2009 this proc is not going to work because Microsoft are going to remove DBCC INDEXDEFRAG from the next version of Microsoft SQL Server (read 2009), you need to user ALTER INDEX instead, as shown in my next blog entry.

SELECT  OBJECT_NAME(i.object_id) AS TableName,

        i.name AS TableIndexName,

        phystat.avg_fragmentation_in_percent,

        phystat.fragment_count

FROM    sys.dm_db_index_physical_stats(DB_ID('your_database_name'), NULL, NULL, NULL, 'LIMITED') phystat

        INNER JOIN sys.indexes i

            ON i.object_id = phystat.object_id AND i.index_id = phystat.index_id

WHERE   phystat.avg_fragmentation_in_percent > 10

ORDER BY phystat.avg_fragmentation_in_percent DESC

Building that into a stored proc to execute every so often to defrag gives us:

CREATE PROCEDURE sjh_defragment_indexes

(

    @maxfrag FLOAT

)

AS

/* Simon Hughes 15 May 2010

For SQL 2005+ only

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.

Please change 'your_database_name' to be the name of your database 

exec sjh_defragment_indexes 5.0

*/

SET NOCOUNT ON

SET XACT_ABORT ON

-- Check this is not being run in a system database

IF DB_NAME() IN ('master', 'msdb', 'model', 'tempdb') 

BEGIN

    PRINT 'This procedure should not be run in system databases.'

    RETURN

END

-- Check fragmentation

SELECT  OBJECT_NAME(i.object_id) AS TableName,

        i.name AS TableIndexName,

        phystat.avg_fragmentation_in_percent,

        phystat.fragment_count,

        'DBCC INDEXDEFRAG (0, [' + OBJECT_NAME(i.object_id) + '], ' + i.name + ') WITH NO_INFOMSGS' AS execstr

INTO    [#frag]

FROM    sys.dm_db_index_physical_stats(DB_ID('your_database_name'), NULL, NULL, NULL, 'LIMITED') phystat

        INNER JOIN sys.indexes i

            ON i.object_id = phystat.object_id

               AND i.index_id = phystat.index_id

WHERE   i.name IS NOT NULL

        AND phystat.avg_fragmentation_in_percent > @maxfrag

ORDER BY phystat.avg_fragmentation_in_percent DESC

SELECT * FROM [#frag]

-- Write start time for information purposes

PRINT 'Started defragmenting at: ' + CONVERT(VARCHAR, GETDATE())

-- Begin defrag

DECLARE @execstr NVARCHAR(1024)

DECLARE cc CURSOR FAST_FORWARD FOR

    SELECT execstr FROM [#frag]

OPEN cc

FETCH NEXT FROM cc INTO @execstr

WHILE @@FETCH_STATUS = 0 

BEGIN

    EXEC sp_executesql @execstr

    FETCH NEXT FROM cc INTO @execstr

END

-- Close and deallocate the cursor

CLOSE cc

DEALLOCATE cc

-- Report on finish time for information purposes

PRINT 'Finished defragmenting at: ' + CONVERT(VARCHAR, GETDATE())

-- Delete the temporary table

DROP TABLE [#frag]

GO

Download code here.