SQL Server Procedure for Index maintenance

Index maintenance

For better query result its always good to have index statistics up to date. We normally look fragmentation percentage on indices which can be due to batch load or batch delete etc. We might observe fragmentation due to fill factors also.

If we go by the MSDN guideline then we normally do reorganize of the indices if the fragmentation is less than 35% and do rebuild of indices if the fragmentation is greater than 35%.

Below procedure accepts DB name as parameter and does the job for all the tables.

Stored procedure for index rebuild and reorganize

CREATE PROCEDURE [dbo].[SP_MainTainIndices]
(
@Database NVARCHAR(4000) = N'TestDB'
)
AS
BEGIN
DECLARE @ReorgIndex TABLE (ID INT IDENTITY(1,1),indexID INT,Table_Name NVARCHAR(4000), Index_Name NVARCHAR(4000))
DECLARE @RebuildIndex TABLE (ID INT IDENTITY(1,1),indexID INT,Table_Name NVARCHAR(4000), Index_Name NVARCHAR(4000))
DECLARE @id INT =1 , @idMax INT ;
DECLARE @sql NVARCHAR(MAX) = ''

INSERT INTO @ReorgIndex (indexID,Table_Name,Index_Name)
SELECT a.index_id,('['+D.name + '].['+ c.name+']') AS Table_Name, b.name AS Index_Name
FROM sys.dm_db_index_physical_stats (DB_ID(@Database), null, NULL, NULL, NULL) AS a
    JOIN sys.indexes (nolock) AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
       JOIN sys.tables (nolock) AS C ON a.object_id = C.object_id
       INNER JOIN sys.schemas (nolock) AS D ON C.schema_id = D.schema_id
       WHERE avg_fragmentation_in_percent > 0 AND avg_fragmentation_in_percent <= 35
       AND b.Name IS NOT NULL
       ORDER BY D.name,C.name,avg_fragmentation_in_percent;

INSERT INTO @RebuildIndex (indexID,Table_Name,Index_Name)
SELECT a.index_id,('['+D.name + '].['+ c.name+']') AS Table_Name, b.name AS Index_Name
FROM sys.dm_db_index_physical_stats (DB_ID(@Database), null, NULL, NULL, NULL) AS a
    JOIN sys.indexes (nolock) AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
       JOIN sys.tables (nolock) AS C ON a.object_id = C.object_id
       INNER JOIN sys.schemas (nolock) AS D ON C.schema_id = D.schema_id
       WHERE avg_fragmentation_in_percent > 35
       AND b.name IS NOT NULL
       ORDER BY D.name,C.name,avg_fragmentation_in_percent;

SELECT @idMax = MAX(ID) FROM @ReorgIndex
WHILE (@id <= @idMax)
BEGIN

SELECT @sql =  (' ALTER INDEX ['+  Index_Name  + '] ON ' + Table_Name +' REORGANIZE ')
FROM @ReorgIndex WHERE ID = @id
EXEC (@sql)
SET @sql = ''
SET @id = @id + 1

END



SET @id = 1
SELECT @idMax = MAX(ID) FROM @RebuildIndex

WHILE (@id <= @idMax)
BEGIN

SELECT @sql =  (' ALTER INDEX ['+  Index_Name  + '] ON ' + Table_Name +' REBUILD ')
FROM @RebuildIndex WHERE ID = @id
EXEC (@sql)
SET @sql = ''
SET @id = @id + 1

END



END