Rebuild or Reorganize? How to Set Up Index Maintenance in SQL Server (video)

Index maintenance is confusing. Should you use maintenance plans or customized SQL Agent jobs? Join Kendra to learn the difference between index ‘rebuild’ and ‘reorganize’ commands, and how to avoid the pitfalls of maintenance plans in SQL Server.

Interested in trying out sp_BlitzTrace™ yourself? Download it in our free SQL Server download pack.

You can also read more about rebuild vs reorganize, or sign up for our online course, Database Maintenance Starter Kit for $149.

Previous Post
What Is Commodity Hardware?
Next Post
The Most Confusing Words, Phrases, and Acronyms in SQL Server

8 Comments. Leave new

  • Barring some catastrophe, I’ll be on the webinar tomorrow. But I do have a question about table size being a barometer for Rebuild vs. Reorg. I’ve noticed that as tables approach 100GB in size, Reorgs become less efficient than just rebuilding. For instance, a 90GB table that I killed a 7 hour Reorg on ran a Rebuild in just a few minutes. Have you noticed similar behavior?

    Thanks,
    Erik

    Reply
    • I haven’t tested specifically around that size mark, but i have noticed that reorg can be much slower — especially if you have enterprise edition and can use parallelism for online rebuilds. Offline rebuild can also be much faster than online rebuild (but, of course, is offline). And different maxdops can dramatically change performance for rebuilds as well, depending on hardware.

      Reply
  • We are building a script that combines DBCC and Index rebuilds at the same time. We go through the list of tables and first do index rebuild then DBCC on the same table. When the table is in cache we might as well do DBCC on it. Will we save IO?

    Reply
    • CHECKDB doesn’t just validate pages in memory– what if the page on disk was corrupt, but hadn’t been read into memory?

      I would also be careful about making maintenance so complicated that you miss something. Breaking CHECKDB into pieces is complex and if you have a limited maintenance window, you could easily miss checking some important tables.

      (Sorry for the brief response, just dashing in to answer some comments between meetings.)

      Reply
  • Should we run Update Stats on the dbs if we have Auto Update Stats on?

    Reply
  • Hi enjoyed your index rebuild video. Learned much!

    You said that the ALTER INDEX statement is basically an INSERT Votes Select * From Votes.

    How does that even work? If duplicates aren’t allowed via the PK, it would die, and if there was no PK you’d have double the data?

    Thx
    Tony

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}