Index maintenance using Ola Hallengren’s scripts

Having recently set up Index maintenance using Ola Hallengren’s scripts on a range of my company’s production servers, I wanted to write about the steps involved including some custom steps to ensure the overall health of the SQL Server database.

The aim of this blog post is to be not too technical, I don’t cover what Ola has documented on his site however I do include scripts for my custom steps. I will only cover index maintenance, although these scripts give the options for statistics maintenance also. This is because we already had a custom solution for statistics maintenance that was working great, whereas we needed to make improvements to our index maintenance solution.

Overview

First of all, I have to say that Ola Hallengren has done a fantastic job with the index maintenance scripts as well as the backup scripts (which I don’t discuss here). They are kept up to date and work on all SQL Server versions from 2005 onwards.

The first thing to do is to visit’s Ola’s site and read about how to set up the Index maintenance.

Before you start

By default, Ola’s scripts install the required objects to perform the maintenance directly into your master database. I recommend using another database if you have one specifically for DBA work, on each instance. You could create one and call it DBADB if you don’t already do this. If you choose this option, you only need to make one small change to the downloads (which is to change the USE Master statement into a USE DBADB statement).

To ensure I can stop the process automatically if it is generating too much transaction log, I create a metadata table for measuring logspace before I start on the process. My scripts already include the USE DBADB statement. Again, just change this to the name of the database you wish to use.

1. IndexMaintenance_Logspace.sql: Important for checking transaction log size whilst the process is ongoing. (important)
   

[sql]
USE [DBADB]
GO
IF NOT EXISTS (
SELECT 1 from sysobjects where name = ‘IndexMaintenance_Logspace’)
BEGIN
CREATE TABLE [dbo].[IndexMaintenance_Logspace](
[DataBaseName] [varchar](100) NULL,
[LogSize] [numeric](18, 4) NULL,
[LogPercentage] [numeric](18, 4) NULL,
[Status] [int] NULL,
InsertDate DATETIME NOT NULL DEFAULT GETDATE()
) ON [PRIMARY]
END
[/sql]

Setting up Index Maintenance

Download the required scripts you need from Ola’s download page.

You can download the full solution which includes all of:

• DatabaseBackup.sql: Stored procedure to back up databases
• DatabaseIntegrityCheck.sql: Stored procedure to check the integrity of databases
• IndexOptimize.sql: Stored procedure to rebuild and reorganize indexes and update statistics
• CommandExecute.sql: Stored procedure to execute and log commands
• CommandLog.sql: Table to log commands

(It also sets up SQL Agent jobs to do the maintenance)

Or you can download the individual scripts which is what I have chosen to do in this case. This allows you to customise your solution a little further. The individual scripts needed if you are only doing index maintenance are:

• IndexOptimize.sql
• CommandExecute.sql
• CommandLog.sql

Execute the scripts in the following order (if using individual scripts)

1. CommandLog.sql
2. CommandExecute.sql
3. IndexOptimize.sql

Automation

Your index maintenance should be automated, that way it always gets done. It should also report on any errors so you know if you have a problem that needs some attention.

I have achieved this via SQL Agent jobs. The script below can be amended depending on the number of jobs you want. On each instance, I went for one job to maintain all small databases and system databases. I then made individual jobs for each of our larger databases. This could also be achieved by using different job steps for each database or set of databases however I felt that separate jobs for the big databases was easier for other DBAs to understand.

1. Create Job and schedule daily

