You are here:Home » tsql » script to Rebuild / Reorganize all index for all databases as per fragmentation ratio

script to Rebuild / Reorganize all index for all databases as per fragmentation ratio

AS a DBA activity, index maintenance is a main activity and here I am sharing a script to rebuild or reorganize indexes as periodically. The way is here to first collect index fragmentation statistics in one table and process further for rebuilding or reorganize. So follow the script below,

1. Create table to collect index fragmentation data
CREATE TABLE [dba].[IndexFragDetails](
 [SID] [int] IDENTITY(1,1) NOT NULL,
 [TableName] [varchar](500) NULL,
 [IndexName] [varchar](500) NULL,
 [IndexType] [varchar](100) NULL,
 [FragPercent] [int] NULL,
 [PageCount] [int] NULL,
 [CheckedDate] [datetime] NULL,
 [LastRebuiltDate] [datetime] NULL,
 [TimeTaken_Min] [int] NULL,
 [Result] [int] NOT NULL
) ON [PRIMARY]

GO

ALTER TABLE [dba].[IndexFragDetails] ADD  DEFAULT (getdate()) FOR [CheckedDate]
ALTER TABLE [dba].[IndexFragDetails] ADD  DEFAULT ((0)) FOR [Result]
GO
2. Capture Index fragmentation statistics
DECLARE @CurrentDate datetime
SET @CurrentDate = GETDATE()

-- Fetching top 100 indexes
;With IndexFragReBuild
As
(
SELECT TOP 100
    [object_id] AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag, 
    page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'LIMITED')
 WHERE avg_fragmentation_in_percent > 5.0  -- Allow limited fragmentation
 AND index_id > 0 -- Ignore heaps
 AND page_count > 1000 -- Ignore small tables
 ORDER BY avg_fragmentation_in_percent desc
)

-- Inserting in detail table
INSERT INTO dba.IndexFragDetails
(
TableName,
IndexName,
IndexType,
FragPercent,
[PageCount],
CheckedDate
)
SELECT 
 QUOTENAME(Schema_name(t.schema_id))+'.'+QUOTENAME(object_name(IFD.objectid)) as TableName,
 QUOTENAME(i.name) as IndexName, 
 I.Type_Desc,
 IFD.Frag, 
 IFD.Page_Count,
 @CurrentDate
FROM IndexFragReBuild IFD
INNER JOIN sys.indexes i
 ON i.object_id = IFD.objectid AND i.index_id = IFD.indexid
INNER JOIN  sys.tables  t
 ON t.object_id = IFD.objectid

-- Removing non-clustered indexes where clustered index exists for same table 
DELETE a 
FROM dba.IndexFragDetails a
 WHERE a.CheckedDate = @CurrentDate
 AND a.IndexType = 'NONCLUSTERED'
 AND EXISTS 
(
 SELECT 
  b.tablename 
 FROM dba.IndexFragDetails b
  WHERE b.CheckedDate = @CurrentDate
  AND a.tablename = b.tablename
  AND b.IndexType = 'CLUSTERED'
)
GO
3. Rebuilding or Reorganize indexes
SET QUOTED_IDENTIFIER ON

DECLARE @MaxCheckedDate datetime, 
  @ErrorMessage varchar(4000)

SET @MaxCheckedDate = (SELECT MAX(c.CheckedDate) FROM dba.IndexFragDetails c WITH (NOLOCK))

CREATE TABLE #IndexFragRebuild
(
 Seq int identity (1,1),
 TableName varchar(100),
 IndexName varchar(100),
 IndexType varchar(100),
 FragPercent int,
 PageCount bigint,
 LastRebuiltDate varchar(20) default 'NA'
)

INSERT INTO #IndexFragRebuild
(
 TableName,
 IndexName,
 IndexType,
 FragPercent,
 PageCount,
 LastRebuiltDate
)

-- Fetching indexes to be rebuild or reorganize from details table
SELECT 
 a.TableName,
 a.IndexName,
 a.IndexType,
 a.FragPercent,
 a.PageCount,
 ISNULL((select CONVERT(varchar(20),MAX(b.LastRebuiltDate),101) FROM dba.IndexFragDetails b  WITH (NOLOCK) 
   WHERE b.TableName=a.TableName AND b.IndexName=a.IndexName ),'NA')
