August 8, 2024 at 2:29 pm
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?
August 8, 2024 at 3:13 pm
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',
August 8, 2024 at 4:49 pm
Thanks Jo.
I'm residential (local SAN - on premise) no cloud.
So the current recommendation is to not even do rebuilds?
August 8, 2024 at 4:55 pm
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.
August 8, 2024 at 7:59 pm
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".
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply