SQL Server Index and Statistics Maintenance

IndexOptimize is the SQL Server Maintenance Solution’s stored procedure for rebuilding and reorganizing indexes and updating statistics. IndexOptimize is supported on SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017, SQL Server 2019, SQL Server 2022, Azure SQL Database, and Azure SQL Managed Instance.

Download

Download MaintenanceSolution.sql. This script creates all the objects and jobs that you need. You can also download the objects as separate scripts. The SQL Server Maintenance Solution is available on GitHub.

License

The SQL Server Maintenance Solution is free.

Parameters

Databases

Select databases. The keywords SYSTEM_DATABASES, USER_DATABASES, ALL_DATABASES, and AVAILABILITY_GROUP_DATABASES are supported. The hyphen character (-) is used to exclude databases, and the percent character (%) is used for wildcard selection. All of these operations can be combined by using the comma (,).

Value	Description
SYSTEM_DATABASES	All system databases (master, msdb, and model)
USER_DATABASES	All user databases
ALL_DATABASES	All databases
AVAILABILITY_GROUP_DATABASES	All databases in availability groups
USER_DATABASES, -AVAILABILITY_GROUP_DATABASES	All user databases that are not in availability groups
Db1	The database Db1
Db1, Db2	The databases Db1 and Db2
USER_DATABASES, -Db1	All user databases, except Db1
%Db%	All databases that have “Db” in the name
%Db%, -Db1	All databases that have “Db” in the name, except Db1
ALL_DATABASES, -%Db%	All databases that do not have “Db” in the name

FragmentationLow

Specify index maintenance operations to be performed on a low-fragmented index.

Value	Description
INDEX_REBUILD_ONLINE	Rebuild index online.
INDEX_REBUILD_OFFLINE	Rebuild index offline.
INDEX_REORGANIZE	Reorganize index.
INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE	Rebuild index online. Rebuild index offline if online rebuilding is not supported on an index.
INDEX_REBUILD_ONLINE,INDEX_REORGANIZE	Rebuild index online. Reorganize index if online rebuilding is not supported on an index.
INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE	Reorganize index. Rebuild index online if reorganizing is not supported on an index. Rebuild index offline if reorganizing and online rebuilding are not supported on an index.
NULL	Do not perform index maintenance. This is the default for a low-fragmented index.

An online index rebuild or an index reorganization is not always possible. Because of this, you can specify multiple index-maintenance operations for each fragmentation group. These operations are prioritized from left to right: If the first operation is supported for the index, then that operation is used; if the first operation is not supported, then the second operation is used (if supported), and so on. If none of the specified operations are supported for an index, then that index is not maintained.

IndexOptimize uses the SQL Server ALTER INDEX command: REBUILD WITH (ONLINE = ON) to rebuild indexes online, REBUILD WITH (ONLINE = OFF) to rebuild indexes offline, and REORGANIZE to reorganize indexes.

FragmentationMedium

Specify index maintenance operations to be performed on a medium-fragmented index.

Value	Description
INDEX_REBUILD_ONLINE	Rebuild index online.
INDEX_REBUILD_OFFLINE	Rebuild index offline.
INDEX_REORGANIZE	Reorganize index.
INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE	Rebuild index online. Rebuild index offline if online rebuilding is not supported on an index.
INDEX_REBUILD_ONLINE,INDEX_REORGANIZE	Rebuild index online. Reorganize index if online rebuilding is not supported on an index.
INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE	Reorganize index. Rebuild index online if reorganizing is not supported on an index. Rebuild index offline if reorganizing and online rebuilding are not supported on an index. This is the default for a medium-fragmented index.
NULL	Do not perform index maintenance.

FragmentationHigh

Specify index maintenance operations to be performed on a high-fragmented index.

Value	Description
INDEX_REBUILD_ONLINE	Rebuild index online.
INDEX_REBUILD_OFFLINE	Rebuild index offline.
INDEX_REORGANIZE	Reorganize index.
INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE	Rebuild index online. Rebuild index offline if online rebuilding is not supported on an index. This is the default for a high-fragmented index.
INDEX_REBUILD_ONLINE,INDEX_REORGANIZE	Rebuild index online. Reorganize index if online rebuilding is not supported on an index.
INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE	Reorganize index. Rebuild index online if reorganizing is not supported on an index. Rebuild index offline if reorganizing and online rebuilding are not supported on an index.
NULL	Do not perform index maintenance.

