Reorganize / Rebuild Indexes in SQL Server

1. Check the fragmentation of an index

SELECT O.name, I.name, S.avg_fragmentation_in_percent
FROM [sys].[dm_db_index_physical_stats](DB_ID('DB_NAME'), NULL, NULL, NULL, NULL) AS S INNER JOIN 
    [sys].[indexes] AS I ON S.object_id = I.object_id AND S.index_id = I.index_id INNER JOIN
    [sys].[objects] AS O ON S.object_id = O.object_id AND I.object_id = O.object_id
WHERE S.avg_fragmentation_in_percent > 5 
ORDER BY S.avg_fragmentation_in_percent DESC;
avg_fragmentation_in_percent value Corrective statement
> 5% and <= 30% ALTER INDEX REORGANIZE
> 30% ALTER INDEX REBUILD

2. Reorganize Index & Update Statistics

Reorganize index will not automatically update the statistics.

ALTER INDEX {INDEX_NAME} ON {OBJECT_NAME} REORGANIZE;
UPDATE STATISTICS {OBJECT_NAME} {INDEX_NAME};

3. Rebuild Index

Rebuild index will automatically update the statistics.

ALTER INDEX {INDEX_NAME} ON {OBJECT_NAME};

4. Everything in one shot

DECLARE @pSqlText varchar(1000)

DECLARE c CURSOR FOR SELECT SqlGenerator.SqlText FROM 
    (SELECT CASE WHEN S.avg_fragmentation_in_percent > 5 AND S.avg_fragmentation_in_percent <= 30 THEN 'ALTER INDEX ' + I.name + ' ON ' + O.name + ' REORGANIZE; UPDATE STATISTICS ' + O.name + ' ' + I.name + ';'
        WHEN S.avg_fragmentation_in_percent > 30 THEN 'ALTER INDEX ' + I.name + ' ON ' + O.name + ' REBUILD;' ELSE '' END AS SqlText
    FROM [sys].[dm_db_index_physical_stats](DB_ID('DB_NAME'), NULL, NULL, NULL, NULL) AS S INNER JOIN 
        [sys].[indexes] AS I ON S.object_id = I.object_id AND S.index_id = I.index_id INNER JOIN
        [sys].[objects] AS O ON S.object_id = O.object_id AND I.object_id = O.object_id
    WHERE I.name IS NOT NULL) SqlGenerator
    WHERE SqlGenerator.SqlText <> ''

OPEN c

FETCH NEXT FROM c INTO @pSqlText

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC (@pSqlText)

    FETCH NEXT FROM c INTO @pSqlText
END

CLOSE c

DEALLOCATE c

References

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s