2

I have an 800GB SQL Server Enterprise database with very bad performance. So, based on a few scripts I found online I'm creating a job on my SQL database to maintain our indexes. Most of my tables have a clustered index and 3 or 4 non-clustered.

  • Does it make any difference the order in which the indexes are being rebuild/reorganized?
  • Is it the same if I first reorganize the clustered index and then the nonclustered ones or if I do it the other way?
  • 3
    Are you sure that fragmentation is your problem ? See this : Why Defragmenting Your Indexes Isn’t Helping with Brent Ozar youtube.com/watch?v=iEa6_QnCFMU – Sabin Bio Sep 10 '17 at 5:22
  • 1
    Order doesn't matter for rebuild/reorganize (but does for drop/create). Fragmentation is unlikely to be to root cause of severe performance problems; I'd first look for index and query tuning opportunities. – Dan Guzman Sep 10 '17 at 13:26
1

Does it make any difference the order in which the indexes are being rebuild/reorganized?

As commented by @Dan Guzman it does not matter. But if your rebuilding and free disk space is limited I suggest you start from small to bigger ones (among the one qualify based on whatever criteria you are using). Reason explained here:

Disk Space Requirements for Index DDL Operations.

Very good tips for other considerations.

Should You Rebuild or Reorganize Indexes on Large Tables? (Dear SQL DBA Episode 19) by Kendra Little.

Is it the same if I first reorganize the clustered index and then the nonclustered ones or if I do it the other way?

Yes it same because the clustered keys, which are the record locators in the NC indexes, aren’t changed because you rebuild the index.

1

On SQL Server 2000, if you rebuilt your non-unique clustered index, all non-clustered indexes based on that non-unique clustered index would be automatically rebuilt. That's because the uniquifier changed during the rebuild.

This is not the case on SQL Server 2005 and higher, or if they are just being reorganized.

For index and statistics maintenance automation, I recommend using Ola Hallengren's Maintenance Solution, to manage this for you. You might also use Minionware's Reindex, also free.

Side note: updating statistics regularly is (in my experience) more beneficial from a time and resource point of view than rebuilding indexes. Thus, I would do daily statistics updates, and defer heavy index maintenance to a longer schedule (weekly or monthly) if required. The level of fragmentation will dictate this, because rebuilding may not be necessary.

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.