FragmentationLevel1

Set the lower limit, as a percentage, for medium fragmentation. The default is 5 percent. This is based on Microsoft’s recommendation in Books Online.

IndexOptimize checks avg_fragmentation_in_percent in sys.dm_db_index_physical_stats to determine the fragmentation.

FragmentationLevel2

Set the lower limit, as a percentage, for high fragmentation. The default is 30 percent. This is based on Microsoft’s recommendation in Books Online.

IndexOptimize checks avg_fragmentation_in_percent in sys.dm_db_index_physical_stats to determine the fragmentation.

MinNumberOfPages

Set a size, in pages; indexes with fewer number of pages are skipped for index maintenance. The default is 1000 pages. This is based on Microsoft’s recommendation.

IndexOptimize checks page_count in sys.dm_db_index_physical_stats to determine the size of the index.

MaxNumberOfPages

Set a size, in pages; indexes with greater number of pages are skipped for index maintenance. The default is no limitation.

IndexOptimize checks page_count in sys.dm_db_index_physical_stats to determine the size of the index.

SortInTempdb

Use tempdb for sort operations when rebuilding indexes.

Value	Description
Y	Use tempdb for sort operations when rebuilding indexes.
N	Do not use tempdb for sort operations when rebuilding indexes. This is the default.

The SortInTempdb option in IndexOptimize uses the SORT_IN_TEMPDB option in the SQL Server ALTER INDEX command.

MaxDOP

Specify the number of CPUs to use when rebuilding indexes. If this number is not specified, the global maximum degree of parallelism is used.

The MaxDOP option in IndexOptimize uses the MAXDOP option in the SQL Server ALTER INDEX command.

FillFactor

Indicate, as a percentage, how full the pages should be made when rebuilding indexes. If a percentage is not specified, the fill factor in sys.indexes is used.

The FillFactor option in IndexOptimize uses the FILLFACTOR option in the SQL Server ALTER INDEX command.

PadIndex

Apply the percentage of free space that the fill factor specifies to the intermediate-level pages of the index.

Value	Description
Y	Apply the percentage of free space that the fill factor specifies to the intermediate-level pages of the index.
N	The intermediate-level pages of the index are filled to near capacity. This is the default.

The PadIndex option in IndexOptimize uses the PADINDEX option in the SQL Server ALTER INDEX command.

LOBCompaction

Compact pages that contain large object (LOB) columns, when reorganizing indexes.

Value	Description
Y	Compact pages that contain LOB columns, when reorganizing indexes. This is the default.
N	Do not compact pages that contain LOB columns, when reorganizing indexes.

The LOBCompaction option in IndexOptimize uses the LOB_COMPACTION option in the SQL Server ALTER INDEX command.

UpdateStatistics

Update statistics.

Value	Description
ALL	Update index and column statistics.
INDEX	Update index statistics.
COLUMNS	Update column statistics.
NULL	Do not perform statistics maintenance. This is the default.

IndexOptimize uses the SQL Server UPDATE STATISTICS command to update statistics.

OnlyModifiedStatistics

Update statistics only if any rows have been modified since the most recent statistics update.

Value	Description
Y	Update statistics only if any rows have been modified since the most recent statistics update.
N	Update statistics regardless of whether any rows have been modified.

IndexOptimize checks modification_counter in sys.dm_db_stats_properties, in SQL Server 2008 R2 starting with Service Pack 2 and in SQL Server 2012 starting with Service Pack 1. In earlier versions it checks rowmodctr in sys.sysindexes. For incremental statistics it checks modification_counter in sys.dm_db_incremental_stats_properties.

StatisticsModificationLevel

Specify a percentage of modified rows for when the statistics should be updated. Statistics will also be updated when the number of modified rows has reached a decreasing, dynamic threshold, SQRT(number of rows * 1000).

IndexOptimize checks the columns modification_counter and rows in sys.dm_db_stats_properties, in SQL Server 2008 R2 starting with Service Pack 2 and in SQL Server 2012 starting with Service Pack 1. In earlier versions it checks the columns rowmodctr and rowcnt in sys.sysindexes. For incremental statistics it checks the columns modification_counter and rows in sys.dm_db_incremental_stats_properties.

