Reorganize and Rebuild Indexes and Update Statistics

Introduction

In this tip, I want share my solution about a common issue: indexed maintenance. That is an easy stored procedure to add to your database to keep a good query execution performance.

Implementation

To implement this procedure on your database, it's just necessary to execute the creation statement. Personally, I suggest to execute this procedure one or two times for a week according to your database size and complexity.

Using the Code

Stored Procedure Explanation

This stored procedure can be divided into 4 parts:

1. Check database integrity
2. Reorganize Indexes
3. Rebuild Indexes
4. Updates Statistics

1. Database Integrity

The first operation is check your database's integrity, if this step returns an error, the system can't proceed to indexes maintenance operations. This step is completed with executing this statement:

DBCC CHECKDB WITH NO_INFOMSGS

2. Indetify Index to Reorganize

The code to complete this step is based on function sys.dm_db_index_physical_stats. sys.dm_db_index_physical_stats return size and fragmentation information for the data and indexes of the specified database. Personally, I chose to reorganize all indexes with an average fragmentation between 10 and 35 percent.

SELECT  @Reorganize = @Reorganize + ' ' + _
'ALTER INDEX [' + i.[name] + '] ON [dbo].[' + t.[name] + ']
        REORGANIZE WITH ( LOB_COMPACTION = ON )'
FROM    sys.dm_db_index_physical_stats
          (DB_ID(@DBName ), NULL, NULL, NULL , 'DETAILED') fi
        inner join sys.tables t
         on fi.[object_id] = t.[object_id]
        inner join sys.indexes i
         on fi.[object_id] = i.[object_id] and
            fi.index_id = i.index_id
where t.[name] is not null and i.[name] is not null
        and avg_fragmentation_in_percent > 10
        and avg_fragmentation_in_percent <=35
order by t.[name]

3. Identify Index to Rebuild

In the same way as the previous step, this statement identifies all indexes with an average fragmentation greater than 35 percent. For these type of indexes, a rebuild operation is necessary.

SELECT  @Rebild = @Rebild + ' ' + _
'ALTER INDEX [' + i.[name] + '] ON [dbo].[' + t.[name] + ']
        REBUILD WITH (ONLINE = OFF )'
FROM    sys.dm_db_index_physical_stats
          (DB_ID(@DBName ), NULL, NULL, NULL , 'DETAILED') fi
        inner join sys.tables t
         on fi.[object_id] = t.[object_id]
        inner join sys.indexes i
         on fi.[object_id] = i.[object_id] and
            fi.index_id = i.index_id
where avg_fragmentation_in_percent > 35 and t.[name] is not null and i.[name] is not null
order by t.[name]

4. Updated Index Statistics

The last step is to update the database statistics. To update indexes, just execute this statement:

EXEC sp_updatestats

Procedure Code

CREATE PROCEDURE [dbo].[usp_Inxed_Statistics_Maintenance]
@DBName AS NVARCHAR(128)
AS

DECLARE @ERRORE INT
--Check Database Error
DBCC CHECKDB WITH NO_INFOMSGS
SET @ERRORE = @@ERROR
IF @ERRORE = 0 
BEGIN
	DECLARE @RC INT
	DECLARE @Messaggio VARCHAR(MAX)
	DECLARE @Rebild AS VARCHAR(MAX)
	DECLARE @Reorganize AS VARCHAR(MAX)

	SET @Reorganize = ''
	SET @Rebild = ''

	SELECT  @Reorganize = @Reorganize + ' ' + 
	'ALTER INDEX [' + i.[name] + '] ON [dbo].[' + t.[name] + '] 
			REORGANIZE WITH ( LOB_COMPACTION = ON )'
	FROM	sys.dm_db_index_physical_stats
			  (DB_ID(@DBName ), NULL, NULL, NULL , 'DETAILED') fi
			inner join sys.tables t
			 on fi.[object_id] = t.[object_id]
			inner join sys.indexes i
			 on fi.[object_id] = i.[object_id] and
				fi.index_id = i.index_id
	where t.[name] is not null and i.[name] is not null 
			and avg_fragmentation_in_percent > 10   
			and avg_fragmentation_in_percent <=35
	order by t.[name]

	EXEC (@Reorganize)

	SELECT  @Rebild = @Rebild + ' ' + 
	'ALTER INDEX [' + i.[name] + '] ON [dbo].[' + t.[name] + '] 
			REBUILD WITH (ONLINE = OFF )'
	FROM	sys.dm_db_index_physical_stats
			  (DB_ID(@DBName ), NULL, NULL, NULL , 'DETAILED') fi
			inner join sys.tables t
			 on fi.[object_id] = t.[object_id]
			inner join sys.indexes i
			 on fi.[object_id] = i.[object_id] and
				fi.index_id = i.index_id
	where avg_fragmentation_in_percent > 35 and t.[name] is not null and i.[name] is not null
	order by t.[name]

	EXEC (@Rebild)
END

-- if there are not error update statistics
SET @ERRORE = @@ERROR
IF @ERRORE = 0
	BEGIN
		EXEC sp_updatestats
	END
	
;