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'