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
' + '
'
-- 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.
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] GO2. 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' ) GO3. 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+'
Comments
Post a Comment