Sunday, 16 May 2010

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.