7

It's been awhile since I needed to tend a SQL Server, but have used Ola's solution with great success in the past. I was told by a DBA today that when you pass options like these for IndexOptimize:

@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'

...that IndexOptimize will not only determine what is possible for the given situation (e.g. INDEX_REBUILD_ONLINE can't be attempted on SQL Server Standard), but that if IndexOptimize runs into an error, it will retry using the next thing in the list from left to right. For example, if an online reindexing failed for some index, it would then try an offline reindexing.

My recollection is that it would NOT work that way and only log an error in the CommandLog table. I cracked the rather large IndexOptimize proc open to check and didn't see anything like that, but maybe I missed it.

Can anyone here verify the behavior for me?

mustaccio
  • 28,207
  • 24
  • 60
  • 76
Eric Tarasoff
  • 171
  • 1

2 Answers2

9

public service announcement

Since you said:

It's been awhile since I needed to tend a SQL Server

I would make the recommendation that you not schedule index maintenance based on logical fragmentation the way you may have done so in the past.

The bulk of the advice that people follow (5%/30%) is from the bad old days of spinning disk storage, where random I/O that resulted in a physical penalty from drive movement could really bog things down.

On modern storage, like SSDs and flash, and for reads from RAM (Random Access Memory), there is no longer the same level of performance gain from putting data pages back in order because that physical movement penalty does not exist.

There may be some wisdom in measuring physical fragmentation (empty space on data pages), but Ola's script doesn't do that. See:

In place of all that index churning that just burns out your fancy storage faster, I'd recommend using Ola's scripts to just update statistics.

If you've reached this point and think my advice is still crazy, ask yourself this: since you started buying laptops with SSDs, how many times have you opened Windows Disk Defragmenter to speed up a slow machine?

Erik Reasonable Rates Darling
  • 45,549
  • 14
  • 145
  • 532
8

It's hard to prove a negative. But perhaps we can find some evidence.

Documentation

From the documentation, at https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html:

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.

That documentation -- found under the FragmentationLow, FragmentationMedium, and FragmentationHigh parameters -- agrees with your recollection, and the first part of your friend's assertion. If Ola's index maintenance script retried by going to the next option if an attempt failed, this would be where I would expect to find it documented.

Code

From the downloadable IndexOptimize.sql, which creates the IndexOptimize stored procedure:

In the section that begins with the comment of "Which actions are allowed?", we find a number of checks to see which of the INDEX_REORGANIZE, INDEX_REBUILD_ONLINE, and INDEX_REBUILD_OFFLINE are allowed. Those checks include, but are not limited to, the SQL Version, the Engine Edition, and the current index type. It then picks which of those actions to use, builds the command, and executes the command, with a line like this:

EXECUTE @CurrentCommandOutput = dbo.CommandExecute @DatabaseContext = @CurrentDatabaseName, @Command = @CurrentCommand, ...

It then checks for an error, and sets the return code.

SET @Error = @@ERROR
IF @Error <> 0 SET @CurrentCommandOutput = @Error
IF @CurrentCommandOutput <> 0 SET @ReturnCode = @CurrentCommandOutput

That's pretty much it. Nothing about a retry. There are a few places in the procedure that use the Try/Catch syntax, but not around the index rebuilding actions.

Verdict

Your recollection is correct. Your friend's assertion -- that if the reindexing operation runs into an error, it will retry using the next thing in the list -- is not supported by the evidence.

Doug Deden
  • 2,171
  • 10
  • 14