SQL Server script to Rebuild/Reorganize fragmented Indexes

When on a database's table frequently running INSERT, UPDATE or DELETE queries than we can see indexes on the particular table gets fragmented and the SQL query may run slower as they may choose non-optimal execution plan. Most of people asking about Does SQL Server rebuild index automatically or any rebuild criteria? But Rebuilding/Reorganize of fragmented Indexes not supported automatically because rebuilding the indexes is expansive operation so be careful to schedule rebuilding via SQL Server Agent.

But hare we are adopting an approach to rebuild or Reorganize only the necessary Indexes instead of all index rebuild, As a general recommendation the index need to rebuild that's get fragment percentage more than 30% and reorganize the index that has fragment percentage in between 10% to 30%.

The below script rebuilding index one by one, so it won't be an expansive operation on production database. 

--- Rebuild/Reorganize Index
CREATE TABLE #fragment_tbl
(
 DatabaseName SYSNAME
 , SchemaName SYSNAME
 , TableName SYSNAME
 , IndexName SYSNAME
 , [Fragmentation%] FLOAT
)
INSERT INTO #fragment_tbl
SELECT
 DB_NAME(DB_ID()) AS DatabaseName
 , ss.name AS SchemaName
 , OBJECT_NAME (s.object_id) AS TableName
 , i.name AS IndexName
 , s.avg_fragmentation_in_percent AS [Fragmentation%]
FROM sys.dm_db_index_physical_stats(db_id(),NULL, NULL, NULL, 'SAMPLED') s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON s.object_id = o.object_id
INNER JOIN sys.schemas ss ON ss.[schema_id] = o.[schema_id]
WHERE s.database_id = DB_ID()
AND i.index_id != 0
AND s.record_count > 0
AND o.is_ms_shipped = 0
DECLARE @RebuildSQL NVARCHAR(MAX)
SET @RebuildSQL = ''
SELECT
 @RebuildSQL = @RebuildSQL +
CASE
 WHEN [Fragmentation%] > 30
   THEN CHAR(10) + 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON '
      + QUOTENAME(SchemaName) + '.'
      + QUOTENAME(TableName) + ' REBUILD;'
 WHEN [Fragmentation%] > 10
    THEN CHAR(10) + 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON '
    + QUOTENAME(SchemaName) + '.'
    + QUOTENAME(TableName) + ' REORGANIZE;'
END
FROM #fragment_tbl
WHERE [Fragmentation%] > 10
DECLARE @StartOffset INT
DECLARE @Length INT
SET @StartOffset = 0
SET @Length = 4000
WHILE (@StartOffset < LEN(@RebuildSQL))
BEGIN
 PRINT SUBSTRING(@RebuildSQL, @StartOffset, @Length)
 SET @StartOffset = @StartOffset + @Length
END
PRINT SUBSTRING(@RebuildSQL, @StartOffset, @Length)
EXECUTE sp_executesql @RebuildSQL
DROP TABLE #fragment_tbl

The scripts will work like a charm,  You can create an stored procedure and can call the stored procedure via SQL Server agent or via windows task scheduler.

That's it

 

Comments are closed