StatisticsSample

Indicate, as a percentage, how much of a table is gathered when updating statistics. A value of 100 is equivalent to a full scan. If no value is specified, then SQL Server automatically computes the required sample.

The StatisticsSample option in IndexOptimize uses the SAMPLE and FULLSCAN options in the SQL Server UPDATE STATISTICS command.

StatisticsResample

Update statistics with the most recent sample.

Value	Description
Y	Update statistics with the most recent sample.
N	Let SQL Server automatically compute the required sample. This is the default.

The StatisticsResample option in IndexOptimize uses the RESAMPLE option in the SQL Server UPDATE STATISTICS command.

You cannot combine the options StatisticsSample and StatisticsResample.

PartitionLevel

Maintain partitioned indexes on the partition level. If this parameter is set to Y, the fragmentation level and page count is checked for each partition. The appropriate index maintenance (reorganize or rebuild) is then performed for each partition.

Value	Description
Y	Maintain partitioned indexes on the partition level. This is the default.
N	Maintain partitioned indexes on the index level.

MSShippedObjects

Maintain indexes and statistics on objects that are created by internal SQL Server components.

Value	Description
Y	Maintain indexes and statistics on objects that are created by internal SQL Server components.
N	Do not maintain indexes and statistics on objects that are created by internal SQL Server components. This is the default.

IndexOptimize checks is_ms_shipped in sys.objects to determine whether an object was created by an internal SQL Server component.

Indexes

Select indexes. If this parameter is not specified, all indexes are selected. The ALL_INDEXES keyword is supported. The hyphen character (-) is used to exclude indexes, and the percent character (%) is used for wildcard selection. All these operations can be combined by using the comma (,).

Value	Description
ALL_INDEXES	All indexes
Db1.Schema1.Tbl1.Idx1	The index Idx1 on the object Schema1.Tbl1 in the database Db1
Db1.Schema1.Tbl1.Idx1, Db2.Schema2.Tbl2.Idx2	The index Idx1 on the object Schema1.Tbl1 in the database Db1 and the index Idx2 on the object Schema2.Tbl2 in the database Db2
Db1.Schema1.Tbl1	All indexes on the object Schema1.Tbl1 in the database Db1
Db1.Schema1.Tbl1, Db2.Schema2.Tbl2	All indexes on the object Schema1.Tbl1 in the database Db1 and all indexes on the object Schema2.Tbl2 in the database Db2
Db1.Schema1.%	All indexes in the schema Schema1 in the database Db1
%.Schema1.%	All indexes in the schema Schema1 in all databases
ALL_INDEXES, -Db1.Schema1.Tbl1.Idx1	All indexes except the index Idx1 on the object Schema1.Tbl1 in the database Db1
ALL_INDEXES, -Db1.Schema1.Tbl1	All indexes except indexes on the object Schema1.Tbl1 in the database Db1

TimeLimit

Set the time, in seconds, after which no commands are executed. By default, the time is not limited.

Delay

Set the delay, in seconds, between index commands. By default, there is no delay.

WaitAtLowPriorityMaxDuration

The time, in minutes that an online index rebuild operation will wait for low priority locks.

The WaitAtLowPriorityMaxDuration option in IndexOptimize uses the WAIT_AT_LOW_PRIORITY and MAX_DURATION options in the SQL Server ALTER INDEX command.

WaitAtLowPriorityAbortAfterWait

The action that will be performed after an online index rebuild operation has been waiting for low priority locks.

Value	Description
NONE	Continue waiting for locks with normal priority.
SELF	Abort the online index rebuild operation.
BLOCKERS	Kill user transactions that block the online index rebuild operation.

The WaitAtLowPriorityAbortAfterWait option in IndexOptimize uses the WAIT_AT_LOW_PRIORITY and ABORT_AFTER_WAIT options in the SQL Server ALTER INDEX command.

Resumable

Specify whether an online index operation is resumable.

Value	Description
Y	Index operation is resumable.
N	Index operation is not resumable. This is the default.

The Resumable option in IndexOptimize uses the RESUMABLE option in the SQL Server ALTER INDEX command.

AvailabilityGroups

Select availability groups. The keyword ALL_AVAILABILITY_GROUPS is supported. The hyphen character (-) is used to exclude availability groups, and the percent character (%) is used for wildcard selection. All of these operations can be combined by using the comma (,).

