OLA's Maintenance scripts - should I be reorganizing?

  • 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?

     

     

  • 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',

     

  • Thanks Jo.

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

     

    So the current recommendation is to not even do rebuilds?

     

  • 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.

  • 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