OLA's Maintenance scripts - should I be reorganizing?

Question

OLA's Maintenance scripts - should I be reorganizing?

krypto69

SSChampion

Points: 13604
More actions
August 8, 2024 at 2:29 pm

#4443217
Hi
These are my current params for nightly maintenance:
```
USE MASTER
EXECUTE dbo.IndexOptimize @Databases = 'MY USER DB',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REBUILD_ONLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE',
@FragmentationLevel1 = 25,
@FragmentationLevel2 = 50,
@SortInTempdb = 'Y',
@MaxDOP = 2
```
I have removed 'REORGANIZE' as per Jeff's thread here:
https://www.sqlservercentral.com/forums/topic/review-ola-hallengren-indexoptimize-parameters
Am I correct in removing the reorg?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jo Pattyn SSC-Dedicated Points: 32347 More actions · Answer 1

With modern storage I would skip index maintenance as Jef mentioned in that thread. So many layers and can be quite costly in the cloud

Most important is having correct statistics. I've kept the script but only to update statistics. Mentioned in https://www.sqlskills.com/blogs/erin/updating-statistics-with-ola-hallengrens-script/

I have 
@FragmentationLow nvarchar(max) = NULL,
@FragmentationMedium nvarchar(max) = NULL /*'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'*/,
@FragmentationHigh nvarchar(max) = NULL /*'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'*/,

but 

@UpdateStatistics nvarchar(max) = 'ALL',

krypto69 SSChampion Points: 13604 More actions · Answer 2

Thanks Jo.

I'm residential (local SAN - on premise) no cloud.

So the current recommendation is to not even do rebuilds?

frederico_fonseca SSCoach Points: 16154 More actions · Answer 3

krypto69 wrote:

Thanks Jo.
I'm residential (local SAN - on premise) no cloud.
So the current recommendation is to not even do rebuilds?

do them on a per table basis if deemed needed - I have an example of one table in one of my systems (just a few million rows) where a daily rebuild is the only way to fix the bad performance that arises from its usage - that or disable compression on the table. Stats rebuild on this particular case do not address the issue.

as for reorg - should ONLY be used for ColumnStore tables as it is the way to recover the space from deleted rows. so again its not a NEVER use - its a use on the specific case where it should be used.

ScottPletcher SSC Guru Points: 101098 More actions · Answer 4

Only do ONLINE rebuild if you really need it. It has additional overhead and is often not quite as efficient as packing data rows.

Data (page) compression is a great tool and I use it a lot. However, be aware that it makes rebuilds take much longer (like 3-5 times longer). So be even more careful of if/when you rebuild large tables that are page compressed.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".