Robert Seles

Ola Hallengren’s SQL Server Maintenance Solution – Index and statistics maintenance

January 23, 2018 by

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:

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:

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.

  • Databases

     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

  • Indexes

    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

  • AvailabilityGroups

    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:

    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:

    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.

  • PageCountLevel

    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

    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

    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.

  • FillFactor

    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

    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.

  • LOBCompaction

    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.

  • UpdateStatistics

    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.

  • OnlyModifiedStatistics

    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.

  • StatisticsSample

    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

    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.

  • PartitionLevel

    Specifies whether to maintain partitioned indexes on the partition level. The procedure accepts Y (yes) and N (no) values, former being the default.

  • MSShippedObjects

    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.

  • TimeLimit

    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.

  • Delay

    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.

  • WaitAtLowPriorityMaxDuration

    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.

  • WaitAtLowPriorityAbortAfterWait

    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

    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.

  • LogToTable

    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.

  • Execute

    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:

  1. In Object Explorer, expand the nodes: SQL Server Agent\Jobs. Right click on IndexOptimize – USER_DATABASES job, and select Properties.

  2. In the Job properties form, navigate to the Steps tab, select the first job step, and click on Edit.


  3. 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:

    1. Analyses the fragmentation, page count and fill factor of all indexes in all user databases on the server.
    2. 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.
    3. The value for fill factor is unchanged for each index.
    4. Indexes smaller than 1000 pages are not processed.
    5. The statistics is not updated.
    6. There is no delay between index commands.
    7. There is no time limit on command execution.
    8. 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:

    1. Select the IndexOptimize – USER_DATABASES job, and check its Properties. Navigate to Schedules tab, and click New… button

    2. 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.

    3. 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:

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:

  1. In Object Explorer, right click on Jobs under the SQL Server Agent node. Click on New job in context menu.

  2. In General tab of the New Job form, set the name, owner, category and description for the job.

  3. In the Steps tab, click on New… button.

  4. 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.

  5. In the New Job form, navigate to the Schedules tab. Click on New… button to create a new schedule.

  6. Set the name and frequency for the schedule, and click OK to save the schedule.

  7. 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:

References

Robert Seles
Latest posts by Robert Seles (see all)

Related posts:

  1. Maintaining SQL Server indexes
  2. Tips and tricks for SQL Server database maintenance optimization
  3. Removing the risk from important maintenance tasks in SQL Server
  4. Top 25 SQL interview questions and answers about indexes
  5. How to identify and resolve SQL Server Index Fragmentation
Indexes, Jobs, Maintenance

About Robert Seles

Robert is a SQL Server and IT enthusiast with many years of experience as a computer repair technician. He has a great passion for new technologies, science, video games, and medieval history. He also likes reading, writing, and learning new languages. Robert is currently working for ApexSQL LLC as Software Sales Engineer where he specializes database backups and indexes. View all posts by Robert Seles

168 Views