SQL Index Performance

Fragmented indexes can be de-fragmented in two ways depending on the level of fragmentation and size of the tables in terms of rows.

Reorganize Index

The reorganize operation doesn’t take a lot of system resources and can be done while users are accessing the table that the index exists on, that's the reason it is an “online" operation. The reorganize process reorganizes the leaf nodes of the index physically to match it with the logical order, this physical order matching the logical order improves the performance of index scans.

Rebuild Index

Rebuilding an index means dropping an existing index and creating a new one altogether. Any fragmentation that was in the older index is gone with the drop and in the new index the logical order matches the physical order.

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind 
ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 30
ORDER BY indexstats.avg_fragmentation_in_percent DESC

This script reorganizes and rebuilds the index if the fragmentation level is higher the given threshold. You can define the threshold for reorganize as well as for rebuild and script will work accordingly.

@fillfactor - While rebuilding index what would be FILLFACTOR for new index.
@FragmentationThresholdForReorganizeTableLowerLimit - Fragmentation Level lower threshold to check for reorganizing the table, if the fragmentation is higher than this level , it will be considered for reorganize.
@FragmentationThresholdForRebuildTableLowerLimit - Fragmentation Level lower threshold to check for rebuilding the table, if the fragmentation is higher than this level , it will be considered for rebuild.

CREATE PROC REBUILD_INDEX
(
@fillfactor INT = 90,
@FragmentationThresholdForReorganizeTableLowerLimit VARCHAR(10) = '10.0',
@FragmentationThresholdForRebuildTableLowerLimit VARCHAR(10) = '30.0'
)
AS
BEGIN

DECLARE @cmd NVARCHAR(1000)  
DECLARE @Table VARCHAR(255)  
DECLARE @SchemaName VARCHAR(255) 
DECLARE @IndexName VARCHAR(255) 
DECLARE @AvgFragmentationInPercent DECIMAL 

DECLARE @Message VARCHAR(1000) 

SET NOCOUNT ON 

BEGIN TRY 

-- ensure the temporary table does not exist 
IF (SELECT OBJECT_ID('tempdb..#FramentedTableList')) IS NOT NULL 
    DROP TABLE #FramentedTableList; 

SET @Message = 'DATE : ' + CONVERT(VARCHAR, GETDATE()) + ' - Retrieving indexes with high fragmentation from ' + DB_NAME() + ' database.' 
PRINT @Message

SELECT OBJECT_NAME(IPS.OBJECT_ID) AS [TableName], avg_fragmentation_in_percent, SI.name [IndexName],  
schema_name(ST.schema_id) AS [SchemaName], 0 AS IsProcessed INTO #FramentedTableList 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL , NULL) IPS 
JOIN sys.tables ST WITH (nolock) ON IPS.OBJECT_ID = ST.OBJECT_ID 
JOIN sys.indexes SI WITH (nolock) ON IPS.OBJECT_ID = SI.OBJECT_ID AND IPS.index_id = SI.index_id 
WHERE ST.is_ms_shipped = 0 AND SI.name IS NOT NULL 
AND avg_fragmentation_in_percent >= CONVERT(DECIMAL, @FragmentationThresholdForReorganizeTableLowerLimit)  
ORDER BY avg_fragmentation_in_percent DESC 

SET @Message = 'DATE : ' + CONVERT(VARCHAR, GETDATE()) + ' - Retrieved indexes with high fragmentation from ' + DB_NAME() + ' database.' 

PRINT @Message


WHILE EXISTS ( SELECT 1 FROM #FramentedTableList WHERE IsProcessed = 0 ) 
BEGIN 

  SELECT TOP 1 @Table = TableName, @AvgFragmentationInPercent = avg_fragmentation_in_percent,  
  @SchemaName = SchemaName, @IndexName = IndexName 
  FROM #FramentedTableList 
  WHERE IsProcessed = 0 

  --Reorganizing the index 
  IF((@AvgFragmentationInPercent >= @FragmentationThresholdForReorganizeTableLowerLimit) AND (@AvgFragmentationInPercent < @FragmentationThresholdForRebuildTableLowerLimit)) 
  BEGIN 
    SET @Message = 'DATE : ' + CONVERT(VARCHAR, GETDATE()) + ' - Reorganizing Index for [' + @Table + '] which has avg_fragmentation_in_percent = ' + CONVERT(VARCHAR, @AvgFragmentationInPercent) + '.' 
    PRINT @Message
    SET @cmd = 'ALTER INDEX ' + @IndexName + ' ON [' + RTRIM(LTRIM(@SchemaName)) + '].[' + RTRIM(LTRIM(@Table)) + '] REORGANIZE'  
    EXEC (@cmd) 
    PRINT @cmd  
	
    SET @Message = 'DATE : ' + CONVERT(VARCHAR, GETDATE()) + ' - Reorganize Index completed successfully for [' + @Table + '].'  
    PRINT @Message
  END
  
  --Rebuilding the index 
  ELSE IF (@AvgFragmentationInPercent >= @FragmentationThresholdForRebuildTableLowerLimit ) 
  BEGIN 
    SET @Message = 'DATE : ' + CONVERT(VARCHAR, GETDATE()) + ' - Rebuilding Index for [' + @Table + '] which has avg_fragmentation_in_percent = ' + CONVERT(VARCHAR, @AvgFragmentationInPercent) + '.' 
    PRINT @Message
    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 @cmd 
	
    SET @Message = 'DATE : ' + CONVERT(VARCHAR, GETDATE()) + ' - Rebuild Index completed successfully for [' + @Table + '].' 
    PRINT @Message
  END 

  UPDATE #FramentedTableList 
  SET IsProcessed = 1  
  WHERE TableName = @Table 
  AND IndexName = @IndexName 
END 

DROP TABLE #FramentedTableList  

END TRY 

BEGIN CATCH 
  PRINT 'DATE : ' + CONVERT(VARCHAR, GETDATE()) + ' There is some run time exception.' 
  PRINT 'ERROR CODE : ' + CONVERT(VARCHAR, ERROR_NUMBER())  
  PRINT 'ERROR MESSAGE : ' + ERROR_MESSAGE() 
END CATCH 
  
END

Fixing Index Fragmentation in SQL Server