Rebuild indexes dynamically for all databases
In this article we are going to learn how we can rebuild indexes for all the databases having fragmentation level more than defined level. Fragmentation level to rebuild the indexes may vary databases to database and In our example we are assuming it as 20%. Which can be suitable for most database servers.
We will also log the details of the indexes before rebuilding them and after rebuilding them in a table to get any report in near future.
And we will exclude all those indexes which have page counts less than 8, i.e. the table is too small and can fit in various different locations intentionally by DB engine.
Log Table Script:
CREATE TABLE MyDB.dbo.cmn_RebuildIndexesLog ( in_BatchNo INT, in_DBId INT, vc_DBName VARCHAR(256), vc_SchemaName VARCHAR(256), vc_TableName VARCHAR(256), vc_IndexName VARCHAR(256), num_AvgFragPercent NUMERIC(18,2), vc_TypeDesc CHAR(1) --Will store 'B' for before rebuild, 'A' for after rebuild )
Procedure To build indexes dynamically for all databases:
CREATE PROCEDURE usp_cmn_RebuildIndexes
AS
BEGIN
DECLARE @in_BatchNo INT
SELECT @in_BatchNo = (ISNULL(MAX(in_BatchNo),0) + 1) FROM MyDB.dbo.cmn_RebuildIndexesLog
DECLARE @vc_DBName VARCHAR(200)
DECLARE @in_DBID INT
DECLARE cur_DBList CURSOR FOR
SELECT NAME, DATABASE_ID FROM SYS.DATABASES
WHERE NAME NOT IN
('master', 'tempdb', 'model', 'msdb', 'ReportServer', 'ReportServerTempDB') --Add if want to exclude any other too
OPEN cur_DBList
FETCH NEXT FROM cur_DBList INTO @vc_DBName, @in_DBID
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @nvc_Qry NVARCHAR(4000)
SET @nvc_Qry = N'USE ' + @vc_DBName + ';
DECLARE @vc_TableName VARCHAR(250)
DECLARE @vc_IndexName VARCHAR(250)
DECLARE @vc_SchemaName VARCHAR(100)
DECLARE @nvc_ExecQry NVARCHAR(1100)
INSERT INTO MyDB.dbo.cmn_RebuildIndexesLog
(in_BatchNo, in_DBId,vc_DBName,vc_SchemaName,vc_TableName,vc_IndexName,num_AvgFragPercent,vc_TypeDesc)
SELECT ' + CAST(@in_BatchNo AS VARCHAR(5)) + ' AS in_BatchNo
,''' + CAST(@in_DBID AS VARCHAR(5)) + ''' AS DBId, ''' + @vc_DBName + ''' AS DBName
,SCHEMA_NAME(O.SCHEMA_ID) SchemaName
,OBJECT_NAME(D.OBJECT_ID) AS [TableName]
,I.NAME AS [IndexName], AVG_FRAGMENTATION_IN_PERCENT, ''B'' AS vc_TypeDesc
FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(' + CAST(@in_DBID AS VARCHAR(5)) + ', NULL, -1, NULL, NULL) D
INNER JOIN SYS.INDEXES I ON I.OBJECT_ID=D.OBJECT_ID AND I.INDEX_ID = D.INDEX_ID
INNER JOIN SYS.OBJECTS O ON I.OBJECT_ID = O.OBJECT_ID
WHERE AVG_FRAGMENTATION_IN_PERCENT >= 20 AND I.NAME IS NOT NULL
AND D.PAGE_COUNT > 8
DECLARE cur_IndexDetails CURSOR FOR
SELECT
vc_SchemaName AS SchemaName,
vc_TableName AS [TableName]
,vc_IndexName AS [IndexName]
FROM MyDB.dbo.cmn_RebuildIndexesLog
WHERE in_BatchNo = ' + CAST(@in_BatchNo AS VARCHAR(5)) + '
AND in_DBId = ' + CAST(@in_DBID AS VARCHAR(5)) + ' AND vc_TypeDesc = ''B''
OPEN cur_IndexDetails
FETCH NEXT FROM cur_IndexDetails INTO @vc_SchemaName, @vc_TableName, @vc_IndexName
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @nvc_ExecQry = N''ALTER INDEX ['' + @vc_IndexName + ''] ON ['' + @vc_SchemaName + ''].['' + @vc_TableName + '']
REBUILD
WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = ON )''
EXEC SP_EXECUTESQL @nvc_ExecQry
FETCH NEXT FROM cur_IndexDetails INTO @vc_SchemaName, @vc_TableName, @vc_IndexName
END
CLOSE cur_IndexDetails
DEALLOCATE cur_IndexDetails
INSERT INTO MyDB.dbo.cmn_RebuildIndexesLog
(in_BatchNo, in_DBId,vc_DBName,vc_SchemaName,vc_TableName,vc_IndexName,num_AvgFragPercent,vc_TypeDesc)
SELECT ' + CAST(@in_BatchNo AS VARCHAR(5)) + ' AS in_BatchNo,
''' + CAST(@in_DBID AS VARCHAR(5)) + ''' AS DBId, ''' + @vc_DBName + ''' AS DBName
,SCHEMA_NAME(O.SCHEMA_ID) SchemaName,
OBJECT_NAME(D.OBJECT_ID) AS [TableName]
,I.NAME AS [IndexName], AVG_FRAGMENTATION_IN_PERCENT, ''A'' AS vc_TypeDesc
FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(' + CAST(@in_DBID AS VARCHAR(5)) + ', NULL, -1, NULL, NULL) D
INNER JOIN SYS.INDEXES I ON I.OBJECT_ID=D.OBJECT_ID AND I.INDEX_ID = D.INDEX_ID
INNER JOIN SYS.OBJECTS O ON I.OBJECT_ID = O.OBJECT_ID
WHERE AVG_FRAGMENTATION_IN_PERCENT >= 20 AND I.NAME IS NOT NULL
AND D.PAGE_COUNT > 8 '
DECLARE @nvc_ParamDefDB NVARCHAR(200)
EXEC SP_EXECUTESQL @nvc_Qry
FETCH NEXT FROM cur_DBList INTO @vc_DBName, @in_DBID
END
--print @nvc_qry
CLOSE cur_DBList
DEALLOCATE cur_DBList
END
Conclusion
In this article we can also exclude those database in which we are not interested to perform index rebuild. Just have a look at this line;
WHERE NAME NOT IN
('master', 'tempdb', 'model', 'msdb', 'ReportServer', 'ReportServerTempDB') --Add if want to exclude any
You can add other databases too here. Replace below lines as per your need to set fragmentation level and page count. You can also put it in procedure parameters;
WHERE AVG_FRAGMENTATION_IN_PERCENT >= 20 AND I.NAME IS NOT NULL AND D.PAGE_COUNT > 8
We have logged to times the details of indexes on which we are going to operate. First time we log it with vc_TypeDesc as “B” and after rebuilding it we again log it as “A” for before and after to compare it with appropriate batch number. Batch number is also dynamically fetched from the log table as below to keep it in sequence;
SELECT @in_BatchNo = (ISNULL(MAX(in_BatchNo),0) + 1) FROM MyDB.dbo.cmn_RebuildIndexesLog
As per your need you can modify other settings too in the procedure.
This procedure can be scheduled with job scheduler to perform the rebuild at the time when our server is not too much busy.
So after using this procedure share your comment on this.
Thanks.
