Auto Index Maintenance for All Databases in a SQL Server Instance
Problem
A few months back, I had shared a script to automate index rebuild/reorg in a database with fragmentation analysis inplace at https://www.sherbaz.com/2013/06/resumable-auto-index-rebuild-and-reorganize-script/. But that script would perform the maintenance only on a particular database at a time.
Solution
The script is modified and it now performs maintenance on all indexes across all databases in an instance. This again has two scripts.
1. analyze2.sql – Checks fragmentation level on indexes in all databases of the SQL instance and stores the results in tempdb.
2. defrag2.sql – Based on the data stored in tempdb by analyze2.sql, the script will process the maintenance, either Index rebuild or an index reorganize based on the fragmentation and the @minfrag, @maxfrag settings in defrag.sql script. Indexes fragmented below @minfrag in percentage will be excluded. Indexes fragmented between @minfrag {88dc7f9385b2c8971f38421926506de3e3fc5e843c9520f0674d4b7de6ae62da} and @maxfrag {88dc7f9385b2c8971f38421926506de3e3fc5e843c9520f0674d4b7de6ae62da} will be reorganized. All remaining indexes will be rebuilt.
Scripts are pasted below for your reference. Please let me know if you find some errors, bugs or issues with the code or the method used.
analyze2.sql
DECLARE @dbname AS VARCHAR (200);
DECLARE @tablename AS VARCHAR (255);
DECLARE @indexname AS VARCHAR (255);
DECLARE @sql AS VARCHAR (MAX);
--DECLARE @objectid INT;
DECLARE @indexid AS VARCHAR (50);
SET NOCOUNT ON;
IF Object_id('tempdb..temptablelist') IS NOT NULL
DROP TABLE tempdb..temptablelist;
CREATE TABLE tempdb..temptablelist
(
table_name VARCHAR (100)
);
IF Object_id('tempdb..tempindexlist') IS NOT NULL
DROP TABLE tempdb..tempindexlist;
CREATE TABLE tempdb..tempindexlist
(
index_name VARCHAR (100),
indid VARCHAR (50)
);
DECLARE dblist CURSOR
FOR SELECT name
FROM sys.databases
WHERE database_id NOT IN (1, 2, 3, 4);
OPEN dblist;
FETCH NEXT FROM dblist INTO @dbname;
WHILE @@FETCH_STATUS = 0
BEGIN
IF Object_id('tempdb..[fraglist' + @dbname + ']') IS NOT NULL
BEGIN
SET @sql = 'DROP TABLE tempdb..[fraglist' + @dbname + ']';
EXECUTE (@sql);
END
SET @sql = '
CREATE TABLE tempdb..[fraglist' + @dbname + ']
(
object_id VARCHAR(100),
index_id VARCHAR(50),
table_name VARCHAR(100),
index_name VARCHAR(100),
avg_fragmentation_in_percent DECIMAL
) ';
EXECUTE (@sql);
-- Declare a cursor.
SET @sql = 'insert into tempdb..temptablelist
select SCHEMA_NAME(schema_id)+''.''+name
from [' + @dbname + '].sys.objects
where type=''U'' and type_desc = ''USER_TABLE''';
EXECUTE (@sql);
DECLARE tables CURSOR
FOR SELECT table_name
FROM tempdb..temptablelist;
-- Open the cursor.
OPEN tables;
-- Loop through all the tables in the database.
FETCH NEXT FROM tables INTO @tablename;
WHILE @@FETCH_STATUS = 0
BEGIN;
SET @sql = 'insert into tempdb..tempindexlist
SELECT name,indid FROM [' + @dbname + ']..sysindexes
where id=OBJECT_ID(''[' + @dbname + '].' + @tablename + ''')
and dpages>0 and indid > 0
';
--print @sql
EXECUTE (@sql);
DECLARE indexes CURSOR
FOR SELECT index_name,
indid
FROM tempdb..tempindexlist;
OPEN indexes;
FETCH NEXT FROM indexes INTO @indexname, @indexid;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
SET @sql = '
INSERT INTO tempdb..[fraglist' + @dbname + ']
SELECT [object_id],
index_id,
''' + @tablename + ''' AS table_name,
''' + @indexname + ''' AS index_name,
avg_fragmentation_in_percent
FROM sys.Dm_db_index_physical_stats(Db_id(''' + @dbname + '''),
Object_id(''' + @dbname + '.' + @tablename + '''),
' + @indexid + ',
NULL,
''LIMITED'')
';
PRINT @sql;
EXECUTE (@sql);
FETCH NEXT FROM indexes INTO @indexname, @indexid;
END
TRUNCATE TABLE tempdb..tempindexlist;
CLOSE indexes;
DEALLOCATE indexes;
FETCH NEXT FROM tables INTO @tablename;
END;
TRUNCATE TABLE tempdb..temptablelist;
-- Close and deallocate the cursor.
CLOSE tables;
DEALLOCATE tables;
SET @sql = 'SELECT * FROM tempdb..[fraglist' + @dbname + ']';
EXECUTE (@sql);
FETCH NEXT FROM dblist INTO @dbname;
END
CLOSE dblist;
DEALLOCATE dblist;
defrag3.sql
DECLARE @maxfrag AS DECIMAL;
DECLARE @minfrag AS DECIMAL;
DECLARE @frag AS DECIMAL;
DECLARE @execstr AS VARCHAR (400);
DECLARE @tablename AS VARCHAR (255);
DECLARE @indexname AS VARCHAR (255);
DECLARE @dbname AS VARCHAR (200);
DECLARE @sql AS VARCHAR (MAX);
SET NOCOUNT ON;
-- Decide on the maximum and minimum fragmentation to allow for rebuild or reorg. fragmentation below minfrag will be ignored
SELECT @maxfrag = 30.0;
SELECT @minfrag = 4.0;
IF Object_id('tempdb..tempfraglist') IS NOT NULL
DROP TABLE tempdb..tempfraglist;
CREATE TABLE tempdb..tempfraglist
(
tablename VARCHAR (100),
indexname VARCHAR (100),
frag DECIMAL
);
DECLARE dblist CURSOR
FOR SELECT name
FROM sys.databases
WHERE database_id NOT IN (1, 2, 3, 4);
OPEN dblist;
FETCH NEXT FROM dblist INTO @dbname;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT '--Started Defrag/rebuild...for ' + @dbname + ' Database';
IF Object_id('tempdb..[fragstatus' + @dbname + ']') IS NOT NULL
BEGIN
SET @sql = 'DROP TABLE tempdb..[fragstatus' + @dbname + ']';
EXECUTE (@sql);
END
SET @sql = '
CREATE TABLE tempdb..[fragstatus' + @dbname + ']
(
table_name VARCHAR(100),
index_name VARCHAR(100)
) ';
EXECUTE (@sql);
SET @sql = 'insert into tempdb..tempfraglist
SELECT table_name,
index_name,
avg_fragmentation_in_percent
FROM tempdb..[fraglist' + @dbname + ']
WHERE index_name IS NOT NULL and index_name not in
(select index_name from tempdb..[fragstatus' + @dbname + '])
AND avg_fragmentation_in_percent > ' + CAST (@minfrag AS VARCHAR (50));
EXECUTE (@sql);
DECLARE fraglist CURSOR
FOR SELECT tablename,
indexname,
frag
FROM tempdb..tempfraglist;
OPEN fraglist;
FETCH NEXT FROM fraglist INTO @tablename, @indexname, @frag;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @frag > @maxfrag
BEGIN
SELECT @execstr = 'ALTER INDEX [' + @indexname + '] ON
[' + @dbname + '].[' + Replace(@tablename, '.', '].[') + ']
REBUILD WITH
(FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON, ONLINE = ON)';
END;
ELSE
BEGIN
SELECT @execstr = 'ALTER INDEX [' + @indexname + '] ON
[' + @dbname + '].[' + Replace(@tablename, '.', '].[') + ']
REORGANIZE';
END
BEGIN TRY
PRINT @execstr;
EXECUTE (@execstr);
END TRY
BEGIN CATCH
SELECT @execstr + ' : Index operation failed.' AS descript,
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH
--USE THIS TABLE TO VIEW THE ACTIVITY PROGRESS
SET @sql = 'INSERT INTO tempdb..[fragstatus' + @dbname + ']
VALUES (''' + @tablename + ''',
''' + @indexname + ''');';
--print @sql
EXECUTE (@sql);
FETCH NEXT FROM fraglist INTO @tablename, @indexname, @frag;
END
TRUNCATE TABLE tempdb..tempfraglist;
CLOSE fraglist;
DEALLOCATE fraglist;
SET @sql = 'SELECT * FROM tempdb..[fragstatus' + @dbname + ']';
EXECUTE (@sql);
PRINT '--Defrag/rebuild completed for database: ' + @dbname;
PRINT '----------------------------------------------------';
FETCH NEXT FROM dblist INTO @dbname;
END
CLOSE dblist;
DEALLOCATE dblist;
