This is the third article in Ola Hallengren’s SQL Server Maintenance Solution series. The article will cover the IndexOptimize stored procedure in more detail, along with the index optimization jobs created by Ola Hallengren’s scripts.
To get the detailed guide on installation procedure and solutions for database backup and integrity check, feel free to first review the first two articles of the series:
- Ola Hallengren’s SQL Server Maintenance Solution – Installation and SQL Server Backup solution
- Ola Hallengren’s SQL Server Maintenance Solution – Database integrity check
The SQL Server Index and Statistics Maintenance part of Ola Hallengren’s solution consists of IndexOptmize stored procedure and IndexOptimize – USER_DATABASES agent job, that are created during the installation process. The procedure CommandExecute also has to be installed in order to use the index optimize solution. All these procedures and jobs are installed by default, but if you prefer the custom installation, make sure that everything is installed properly on the server.
In SSMS, connect to the server, and expand the following nodes in Object Explorer:
Databases\System Databases\master\Programmability\Stored Procedures
Make sure that the procedures dbo.CommandExecute and dbo.IndexOptimize are present on the list.
The IndexOptimize – USER_DATABASES agent job is available at: SQL Server Agent\Jobs
IndexOptimize stored procedure
To fully understand the agent job created by the installation script, we need to go through the parameters that IndexOptimize stored procedure uses. The are total of 27 parameters that can be used to configure the procedure. To get full details on the procedure parameters, run a following query:
SELECT 'Parameter_name' = name,
'Type' = TYPE_NAME(user_type_id),
'Length' = max_length,
'Prec' = CASE
WHEN TYPE_NAME(system_type_id) = 'uniqueidentifier'
ELSE OdbcPrec(system_type_id, max_length, precision)
'Scale' = OdbcScale(system_type_id, scale),
'Param_order' = parameter_id,
'Collation' = CONVERT(SYSNAME,
WHEN system_type_id IN(35, 99, 167, 175, 231, 239)
WHERE object_id = OBJECT_ID('dbo.IndexOptimize');
Executing the query gets the following results:
It is possible to change the default values for each parameter inside the procedure, but this is not advised, as it will affect any agent job that already uses the procedure (like IndexOptimize – USER_DATABASES job for example).
The common practice is to pass all necessary parameters to the stored procedure through a T-SQL, or CmdExec script. These scripts can then be included in a SQL Server Agent job, and assigned to run on a custom schedule. Creating and configuring these jobs would result in a custom, fully automated index maintenance solution.
Before creating the script, it is necessary to understand the most important procedure parameters, along with the corresponding values.
The parameter specifies one or more databases that will be affected by the script. To include multiple databases, just separate the database names by comma (,). To exclude a database, use the hyphen character (-) in front of the database name. For the wildcard selection, use the percentage character (%). The list of all accepted parameters is presented in the table.
Parameter value Description SYSTEM_DATABASES Performs the operations on all system databases USER_DATABASES Performs the operations on all user databases ALL_DATABASES Performs the operations on all databases AVAILABILITY_GROUP_DATABASES Performs the operations on all databases belonging to the availability group DATABASE01, DATABASE2 Performs the operations on databases named “DATABASE01” and “DATABASE02” %DAT% Performs the operations on all databases that have the “DAT” string in their name
The parameter specifies the index selection. Comma, hyphen and percentage characters are used in the same way as with Databases parameter. If no other value is specified, the selection will include all indexes.
Parameter value Description ALL_INDEXES Selects all indexes Database1.Schema1.Tab1.Index1 Selects the Index1 on the object Schema1.Tab1 in Database1 Database1.Schema1.% Selects all indexes in Schema1 in Database1
Same rules apply for the availability groups selection, which is controlled by AvailabilityGroups parameter.
Parameter value Description ALL_AVAILABILITY_GROUPS Selects all availability groups AGROUP1 Selects the availability group named “AGROUP1” %GROUP% Selects all availability groups that contain string “GROUP” in their name
- FragmentationLevel1 and FragmentationLevel2
The stored procedure groups all indexes in 3 categories, depending on the value of average index fragmentation in percent. This value is obtained for all indexes, that belong to a database that specified by DATABASES parameter. The IndexOptimize procedure runs the sys.dm_db_index_physical_stats function to obtain average fragmentation values for the indexes, and then it groups them in 3 categories:
- Indexes with low fragmentation
- Indexes with medium fragmentation
- Indexes with high fragmentation
FragmentationLevel1 parameter is the integer value, that presents the maximum limit (in %) for the indexes with low fragmentation group, and lower limit for indexes with medium fragmentation.
Likewise, the FragmentationLevel2 parameter presents the maximal percentage limit for the indexes with medium fragmentation group, and starting point for the indexes with high fragmentation.
For example, we can set the parameter values as following:12@FragmentationLevel1 = 10,@FragmentationLevel2 = 30,
In this case, the procedure runs sys.dm_db_index_physical_stats check, and groups indexes in categories. All indexes that have the average fragmentation value between 0 and 10% are considered indexes with low fragmentation. Average fragmentation values for indexes with medium fragmentation is between 10 and 30%. If index fragmentation exceeds 30%, it is considered as highly fragmented index.
The default value for FragmentationLevel1 parameter is 5, while the FragmentationLevel2 takes the default value of 30. This means that if these values are not specified in the script, all indexes with fragmentation between 0 and 5% are considered indexes with low fragmentation, between 5 and 30% – indexes with medium fragmentation, and above 30% – highly fragmented indexes.
- FragmentationLow, FragmentationMedium and FragmentationHigh
Each of the parameters presents the corresponding index group based on the fragmentation percentages defined by FragmentationLevel1 and FragmentationLevel2 parameters. The value for each parameter specifies the maintenance action that will be executed on the index group.
Parameter value Description INDEX_REBUILD_ONLINE Runs the online index rebuild. The procedure uses SQL Server ALTER INDEX command, combined with REBUILD WITH ONLINE = ON option INDEX_REBUILD_OFFLINE Runs the online index rebuild. The procedure uses SQL Server ALTER INDEX command, combined with REBUILD WITH ONLINE = OFF option INDEX_REORGANIZE Reorganizes the index. The procedure uses SQL Server ALTER INDEX command, with the REORGANIZE option NULL Skips index maintenance for the specified index group. This is the default value for indexes with low fragmentation
While it’s possible to run reorganize and offline rebuild operations on any index, it is known that online rebuild is not supported for all indexes. For example, online rebuild is only available in Enterprise edition of SQL Server 2005, while SQL Server 2008 supports the operation in Evaluation, Developer and Enterprise editions. All later versions of SQL Server support online rebuild in all editions. The online index rebuild is also not supported for XML indexes, Spatial indexes and indexes on a local temp table.
Taking this into consideration, it is noticeable that online rebuild might fail due to SQL Server version, edition, or index type. Ola Hallengren designed the solution for these cases too: it is possible to specify multiple operations on an index group. If the first specified operation cannot be executed for any reason, the second operation will be performed. Here is the example:123@FragmentationLow = NULL@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'
In this case, indexes with low fragmentation will be skipped. The stored procedure will attempt to reorganize all indexes with medium fragmentation. If any of these indexes cannot be reorganized, online rebuild will be performed. If online rebuild also fails on an index, it will be rebuilt offline. Same logic is used for highly fragmented indexes: procedure initially tries to rebuild indexes online, and if this fails for any reason, offline rebuild is performed. The values presented in this query are also the default values used for these parameters.
The parameter takes only integer values. The value presents the index size in pages. All indexes that occupy less pages than specified value will be skipped by the maintenance operations. The default value for PageCountLevel is 1000, which means that all indexes smaller than 1000 pages will be skipped, if not specified otherwise.
SortInTempdb specifies if the tempdb will be used for sort operations while running the index rebuild. Available values for the parameter are Y (Yes) and N (No), later being the default.
MaxDOP is the integer value, that presents the number of CPUs that should be used for index rebuild operations. If not specified otherwise, the global maximum degree of parallelism is used.
Sets the fill factor in percent for the index rebuild operations. If not specified otherwise, the procedure uses the fill factor that is already set for each index. Default SQL Server value is 0 or 100%, which means that no free space should be left on each page while rebuilding the index. Note that changing this value to any other than 0 or 100 results in index size growth, so use this option carefully.
PadIndex parameter applies the percentage of free space, specified by the fill factor, to the intermediate-level pages of the index. Available values are Y (yes) and N (no), N being the default.
The parameter defines whether or not to compact pages that contain large object columns when reorganizing indexes. Available values are Y (yes) and N (no). The default value is Y.
The procedure uses the T-SQL UPDATE STATISTICS command to perform statistics update. The value of UpdateStatistics parameter specifies the statistic types that will be updated. Parameter values are presented in the table:
Parameter value Description ALL Updates both index and column statistics INDEX Updates index statistics only COLUMNS Updates column statistics only NULL Skip the statistics update.
The default value for UpdateStatistics parameter is NULL.
The procedure updates statistics if rows have been modified since the last statistics update. Available values are Y (yes) and N (no). The parameter is taken into consideration only if value for UpdateStatistics is other than NULL.
The parameter specifies the percentage of total rows that are scanned while updating statistics. If the value of 100 is passed to the procedure, the full scan will be performed. If no value is provided, the SQL Server will automatically obtain the sample.
StatisticsResample parameter updates statistics with the most recent sample. Available values are Y (yes) and N (no), later being the default. If the default value is used, SQL Server will compute the required sample automatically.
Specifies whether to maintain partitioned indexes on the partition level. The procedure accepts Y (yes) and N (no) values, former being the default.
Specifies whether to maintain indexes and statistics on objects created by internal SQL Server components. The procedure accepts values Y (yes) and N (no), later being the default.
Use the TimeLimit parameter to limit the duration of index maintenance tasks. The value presents the time in seconds, after which all commands are stopped. By default, this parameter is not taken into consideration, and procedure has unlimited time to complete all commands.
The parameter presents the time delay (in seconds) between index commands. The default value is 0, and there is no delay between the index commands.
The value for this parameter is the time in minutes, that the procedure will wait for low priority locks, when an online index rebuild operation is performed.
The parameter specifies the action that will be taken after the time set by WaitAtLowPriorityMaxDuration parameter has passed. Available values are presented in the table.
Parameter value Description NONE Continues to wait for the locks SELF Aborts the rebuild operation BLOCKERS Kills the user transactions that block the rebuild operation
LockTimeout parameter sets the time (in seconds) that the procedure will wait for until the lock is released. If not specified otherwise, the procedure will wait on any locks indefinitely.
The parameter specifies whether to log the commands to dbo.CommandLog table. The available values are Y (yes) and N (no). The default value is N.
Specifies whether to execute procedure commands, or just print them. Available values are Y (yes) and N (no), former being the default.
IndexOptimize – USER_DATABASES SQL Server Agent job
Now that the all procedure parameters are covered, we can check each of them for the job that is pre-created by Ola’s scripts. To do this, we will need to check the script that is used in this job:
In Object Explorer, expand the nodes: SQL Server Agent\Jobs. Right click on IndexOptimize – USER_DATABASES job, and select Properties.
In the Job properties form, navigate to the Steps tab, select the first job step, and click on Edit.
In the Job step properties, the sqlcmd script that is executed by the job can be located in Command textbox.
The only parameters that are passed to the procedure are Databases (selection is performed on all user databases) and LogToTable (operations are logged to dbo.CommandLog table). This means that all other parameters, that are not speciffied in this script use their default values. At a glance, this job performs the following actions:
- Analyses the fragmentation, page count and fill factor of all indexes in all user databases on the server.
Divides indexes in categories, and performs the default operations on each category:
- Indexes with low fragmentation (fragmentation is less than 5%) – No maintenance operations are performed
- Indexes with medium fragmentation (fragmentation ranges between 5% and 30%) – The procedure will first attempt to reorganize indexes. If it fails, it will try to run online rebuild. Finaly, it runs offline rebuild, on all indexes that do not support online rebuild.
- Indexes with high fragmentation (fragmentation exceeds 30%): Online rebuild is run initially. Offline rebuild is performed on all indexes where online rebuild failed.
- The value for fill factor is unchanged for each index.
- Indexes smaller than 1000 pages are not processed.
- The statistics is not updated.
- There is no delay between index commands.
- There is no time limit on command execution.
- The online rebuild operations will wait for all locks until they are lifted.
If these default settings work for your environment, proceed with the job configuration in Job properties form. In order to automate the solution, the custom schedule needs to be set for the job.
To set the schedule, and test the job, perform the following steps:
Select the IndexOptimize – USER_DATABASES job, and check its Properties. Navigate to Schedules tab, and click New… button
In the New Job Schedule form, provide name and frequency for the schedule. Since offline rebuild tasks take the tables offline while processing their indexes, it is best practice to set the schedule to run when there is little to none traffic on the server. Check the desription at the bottom. If it’s good, click OK to create the schedule.
Back in Job properties form, it might be usefull to set Email notiffications for this job. To do this, navigate to Notifications tab, check the E-mail box, and select an operator. If there is no operator set on the server, consult this article that deals with Email notificitaions settings in detail. Finaly, set the condition for sending the email notification. Click OK to save changes.
The job is now set to run automatically on the defined schedule. To run the job imediately, right click on it in Object Explorer, and select Start Job at Step…
A success message is displayed as soon as all operations are completed.
Creating a custom index maintenance solution
Default parameter values should work well for the most low and medium traffic servers, but the values for low, medium and high fragmentation might be reconsidered for the servers that experience high traffic or use some kind of replication (log shipping, Availability Groups). It’s a fact that the index maintenance operations change data pages, and create records in the transaction log. This means that the transaction log can grow signifficantly in size after the index maintenance, that further results in larger transaction log backups, and requires more data to be copied and transferred over the network if some kind of replication is used. This understandably costs more resources: more disk space is required for the transaction log and its backups, and more bandwith is required for file copying. Furthermore, the default settings allow offline rebuilds on indexes with fragmentation as low as 5%. It is known that offline index rebuilds take the processed tables offline, and block all queries on the table until the rebuilding is completed.
This might be a problem for the databases with high traffic, so it would be wise to create a custom solution for this kind of scenario. For example, the values for FragmentationLevel1 and FragmentationLevel2 might be reconsidered. Generally, for high traffic databases, these values are usually set at 30% and 50%. In that case, all indexes with fragmentation less than 30% will not be processed. Additionally, the database selection can be changed, so that the job is run only on speciffic set of databases. If there is limited database maintenance window, the TimeLimit parameter can also be set.
Here is the example of the custom script:
@Databases = 'AdventureWorks2014, GenerateDB, Sales',
@FragmentationLevel1 = 30,
@FragmentationLevel2 = 50,
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@PageCountLevel = 500,
@TimeLimit = 3600
This query can be used as is to run the maintenance task, but to take the full advantage of this custom solution, it is best to include the script in SQL Server Agent Job. To create the job, perform the following steps:
In Object Explorer, right click on Jobs under the SQL Server Agent node. Click on New job in context menu.
In General tab of the New Job form, set the name, owner, category and description for the job.
In the Steps tab, click on New… button.
In the New Job Step form, specify the Step name. For the Type, select Transact-SQL script (T-SQL). In Command box, paste the custom maintenance script. Click OK to save the job step.
In the New Job form, navigate to the Schedules tab. Click on New… button to create a new schedule.
Set the name and frequency for the schedule, and click OK to save the schedule.
Optionally, navigate to the Notifications tab to set Email notifications for the job. Check the E-mail box, and select the operator and condition that will trigger the notification. Click OK to save the job settings.
To run the created job, locate it under the Jobs node in Object Explorer, right click on it, and select Start job at step… from the context menu.
The success message is generated upon the job completion.
Previous articles in this series:
- Ola Hallengren’s SQL Server Maintenance Solution – Installation and SQL Server Backup solution
- Ola Hallengren’s SQL Server Maintenance Solution – Database integrity check
- SQL Server Index and Statistics Maintenance
- Specify fill factor for an index
- How to set up email notifications for backup jobs in SQL Server
- Multiple methods for scheduling a SQL Server backup automatically - July 17, 2018
- Ola Hallengren’s SQL Server Maintenance Solution – Index and statistics maintenance - January 23, 2018
- Ola Hallengren’s SQL Server Maintenance Solution – Database integrity check - January 10, 2018