Value	Description
ALL_AVAILABILITY_GROUPS	All availability groups
AG1	The availability group AG1
AG1, AG2	The availability groups AG1 and AG1
ALL_AVAILABILITY_GROUPS, -AG1	All availability groups, except AG1
%AG%	All availability groups that have “AG” in the name
%AG%, -AG1	All availability groups that have “AG” in the name, except AG1
ALL_AVAILABILITY_GROUPS, -%AG%	All availability groups that do not have “AG” in the name

LockTimeout

Set the time, in seconds, that a command waits for a lock to be released. By default, the time is not limited.

The LockTimeout option in IndexOptimize uses the SET LOCK_TIMEOUT set statement in SQL Server.

LockMessageSeverity

Set the severity for lock timeouts and deadlocks.

Value	Description
10	This is an informational message.
16	This is an error message. This is the default.

StringDelimiter

Specify the string delimiter. By default, the string delimiter is comma.

DatabaseOrder

Specify the database order.

Value	Description
NULL	The order that the databases have been specified in. Then ascending by the database name. This is the default.
DATABASE_NAME_ASC	Ascending by the database name
DATABASE_NAME_DESC	Descending by the database name
DATABASE_SIZE_ASC	Ascending by the database size
DATABASE_SIZE_DESC	Descending by the database size

DatabasesInParallel

Process databases in parallel.

Value	Description
Y	Process databases in parallel.
N	Process databases one at a time. This is the default.

You can process databases in parallel by creating multiple jobs with the same parameters, and add the parameter @DatabasesInParallel = 'Y'.

ExecuteAsUser

Change the execution context to a user. The user can be dbo or any other user. The user has to exist in all databases that you are working with.

The ExecuteAsUser option in IndexOptimize uses the EXECUTE AS command in SQL Server.

LogToTable

Log commands to the table dbo.CommandLog.

Value	Description
Y	Log commands to the table.
N	Do not log commands to the table. This is the default.

Execute

Execute commands. By default, the commands are executed normally. If this parameter is set to N, then the commands are printed only.

Value	Description
Y	Execute commands. This is the default.
N	Only print commands.

Examples

A. Rebuild or reorganize all indexes with fragmentation on all user databases

EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30

B. Rebuild or reorganize all indexes with fragmentation and update modified statistics on all user databases

C. Update statistics on all user databases

EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL'

D. Update modified statistics on all user databases

EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'

E. Rebuild or reorganize all indexes with fragmentation on all user databases, performing sort operations in tempdb and using all available CPUs

EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@SortInTempdb = 'Y',
@MaxDOP = 0

F. Rebuild or reorganize all indexes with fragmentation on all user databases, using the option to maintain partitioned indexes on the partition level

G. Rebuild or reorganize all indexes with fragmentation on all user databases, with a time limit so that no commands are executed after 3600 seconds

H. Rebuild or reorganize all indexes with fragmentation on the table Production.Product in the database AdventureWorks

EXECUTE dbo.IndexOptimize
@Databases = 'AdventureWorks',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@Indexes = 'AdventureWorks.Production.Product'

I. Rebuild or reorganize all indexes with fragmentation except indexes on the table Production.Product in the database AdventureWorks

J. Rebuild or reorganize all indexes with fragmentation on all user databases and log the results to a table

Execution

You can execute the stored procedures from T-SQL job steps, or from CmdExec job steps with sqlcmd and the -b option.

SQL Server version	Job type
SQL Server 2008 and 2008 R2 on Windows	CmdExec job steps with sqlcmd and the -b option
SQL Server 2012, 2014, 2016, and 2017 on Windows	T-SQL job steps or CmdExec job steps with sqlcmd and the -b option
SQL Server 2017 on Linux	T-SQL job steps
Azure SQL Database Managed Instance	T-SQL job steps

There is a problem in SQL Server 2005, 2008 and 2008 R2 that a T-SQL job step stops executing after the first error. Use CmdExec job steps with sqlcmd and the -b option on these versions.

You can use the MaintenanceSolution.sql script to create the jobs. It will create CmdExec job steps with sqlcmd on SQL Server 2005, 2008 and 2008 R2, and T-SQL job steps on later versions.

Menu

GitHub

Awards