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

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.