SQLServerCentral Article

The Real World: Rebuilding Index - 1 Instance, 106 Databases

,

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

https://msdn.microsoft.com/en-us/library/ms188917.aspx

https://msdn.microsoft.com/en-us/library/ms188001.aspx

Resources

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

3 (18)

You rated this post out of 5. Change rating

Share

Categories

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

3 (18)

You rated this post out of 5. Change rating

Related content