Sql-server – How to configure Ola Hallengren IndexOptimize script to run against a subset of tables

ola-hallengrensql serversql-server-2016

I'm using Ola Hallengren IndexOptimize script against a SQL 2016 database that is 7 TB in size, with over 300,000 tables. I only have a 6 hour window each night to manage indexes. I'm using the timelimit parameter to stop the job after 6 hours.

The problem is, every night the index job starts at the beginning of the indexes alphabetically, and only gets through about the same 4,000 tables.

What can I do to get the index job to cover all the indexes in the database? Perhaps by creating multiple jobs, one for each night of the week that do a subset of indexes? Or is there a way to have the job start back up the next day where it previously left off?

All the tables are in the same database schema. This is a vendor-supplied database, I'm unable to make changes to the database schema.

Thanks in advance for any guidance.

My current job steps are as follows:

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 = 10, 
@FragmentationLevel2 = 40,
@UpdateStatistics = 'ALL', 
@OnlyModifiedStatistics = 'Y', 
@PartitionLevel = 'N', 
@MaxDOP = 0,
@SortInTempdb = 'Y',
@TimeLimit = 21600,
@LogToTable = 'Y'

Best Answer

Don't defrag your indexes every night. You don't need to anymore on modern hardware (especially with solid-state storage with zero seek times). For daily maintenance, you only need to update modified statistics, and even then you only need to do this when you notice performance degradation (i.e. check what your monitoring system tells you).

Per Ola's site, you can do something like this:

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

The other thing you can do, for when you do need to defragment indexes (if for example you have deleted a large amount of data), is using SQL Server's Service Broker to set up the maintenance plan for multiple tables in a batch.

That article is in-depth and difficult to summarise in this reply, but it comes down to creating an asynchronous queue, that fires Agent jobs in parallel.

Related Question