Re-Index Your Indexed SQL Server Records

Hi, today I want to speak about the re-index in the SQL server. Indexes are thrown for fast access in SQL Server but sometimes they lose their characteristics as the data changes over time and needs maintenance.

Firstly you should know indexing everything is not a good idea and secondly, missing indexes are a burden, we don’t want to carry it. How should we use the index? and which type of index shall we use? I will speak about that in another story.

There are tables for checking indexes are in SQL Server, I used these tables to get indexes and fragmentations. If fragmentation is bigger than 5, you should REORGONISE If bigger than 30 you should REBUILD this index.

I create a script for this. You just give the name of the database, let it do the job

CREATE PROC [edb].[INDEX_MAINTENANCE] @DBName VARCHAR(100)
AS BEGIN
SET NOCOUNT ON;
DECLARE
@OBJECT_ID INT,
@INDEX_NAME sysname,
@SCHEMA_NAME sysname,
@OBJECT_NAME sysname,
@AVG_FRAG float,
@command varchar(8000),
@RebuildCount int,
@ReOrganizeCount int
CREATE TABLE #tempIM (
[ID] [INT] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[INDEX_NAME] sysname NULL,
[OBJECT_ID] INT NULL,
[SCHEMA_NAME] sysname NULL,
[OBJECT_NAME] sysname NULL,
[AVG_FRAG] float
)
SELECT @RebuildCount=0,@ReOrganizeCount=0
 --Get Fragentation values
SELECT @command = ‘Use ‘ + @DBName + ‘;
INSERT INTO #tempIM (OBJECT_ID, INDEX_NAME, SCHEMA_NAME, OBJECT_NAME, AVG_FRAG)
SELECT
ps.object_id,
i.name as IndexName,
OBJECT_SCHEMA_NAME(ps.object_id) as ObjectSchemaName,
OBJECT_NAME (ps.object_id) as ObjectName,
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(‘’’+@DBName+’’’), NULL, NULL , NULL,’’LIMITED’’) ps
INNER JOIN sys.indexes i ON i.object_id=ps.object_id and i.index_id=ps.index_id
WHERE avg_fragmentation_in_percent > 5 AND ps.index_id > 0
ORDER BY avg_fragmentation_in_percent desc ‘;
exec(@command)
DECLARE c CURSOR FAST_FORWARD FOR
SELECT OBJECT_ID,INDEX_NAME, SCHEMA_NAME, OBJECT_NAME, AVG_FRAG
FROM #tempIM
OPEN c
FETCH NEXT FROM c INTO @OBJECT_ID, @INDEX_NAME, @SCHEMA_NAME, @OBJECT_NAME, @AVG_FRAG
WHILE @@FETCH_STATUS = 0
BEGIN
--Reorganize or Rebuild
IF @AVG_FRAG>30 
BEGIN
SELECT @command = ‘Use ‘ + @DBName + ‘; ALTER INDEX [‘ + @INDEX_NAME +’] ON [‘+ @SCHEMA_NAME + ‘].[‘ + @OBJECT_NAME + ‘] REBUILD ‘;
SET @RebuildCount = @RebuildCount+1
END 
ELSE BEGIN
SELECT @command = ‘Use ‘ + @DBName + ‘; ALTER INDEX [‘ + @INDEX_NAME +’] ON [‘+ @SCHEMA_NAME + ‘].[‘ + @OBJECT_NAME + ‘] REORGANIZE ‘;
SET @ReOrganizeCount = @ReOrganizeCount+1
END
BEGIN TRY
select @command
EXEC (@command);
END TRY
BEGIN CATCH
END CATCH
FETCH NEXT FROM c INTO @OBJECT_ID, @INDEX_NAME, @SCHEMA_NAME, @OBJECT_NAME, @AVG_FRAG
END
CLOSE c
DEALLOCATE c
DROP TABLE #tempIM
SELECT cast(@RebuildCount as varchar(5))+’ index Rebuilded,’+cast(@ReOrganizeCount as varchar(5))+’ index Reorganized.’ as Result
END

NOTICE: After this script, if there is still fragmentation bigger than 5, it means this table is not big enough

You can check your fragmentation rate with this query

SELECT
ps.object_id,
i.name as IndexName,
OBJECT_SCHEMA_NAME(ps.object_id) as ObjectSchemaName,
OBJECT_NAME (ps.object_id) as ObjectName,
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID('yourdbname'), NULL, NULL , NULL, ‘LIMITED’) ps
INNER JOIN sys.indexes i ON i.object_id=ps.object_id and i.index_id=ps.index_id
WHERE avg_fragmentation_in_percent > 5 AND ps.index_id > 0
ORDER BY avg_fragmentation_in_percent desc