[sql]
USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N’Database Maintenance’ AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N’Database Maintenance’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N’Index_Maintenance_All_Other_DBs’,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N’Index and statistics maintenance.Documentation at: https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html’,
@category_name=N’Database Maintenance’,
@owner_login_name=N’sa’,
@notify_email_operator_name=N’DBA_ONLY’, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Enable T-Log checking job’,
@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’IF EXISTS (SELECT * FROM msdb.dbo.sysjobs WHERE name = N”Index_Maintenance_KILL_IF_TLOG_GROWS”)
BEGIN
EXEC msdb.dbo.sp_update_job
@job_name = N”Index_Maintenance_KILL_IF_TLOG_GROWS”,
@enabled = 1;
END
ELSE
BEGIN
RAISERROR (”T-Log checking job does not exist. Do not run Index_Maintenance”,16,1)
END’,
@database_name=N’master’,
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Run IndexOptimize SYSTEM_DATABASES’,
@step_id=2,
@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’CmdExec’,
@command=N’sqlcmd -E -S JOHNSERVER1 -d DBADB -Q “EXECUTE dbo.usp_IndexOptimize @Databases = ”SYSTEM_DATABASES,DBADB”, @FragmentationHigh = ”INDEX_REORGANIZE”, @LogToTable = ”Y”, @TimeLimit = 1800, @UpdateStatistics = ”ALL”, @OnlyModifiedStatistics = ”Y” ” -b’,
@output_file_name=N’D:\DBA\Index_Maintenance\indexoptimize_output.txt’,
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Disable T-Log checking job’,
@step_id=3,
@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’IF EXISTS (SELECT * FROM msdb.dbo.sysjobs WHERE name = N”Index_Maintenance_KILL_IF_TLOG_GROWS”)
BEGIN
EXEC msdb.dbo.sp_update_job
@job_name = N”Index_Maintenance_KILL_IF_TLOG_GROWS”,
@enabled = 0;
END
ELSE
BEGIN
RAISERROR (”T-Log checking job does not exist. Do not run Index_Maintenance”,16,1)
END’,
@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’Daily at X O”Clock’,
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20150813,
@active_end_date=99991231,
@active_start_time=230000,
@active_end_time=235959
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
[/sql]

Customisation

One thing that should be carefully measured is transaction log activity. Maintenance on large indexes can cause a huge amount of growth to T-Logs. Even with regular t-log backups, they can still increase to a size that can take your database offline. I manage this risk in 2 ways, increase the backup schedule for transaction logs and automate a kill command if any transaction logs exceed 70% full. You can set this at any value you fell comfortable with. A note of caution, automating any kill commands against a database is very risky and you could lose data if not done right. I’m happy my approach works for me but you should verify this yourself being running on any SQL Server instance. Make sure you understand 100% of how the script works and that it does what you want it to.

The job above enables and disables another job Index_Maintenance_KILL_IF_TLOG_GROWS which is scheduled to run every minute when enabled. (Discussed further down this page) If this job finds a transaction log more than 70% full (or whatever you choose), it will kill the Index_Maintenance_All_Other_DBs job. You must consider how regularly you are running Index Maintenance jobs and what methods of defragmentation you allow. On enterprise edition, I allow REBUILD with (ONLINE=ON) and REORGANIZE. On anything lower such as standard edition, I only allow REORGANIZE. Since a regularly scheduled job will usually catch most indexes when in the range of REORGANIZE rather than REBUILD, we don’t need to worry about rolling back as REORGANIZE INDEX doesn’t roll back when killed. It maintains its progress. Even if a REBUILD is ongoing and we don’t want to risk growing our T-Log, we have to live with the rollback. In practice, you shouldn’t find you encounter a situation often where a large index is rebuilding and needs rolled back.

1. Kill if T-Log grows

The job below is referenced by Index_Maintenance_All_Other_DBs job. Its purpose is to monitor the used percentage of the transaction log for each database. If any of them exceed the set threshold (for me 70%), a kill command is issued which kills the index rebuild or reorgnize. It usually sits in a disabled state and is only enabled when the index maintenance process is running. (Remember to change any references to DBADB to whatever your own DBA database name is)

[sql]
USE [msdb]

GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
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’Index_Maintenance_KILL_IF_TLOG_GROWS’,
@enabled=0,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N’This job checks the t-log growth on all DBs undergoing Index maintenance. If TLOG grows to 70% full, the operation is killed and DBA team are alerted.’,
@category_name=N'[Uncategorized (Local)]’,
@owner_login_name=N’sa’,
@notify_email_operator_name=N’DBA_ONLY’, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Check for problem operations and kill’,
@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’
CREATE TABLE #ls ([DatabaseName] [varchar](100) NULL,
[LogSize] [numeric](18, 4) NULL,
[LogPercentage] [numeric](18, 4) NULL,
[Status] [int] NULL)INSERT INTO #ls
(
[DatabaseName],
[LogSize],
[LogPercentage],
[Status]
)
EXEC (”DBCC SQLPERF (logspace)”)IF EXISTS (
SELECT TOP 1 1 from #ls
WHERE LogPercentage > 70
)
BEGIN
DECLARE @Killcmds TABLE (
cmd NVARCHAR (16)
)INSERT INTO @Killcmds (cmd)
SELECT ”kill ” + CONVERT(nvarchar,r.session_id)+”;”
from sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s
ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)
WHERE s.nt_user_name = ”JOHNSERVER1.SQL”
AND text LIKE ”ALTER INDEX%”
AND s.session_id<>@@spid
AND program_name = ”SQLCMD”
AND command = ”DBCC”DECLARE @SPID int
DECLARE @cmd nvarchar(200)DECLARE kill_cursor CURSOR FOR
SELECT cmd from @KillcmdsOPEN kill_cursor
FETCH NEXT FROM kill_cursor INTO @cmd
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_executesql @cmd
PRINT @cmd
EXEC (”DBCC SQLPERF (logspace)”)
FETCH NEXT FROM kill_cursor INTO @cmd
END
CLOSE kill_cursor
DEALLOCATE kill_cursorEND
DROP TABLE #ls’,
@database_name=N’master’,
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Insert logspace into table’,
@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’– Record logspace
INSERT INTO [DBADB].[dbo].[IndexMaintenance_Logspace]
(
[DatabaseName],
[LogSize],
[LogPercentage],
[Status]
)
EXEC (”DBCC SQLPERF (logspace)”)’,
@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’Every minute (when enabled)’,
@enabled=0,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=1,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20150325,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
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[/sql]
A parameter called TimeLimit can be provided which allows the DBA to set a time, in seconds, after which no commands are executed. This means that if a command is executing and the the TimeLimit had been exceeded, it would continue until the rebuild or reorganize command had completed. This is usually the way you would want it as rebuilds need to be rolled back if they are terminated. I however wanted to set an upper time limit; so I created a job that would kill any other Index Maintenance still going 30 minutes after the TimeLimit period.

1. Create Index_Maintenance Stop job

[sql]
USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0IF 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’Index_Maintenance_STOP’,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N’This job simply stops the job Index_Maintenance_All_Other_DBs if it is still running. Previous outputs form the job runs can be found on D:\DBA\Index_Maintenance\Stop_Job_Index_Maintenance_All_Other_DBs.log’,
@category_name=N'[Uncategorized (Local)]’,
@owner_login_name=N’sa’,
@notify_email_operator_name=N’DBA_ONLY’, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Stop Job if still running’,
@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’DECLARE @JOB_NAME SYSNAME = N”Index_Maintenance_All_Other_DBs”

IF EXISTS(
SELECT 1
FROM msdb.dbo.sysjobs_view sjv
INNER JOIN msdb.dbo.sysjobactivity sja
ON sjv.job_id = sja.job_id
WHERE sja.run_requested_date is not null
AND sja.stop_execution_date is null
AND sjv.name = @JOB_NAME
)
BEGIN
PRINT ”Stop Job”
EXEC msdb..sp_stop_job ”Index_Maintenance_All_Other_DBs”
END
ELSE
BEGIN
PRINT ”Not running”
END’,
@database_name=N’master’,
@output_file_name=N’D:\DBA\Index_Maintenance\Stop_Job_Index_Maintenance_All_Other_DBs.log’,
@flags=2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Disable T-Log checking job’,
@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’IF EXISTS (SELECT * FROM msdb.dbo.sysjobs WHERE name = N”Index_Maintenance_KILL_IF_TLOG_GROWS”)
BEGIN
EXEC msdb.dbo.sp_update_job
@job_name = N”Index_Maintenance_KILL_IF_TLOG_GROWS”,
@enabled = 0;
END
ELSE
BEGIN
RAISERROR (”T-Log checking job does not exist. Do not run Index_Maintenance”,16,1)
END’,
@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’Daily at X O”Clock’,
@enabled=0,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20150224,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
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
[/sql]

Useful tips

I always set my parameters in the stored procedure call, rather than changing the body of the procs. The main reason for this is visibility – in that it makes it easy for a new DBA to see which non-standard parameters are being passed in. Another benefit is that if I download an updated version of the scripts, I won’t lose my customisation.