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.
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
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.
Pfft. Offline Rebuilds. What’d we pay 7k a core for? Power Pivot?
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?
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.)
Should we run Update Stats on the dbs if we have Auto Update Stats on?
Absolutely! Auto Update Stats will only rebuild stats when a certain volume of data has changed. If you have a lot of data, that can a long time (it’s 20% of the data plus 500 rows). Ola Hallengren’s maintenance scripts can handle this for you.
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