0

I have maintenance job on SQL Server 2012 Enterprise Edition that runs daily to check index fragmentation and reorganize or rebuild the index based on the percentage of fragmentation. I used this approach to improve performance and it worked perfectly for a long time. However, recently this approach has started to take too much time.

So, instead of using the above, I execute index rebuild. It completed with one-six of the time taken in the first approach.

Why did the first approach become slow over time, and why is the full index rebuild better?

EXECUTE dbo.IndexOptimize @Databases = 'MyDBname'
    , @FragmentationLow = NULL
    , @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'
    , @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'
    , @FragmentationLevel1 = 5
    , @FragmentationLevel2 = 30
    , @UpdateStatistics = 'ALL'
    , @OnlyModifiedStatistics = 'Y'
1

You don't state the steps you take in each case so it is difficult to give specific analysis but three generic explanations for some of the difference spring to mind:

  • Rebuilding is an offline operation unless you are using Enterprise Edition, so it takes a table lock and does all the work before letting any other processes touch it. This means the process can optimise for itself and not care about concurrency or otherwise dealing with changes that happen during the process.
    Reorganise is an online operation so it needs to be concerned with possible concurrent actions: optimising for concurrency often means doing more work than you otherwise would and if there is any concurrent activity it will contend with the process for IO bandwidth & throughput.
  • Rebuilding just throws away the existing index and rebuilds from the table data, reorganise is much more "clever" and moves & compacts existing pages which depending on the state of the page distribution, your I/O subsystem, how much of the index's pages are in RAM right now, and how much of the cluster/heap is in RAM, how fragmented the cluster/heap is on disk, if another index can be used to aid the rebuild, and a number of other factors, could make reorganise slower.
  • If you are not checking first (and just rebuilding) then you are skipping a chunk of the work that the first job does: you just rebuild instead of scan then rebuild or reorganise.

https://www.brentozar.com/archive/2013/09/index-maintenance-sql-server-rebuild-reorganize/ has a few other notes that you might find useful in understanding the difference more.

For an answer more specific to your particular case you'll need to update your question with more details of the maintenance plans that you are comparing.

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.