A certain SQL Server 2008 R2 instance runs in my environment hosting 106 databases used by a single weird application that creates new databases (when existing databases are just half full) based on some algorithm we are yet to fully understand. There are several good index rebuild scripts out there, but this approach worked for us in this scenario, and I am sure someone may find it useful.
The basis of the approach we used is a well-known sample script provided on SQL Server Books Online to check fragmentation of indexes. The original scripts for checking the degree of fragmentation with the intent of either rebuilding or reorganizing the indexes are something like the below listing:
/* SCRIPT 1: Check indexes with defragmentation greater than 30%. Use REBUILD for these indexes*/USE AdventureWorks2008 GO SELECT a.index_id, b.name, avg_fragmentation_in_percent, c.schema_id, a.object_id, database_id, c.name, 'ALTER INDEX [' + b.name + '] ON [' + SCHEMA_NAME(c.schema_id) + '].[' + OBJECT_NAME(a.object_id) + '] REBUILD WITH (ONLINE = OFF)' AS RebuildStatement FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS a JOIN sys.indexes AS b JOIN sys.objects AS c ON b.object_id = c.object_id ON a.object_id = b.object_id AND a.index_id = b.index_id where avg_fragmentation_in_percent > 30 GO
/* SCRIPT 2: Check indexes with defragmentation less than 30%. Use REORGANIZE for these indexes */USE AdventureWorks2008 GO SELECT a.index_id, b.name, avg_fragmentation_in_percent, c.schema_id, a.object_id, database_id, c.name, 'ALTER INDEX [' + b.name + '] ON [' + SCHEMA_NAME(c.schema_id) + '].[' + OBJECT_NAME(a.object_id) + '] REORGANIZE' AS ReorganizeStatement FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS a JOIN sys.indexes AS b JOIN sys.objects AS c ON b.object_id = c.object_id ON a.object_id = b.object_id AND a.index_id = b.index_id where avg_fragmentation_in_percent > 10 AND avg_fragmentation_in_percent < 30; GO
We modified the script to use the SORT_IN_TEMPDB option as well as change the FILL FACTOR to 80 rather then the default 100. A FILL FACTOR of 80 was chosen to make room for index growth as the application using these databases is INSERT-intensive. In your specific case, you may want to leave the default FILL FACTOR or even reduce the value further depending on the nature of your database. The modified scripts are shown in the listing below:
/* SCRIPT 3: Index Rebuild with More Options -- Determine Indexes with over 30 % fragmentation -- -- Note: use FILLFACTOR of 60 to 80 for OLTP database and 100 or 0 for DSS databases -- */USE <db_name> GO SELECT a.index_id, b.name, avg_fragmentation_in_percent, c.schema_id, a.object_id, database_id, c.name, 'ALTER INDEX [' + b.name + '] ON [' + SCHEMA_NAME(c.schema_id) + '].[' + OBJECT_NAME(a.object_id) + '] REBUILD WITH (ONLINE = OFF, SORT_IN_TEMPDB = ON, FILLFACTOR = 80, PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF)' AS RebuildStatement FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS a JOIN sys.indexes AS b JOIN sys.objects AS c ON b.object_id = c.object_id ON a.object_id = b.object_id AND a.index_id = b.index_id where avg_fragmentation_in_percent > 30 GO -- Run the result of SCRIPT 3 with intervals of ten statements -- -- i.e. insert GO after every ten lines -- -- Rerun the SCRIPT 3 to verify impact of index Rebuild -- /* SCRIPT 4: Index Reorganization with More Options -- Determine Indexes less than 30 % fragmentation -- */USE db_name> GO SELECT a.index_id, b.name, avg_fragmentation_in_percent, c.schema_id, a.object_id, database_id, c.name, 'ALTER INDEX [' + b.name + '] ON [' + SCHEMA_NAME(c.schema_id) + '].[' + OBJECT_NAME(a.object_id) + '] REORGANIZE' AS ReorganizeStatement FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS a JOIN sys.indexes AS b JOIN sys.objects AS c ON b.object_id = c.object_id ON a.object_id = b.object_id AND a.index_id = b.index_id WHERE avg_fragmentation_in_percent > 10 AND avg_fragmentation_in_percent < 30; GO -- Run the result of SCRIPT 4 with intervals of ten statements -- -- i.e. insert GO after every ten lines --
The above approach still requires manual intervention to run the output of the scripts so as to do the actual rebuild/reorg as the case may be. The next step was to add a little intelligence to the script such that the output becomes a parameter in the sp_executesql stored procedure.
/* Query 9: Generate and Run Index Rebuild Statements Automatically */-- Ths script can be used in a job -- For pre-2005 you may have to specify the actual database id instead of the DB_ID() function use postilion_office go create table #indtab (ID SMALLINT IDENTITY(1,1), REBUILDSTMT nvarchar(600)) insert into #indtab SELECT 'ALTER INDEX [' + b.name + '] ON [' + SCHEMA_NAME(c.schema_id) + '].[' + OBJECT_NAME(a.object_id) + '] REBUILD WITH (ONLINE = OFF,FILLFACTOR=80,SORT_IN_TEMPDB=ON,PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF);' FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS a JOIN sys.indexes AS b JOIN sys.objects AS c ON b.object_id = c.object_id ON a.object_id = b.object_id AND a.index_id = b.index_id where avg_fragmentation_in_percent > 30 GO select * from #indtab declare @sql nvarchar(600) declare @rbldrows smallint select @rbldrows = max(id) from #indtab while (@rbldrows > 0) begin print 'Total number of rebuild statements:' + cast(@rbldrows as nvarchar(10)) --print @rbldrows select @sql= REBUILDSTMT from #indtab where id = @rbldrows print @sql exec sp_executesql @sql; set @rbldrows = @rbldrows - 1; print 'Index Rebuild Complete' end drop table #indtab /* Query 10: Generate and Run Index Reorg Statements Automatically */use db_name() go create table #indtab (ID SMALLINT IDENTITY(1,1), REORGSTMT nvarchar(600)) insert into #indtab SELECT 'ALTER INDEX [' + b.name + '] ON [' + SCHEMA_NAME(c.schema_id) + '].[' + OBJECT_NAME(a.object_id) + '] REORGANIZE;' FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS a JOIN sys.indexes AS b JOIN sys.objects AS c ON b.object_id = c.object_id ON a.object_id = b.object_id AND a.index_id = b.index_id where avg_fragmentation_in_percent > 10 AND avg_fragmentation_in_percent < 30 GO select * from #indtab declare @sql nvarchar(600) declare @reorgrows smallint select @reorgrows = max(id) from #indtab while (@reorgrows > 0) begin print 'Total number of rebuild statements:' + cast(@reorgrows as nvarchar(10)) select @sql= REORGSTMT from #indtab where id = @reorgrows print @sql exec sp_executesql @sql; set @reorgrows = @reorgrows - 1; print 'Index Rebuild Complete' end drop table #indtab
The above script will allow us to automatically rebuild indexes according to their degree of fragmentation in only ONE database. Let us run through the script and point out a few things:
- We chose to create a temporary table, #indtab, and insert into this table rather than just using a SELECT INTO because we wanted to use the ID column as a filter in subsequent WHILE loop. The WHILE loop essentialy picks each row of the REBUILDSTMT/ REORGSTMT column of the #indtab table and assigns this to the @sql variable. Picking each row requires that we have a column we can filter by. The ID column is useful in this case.
- There could be any number of fragmented indexes depending on the database we are dealing with. In other to loop through all rows in the #indtab table, we have to start with the last row. We do this by assigning the MAX(ID) value to a variable @rbldrows or @reorgrows and then keep decrementing this till we run the first rebuild/reorg statement in the #indtab table.
- We did have one or two arithmetic overflow errors so one has to be careful with the length of the data types assigned to each variable and the columns in the #indtab table. This would depend on for example the length of one's database names as well as the number of fragmented indexes. The final PRINT line has to cast the @reorgrows variable as nvarchar(10). A length of 10 caters for up to 1,000,000,000 fragmentment indexes. Hopefully no one has this amount. 🙂
- It is of course necessary to drop the #indtab table at the end of the entire script so we can reuse the name when next the script runs in a job for example.
To improve on the script so we can handle multiple databases we must use the stored procedure sp_msforeachdb and exclude the system databases as in the listing below:
/* Query 11: Run Index Rebuild Across Databases Using sp_msforeachdb */exec sp_MSforeachdb @command1= ' if ''?'' not in ("master","model","msdb","tempdb") use [?] create table #indtab (ID SMALLINT IDENTITY(1,1), REBUILDSTMT nvarchar(600)) insert into #indtab SELECT ''ALTER INDEX ['' + b.name + ''] ON ['' + SCHEMA_NAME(c.schema_id) + ''].['' + OBJECT_NAME(a.object_id) + ''] REBUILD WITH (ONLINE = OFF,FILLFACTOR=80,SORT_IN_TEMPDB=ON,PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF);'' FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS a JOIN sys.indexes AS b JOIN sys.objects AS c ON b.object_id = c.object_id ON a.object_id = b.object_id AND a.index_id = b.index_id where avg_fragmentation_in_percent > 30 select * from #indtab declare @sql nvarchar(600) declare @rbldrows smallint select @rbldrows = max(id) from #indtab while (@rbldrows > 0) begin print ''Total number of rebuild statements:'' + cast(@rbldrows as nvarchar(10)) --print @rbldrows select @sql= REBUILDSTMT from #indtab where id = @rbldrows print @sql exec sp_executesql @sql; set @rbldrows = @rbldrows - 1; print ''Index Rebuild Complete'' end drop table #indtab' /* Query 12: Run Index Reorg Across Databases Using sp_msforeachdb */ exec sp_MSforeachdb @command1= ' if ''?'' not in ("master","model","msdb","tempdb") use [?] create table #indtab (ID SMALLINT IDENTITY(1,1), REORGSTMT nvarchar(600)) insert into #indtab SELECT ''ALTER INDEX ['' + b.name + ''] ON ['' + SCHEMA_NAME(c.schema_id) + ''].['' + OBJECT_NAME(a.object_id) + ''] REORGANIZE;'' FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS a JOIN sys.indexes AS b JOIN sys.objects AS c ON b.object_id = c.object_id ON a.object_id = b.object_id AND a.index_id = b.index_id where avg_fragmentation_in_percent > 10 AND avg_fragmentation_in_percent < 30 GO select * from #indtab declare @sql nvarchar(600) declare @reorgrows smallint select @reorgrows = max(id) from #indtab while (@reorgrows > 0) begin print ''Total number of rebuild statements:'' + cast(@reorgrows as nvarchar(10)) --print @reorgrows select @sql= REORGSTMT from #indtab where id = @reorgrows print @sql exec sp_executesql @sql; set @reorgrows = @reorgrows - 1; print ''Index Rebuild Complete'' end drop table #indtab'
A few notes
- The IF command is used to exclude SYSTEM databases from the sp_msforeachdb looping. We generally prefer not to bother with rebuilding indexes on the small system databases. There is not likely to be any benefit from that excercise.
- Also observe the use of square brackets to 'fence' the database name place holder "?". It is useful in case any database name in the instance has a space.
- The GO command is completely excluded when using sp_msforeachdb as we find that it does not work when a script is passed as the @command1 parameter.
The final step would be to use this script in a scheduled job. It could also be used to build a stored procedure which would then be called by the scheduled job. The below listing shows the job we finally arrived at to rebuild and reorganize indexes for our instance of 106 user databases:
-- ########################################################## -- # Manage Indexes Job # -- # Kenneth Igiri # -- # eProcess International S.A. , Accra # -- # # -- # WRITTEN: 25/01/2015 # -- # LAST MODIFIED: 25/01/2015 # -- # # -- ########################################################## -- ********************************************************** -- * NOTES * -- * Please use this for database with roughly * -- * same size of tables an many databases. * -- * For databases with large core tables it may * -- * be necessary to apply more specific indexing * -- * strategy. * -- ********************************************************** -- ========================================================== -- = SECTION 1: = -- ========================================================== /* Create Job to Rebuild and Reorganize Indexes */USE [msdb] GO /****** Object: Job [Custom_Rebuild_Reorg_Indexes] Script Date: 01/25/2015 15:57:27 ******/BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 /****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 01/25/2015 15:57:27 ******/IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END DECLARE @jobId BINARY(16) EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Custom_Rebuild_Reorg_Indexes', @enabled=1, @notify_level_eventlog=0, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'No description available.', @category_name=N'[Uncategorized (Local)]', @owner_login_name=N'ECOBANKGROUP\kigiri', @job_id = @jobId OUTPUT IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /****** Object: Step [Rebuild Indexes] Script Date: 01/25/2015 15:57:27 ******/EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Rebuild Indexes', @step_id=1, @cmdexec_success_code=0, @on_success_action=3, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'exec sp_MSforeachdb @command1= '' if ''''?'''' not in ("master","model","msdb","tempdb") use [?] create table #indtab (ID SMALLINT IDENTITY(1,1), REBUILDSTMT nvarchar(600)) insert into #indtab SELECT ''''ALTER INDEX ['''' + b.name + ''''] ON ['''' + SCHEMA_NAME(c.schema_id) + ''''].['''' + OBJECT_NAME(a.object_id) + ''''] REBUILD WITH (ONLINE = OFF,FILLFACTOR=80,SORT_IN_TEMPDB=ON,PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF);'''' --INTO #indtab FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS a JOIN sys.indexes AS b JOIN sys.objects AS c ON b.object_id = c.object_id ON a.object_id = b.object_id AND a.index_id = b.index_id where avg_fragmentation_in_percent > 30 select * from #indtab declare @sql nvarchar(600) declare @rbldrows smallint select @rbldrows = max(id) from #indtab while (@rbldrows > 0) begin print ''''Total number of rebuild statements:'''' + cast(@rbldrows as nvarchar(10)) --print @rbldrows select @sql= REBUILDSTMT from #indtab where id = @rbldrows print @sql exec sp_executesql @sql; set @rbldrows = @rbldrows - 1; print ''''Index Rebuild Complete'''' end drop table #indtab''', @database_name=N'master', @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /****** Object: Step [Reorganize Indexes] Script Date: 01/25/2015 15:57:27 ******/EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Reorganize Indexes', @step_id=2, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N' exec sp_MSforeachdb @command1= '' if ''''?'''' not in ("master","model","msdb","tempdb") use [?] create table #indtab (ID SMALLINT IDENTITY(1,1), REORGSTMT nvarchar(600)) insert into #indtab SELECT ''''ALTER INDEX ['''' + b.name + ''''] ON ['''' + SCHEMA_NAME(c.schema_id) + ''''].['''' + OBJECT_NAME(a.object_id) + ''''] REORGANIZE;'''' FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS a JOIN sys.indexes AS b JOIN sys.objects AS c ON b.object_id = c.object_id ON a.object_id = b.object_id AND a.index_id = b.index_id where avg_fragmentation_in_percent > 10 AND avg_fragmentation_in_percent < 30 GO select * from #indtab declare @sql nvarchar(600) declare @reorgrows smallint select @reorgrows = max(id) from #indtab while (@reorgrows > 0) begin print ''''Total number of rebuild statements:'''' + cast(@reorgrows as nvarchar(10)) --print @reorgrows select @sql= REORGSTMT from #indtab where id = @reorgrows print @sql exec sp_executesql @sql; set @reorgrows = @reorgrows - 1; print ''''Index Rebuild Complete'''' end drop table #indtab'' ', @database_name=N'master', @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Sch_Rebuild_Reorg_Indexes', @enabled=1, @freq_type=8, @freq_interval=1, @freq_subday_type=1, @freq_subday_interval=0, @freq_relative_interval=0, @freq_recurrence_factor=1, @active_start_date=20150125, @active_end_date=99991231, @active_start_time=40000, @active_end_time=235959, @schedule_uid=N'0986319e-9d59-4300-8ac5-8834a2f1c601' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback COMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave: GO
Please feel free to add comments on the dicussion board if you know something more about how we can improve the approach. I am sure you may also find a few useful ideas on scripts you may be writing in your own environment.
Thank you for reading.
References
https://www.sqlskills.com/blogs/jonathan/the-accidental-dba-day-14-of-30-index-maintenance/
https://msdn.microsoft.com/en-us/library/ms188388.aspx