When to Reorganize/Rebuild Index

I had posted about using SQL Server 2005 DMV in the past. I had been thinking about adding some more details for a long time. Finally here it is..

When the data in the underlying tables change, indexes become fragmented (ie. logical ordering of index does not match with the physical ordering of the underlying table). When indexes get fragmented, the performance of the queries will suffer.

So most of the DBAs add to rebuild/reorganize the indexes in their maintenance jobs. Here are the differences between reorganizing and rebuilding the indexes.

Reorganize	Rebuild	Rebuild online in SQL Server 2005
It causes the data in the outermost data pages to reorder and will compact the index	Drops the desired index and creates a new index.	The database engine creates a new index and it is needs additional disk space to accommodate two concurrent indexes. During the process the new index is a write-only index and modifications to original index and applied to the new one. Once the process it done, it drops the original index.
Takes less system resouces and it is an online operation	Since the index is removed and recreated, it is an offline operation	The database engine will still have the original index available to the users while it building a new index. It uses Row Versioning to maintain the transactional consistency and integrity.

I personally look at the page count along with the avg_fragmentation_in_percent because high fragmentation on big indexes will have higher performance impact.

Even if you rebuild all the indexes, you may notice fragmentation for some tables will remain high. This is because if there are less than 1000 pages affected then the fragmentation will not be affected. For smaller tables, SQL server will prefer to use a table scan instead of an index.

Here is the script that I use

--Script to Reorganize/Rebuild indexes

DECLARE @cmd VARCHAR(1000)

DECLARE @Table VARCHAR(255)

DECLARE @SchemaName VARCHAR(255)

DECLARE @IndexName VARCHAR(255)

DECLARE @AvgFragmentationInPercent DECIMAL

DECLARE @fillfactor INT

DECLARE @ThresholdLowerLimit VARCHAR(10)

DECLARE @ThresholdUpperLimit VARCHAR(10)

DECLARE @PageCount INT

/* You can specify your value for reorganize and rebuild indexes

If avg_fragmentation_in_percent >= 5 and <30> 1000 Then Reorganize the Index

If avg_fragmentation_in_percent >= 30 AND Page_Count > 1000 Then Rebuild the Index

*/

SET @fillfactor = 90 --you can specify value for fill factor

SET @ThresholdLowerLimit= '5.0' -- this value is percentage

SET @ThresholdUpperLimit = '30.0' -- this value is percentage

SET @PageCount = 1000 --if pages are less then fragmentation would potentially remain same; This is not a magical cut off point but a start

-- ensure the temporary table does not exist

IF (SELECT OBJECT_ID('tempdb..#IndexList')) IS NOT NULL

DROP TABLE #IndexList;

--Select all the indexes that have >= 5% percent fragmentation and PageCount > 1000

SELECT OBJECT_NAME(S.OBJECT_ID) AS [TableName],schema_name(T.schema_id) AS [SchemaName],

OBJECT_NAME(S.object_id) [IndexName],S.page_count, S.avg_fragmentation_in_percent, 0 AS IsDefraged INTO #IndexList

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL , NULL) S

JOIN sys.tables T WITH (nolock) ON S.OBJECT_ID = T.OBJECT_ID

WHERE T.is_ms_shipped = 0 --We only need user tables

AND S.avg_fragmentation_in_percent >= CONVERT(DECIMAL, @ThresholdLowerLimit)

AND S.page_count > @PageCount

ORDER BY S.page_count DESC

WHILE EXISTS ( SELECT 1 FROM #IndexList WHERE IsDefraged = 0 )

BEGIN

SELECT TOP 1 @Table = TableName, @AvgFragmentationInPercent = avg_fragmentation_in_percent,

@SchemaName = SchemaName, @IndexName = IndexName

FROM #IndexList

WHERE IsDefraged = 0

--Reorganizing the index if avg_fragmentation_in_percent > 5 and < style="">

IF((@AvgFragmentationInPercent >= @ThresholdLowerLimit) AND (@AvgFragmentationInPercent < @ThresholdUpperLimit))

BEGIN

SET @cmd = 'ALTER INDEX ' + @IndexName + ' ON [' + RTRIM(LTRIM(@SchemaName)) + '].[' + RTRIM(LTRIM(@Table)) + '] REORGANIZE'

EXEC (@cmd)

PRINT N'Reorganize Index completed successfully for [' + @Table + ']' + CONVERT(VARCHAR, GETDATE())

END

--Rebuilding the index if avg_fragmentation_in_percent > 30

ELSE IF (@AvgFragmentationInPercent >= @ThresholdUpperLimit )

BEGIN

SET @cmd = 'ALTER INDEX ' + @IndexName + ' ON [' + RTRIM(LTRIM(@SchemaName)) + '].[' + RTRIM(LTRIM(@Table)) + '] REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ', STATISTICS_NORECOMPUTE = OFF)'

EXEC (@cmd)

PRINT N'Rebuild Index completed successfully for [' + @Table + ']' + CONVERT(VARCHAR, GETDATE())

END

--Update the temp table

UPDATE #IndexList

SET IsDefraged = 1

WHERE TableName = @Table

AND IndexName = @IndexName

END

DROP TABLE #IndexList