FROM dba.IndexFragDetails a WITH (NOLOCK)
 WHERE 
 a.LastRebuiltDate IS NULL
 AND a.CheckedDate = @MaxCheckedDate
 AND a.Result = 0
 Order by a.FragPercent DESC

DECLARE @Subject VARCHAR (1000)  
DECLARE @Body VARCHAR(MAX) 
DECLARE @hpart VARCHAR(400)  
Declare @ini int,@cnt int, 
  @sql varchar(500), 
  @tableName varchar(500), 
  @indexName varchar(500),
  @Startdate datetime, 
  @EndDate datetime,
  @SDate datetime, 
  @EDate datetime,
  @FragPercent int


SET @Subject = 'List of the indexes to be Rebuild\Reorganize'  
SET @hpart='TableName
IndexName
IndexType
FragPercent
PageCount
LastRebuiltDate
' SET @Body = CAST ( ( SELECT td = TableName, '', td = IndexName, '', td = IndexType, '', td = FragPercent, '', td = PageCount, '', td = LastRebuiltDate, '' from #IndexFragRebuild FOR XML PATH('tr'), TYPE ) AS VARCHAR(MAX) ) + N'' SET @Body = ''+@hpart+@Body+'
' + ' ' -- Sending an email of list of indexes to be rebuild\reorganize EXEC msdb.dbo.sp_send_dbmail @recipients = 'prajapatipareshm@gmail.com' , @subject = @subject , @body = @Body , @profile_name = 'ProfileName' , @body_format = 'HTML' ; SET @ini = 1 SET @cnt = (select MAX(seq) from #IndexFragRebuild) SET @SDate = GETDATE() WHILE(@ini <= @cnt) BEGIN BEGIN TRY SELECT @tableName = TableName, @indexName = IndexName, @FragPercent = FragPercent FROM #IndexFragRebuild WHERE seq = @ini IF (@FragPercent > 30) BEGIN SET @sql = 'ALTER INDEX '+@indexName+' on ' +@tableName+ ' REBUILD WITH (ONLINE = Off);' END ELSE BEGIN SET @sql = 'ALTER INDEX '+@indexName+' on ' +@tableName+ ' REORGANIZE ;' END EXEC (@sql) Update dba.IndexFragDetails SET LastRebuiltDate = GETDATE(), TimeTaken_Min = DATEDIFF(MINUTE,@Startdate,@Enddate), Result = 1 -- 1-Finish, 2-Failed, 0-Pending WHERE LastRebuiltDate IS NULL AND TableName = @tableName AND IndexName = @indexName AND CheckedDate = @MaxCheckedDate END TRY BEGIN CATCH SET @ErrorMessage = ERROR_MESSAGE() SET @Enddate = GETDATE() UPDATE dba.IndexFragDetails SET LastRebuiltDate = NULL, TimeTaken_Min = DATEDIFF(MINUTE,@Startdate,@Enddate), Result = 2 -- 1-Finish, 2-Failed, 0-Pending WHERE LastRebuiltDate IS NULL AND TableName = @tableName AND IndexName = @indexName AND CheckedDate = @MaxCheckedDate SET @subject = 'Rebuild\Reorganize error occured : ' + @indexName + ' index on ' + @tableName + ' table ' -- Sending an email of failed operation EXEC msdb.dbo.sp_send_dbmail @recipients = 'prajapatipareshm@gmail.com' , @subject = @subject , @body = @ErrorMessage , @profile_name = 'ProfileName' , @body_format = 'HTML' ; END CATCH SET @ini = @ini + 1 WAITFOR DELAY '00:00:05' END SET @EDate = GETDATE() SET @subject = 'All Indexes Rebuilt\Reorganized in '+ cast(DATEDIFF(MINUTE,@Sdate,@EDate) as varchar) + ' Minutes' -- Sending an email of completion EXEC msdb.dbo.sp_send_dbmail @recipients = 'prajapatipareshm@gmail.com' , @subject = @subject , @body = @subject , @profile_name = 'ProfileName' , @body_format = 'HTML' ; DROP TABLE #IndexFragRebuild GO
This is just what i want to share. Please share your opinion.