6

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'
9

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.

| improve this answer | |
  • thanks for the suggestions. even the update stats only job takes over 12 hours, but i'll see if i can split up that job somehow, and investigate the Service Broker options. – johna May 14 at 18:19
  • @MSSQLServerDBA's suggestion is useful too. You can also leverage Query Store to focus on the statistics that are causing performance regressions. Don't feel beholden to just one or two techniques to help you out, because you aren't dealing with the average SQL Server database. – Randolph West May 14 at 22:17
3

What philcart did should help you 'start back up the next day where it previously left off':

I run the IndexOptimise in two SQL Agent jobs.

In the first job that runs weekly, I pull out all the indexes that need optimisation using the following,

@FragmentationLow = NULL,  @FragmentationMedium =
'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', 
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', 
@FragmentationLevel1 = 5,  @FragmentationLevel2 = 30,  @LogToTable =
'Y',  @Execute = 'N'; 

This captures the Index rebuild/defrag commands in the CommandLog table. After running the IndexOptimise command, the "EndTime" column is set to null for all the records just inserted.

Then on a daily basis, within our maintenance window, the second job just uses a simple cursor to pull out each command that has an EndTime of NULL and run it if the time window hasn't elapsed.

DECLARE @sqlCmd nvarchar(max);  DECLARE @maxDuration int = 60; 
DECLARE @startTime datetime = GETDATE();  DECLARE @totalCmds int =
(SELECT COUNT(1) FROM [dbo].[CommandLog] WHERE [EndTime] IS NULL); 
DECLARE @currCount int = 0;  DECLARE @cmdSample nvarchar(100);  IF
@totalCmds > 0  BEGIN -- we have work to do  DECLARE cmds CURSOR
FAST_FORWARD FOR SELECT [ID],[Command] FROM [dbo].[CommandLog] WHERE
[EndTime] IS NULL ORDER BY [ID] DESC  OPEN cmds  FETCH NEXT FROM cmds
INTO @logID, @sqlCmd  WHILE (@@FETCH_STATUS = 0 AND
(DATEDIFF(MI,@startTime,GETDATE()) < @maxDuration))  BEGIN  SET
@currCount += 1;  SET @cmdSample = LEFT(@sqlCmd,60)+'...';  UPDATE
[dbo].[CommandLog] SET [StartTime] = GETDATE() WHERE [ID] = @logID;
RAISERROR('Excuting IndexOptimize command for ID:%i (%i of %i) -
%s',10,1,@logID, @currCount, @totalCmds,@cmdSample) WITH NOWAIT;  EXEC
sp_executeSql @command = @sqlCmd;  UPDATE [dbo].[CommandLog] SET
[EndTime] = GETDATE() WHERE [ID] = @logID;  RAISERROR('Command
complete for ID:%i (%i of %i) - %s',10,1,@logID, @currCount,
@totalCmds,@cmdSample) WITH NOWAIT;  FETCH NEXT FROM cmds INTO @logID,
@sqlCmd  END  IF (@currCount < @totalCmds)  BEGIN 
RAISERROR('IndexOptimize finishing due to elapsed time, executed %i
commands out of %i',10,1,@currCount, @totalCmds) WITH NOWAIT;  END 
ELSE  BEGIN  RAISERROR('All commands executed within allowed time
window',10,1) WITH NOWAIT;  END  CLOSE cmds  DEALLOCATE cmds  END --
we have work to do  ELSE  BEGIN -- we have work to do 
RAISERROR('IndexOptimize has nothing to execute',10,1) WITH NOWAIT; 
END -- we have work to do 
| improve this answer | |
  • thank you! I'll see if i can make something like this work. – johna May 14 at 18:20
0

The @Indexes parameter allows you to specify individual indexes or tables. We use it on some very large tables and then exclude that from the main index job for that database.

Read more here: https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html, particularly the Indexes section.

You can also create a meta table to populate the list of tables, and schedule and then try running that through a cursor.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.