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 dataCREATE 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 statisticsDECLARE @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 indexesSET 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.
