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:
- Check database integrity
- Reorganize Indexes
- Rebuild Indexes
- 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
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
SET @ERRORE = @@ERROR
IF @ERRORE = 0
BEGIN
EXEC sp_updatestats
END
;