SQLTeam.com | Weblogs | Forums

Order of operations for Index Rebuild vs. Update Stats

Our Index job does:

  1. Update Stats
  2. Index Rebuilds/Re-orgs

Since Index Rebuilds/Re-orgs will also update that index's Stats, wouldn't it be better to do the Index Rebuild first since that should reduce the Update Stats requirements?

Note: for Update Stats, we don't do All, we do it based on the modification percentage.

Yes. Any index that is rebuilt will have stats with All update done on it.

It's a waste of time to redo stats before a rebuild, and both a waste of time and will make your statistics worse to redo statistics after a rebuild.

1 Like

I can't find the article I saw on stats but the article had a bit of proof that rebuilding stats makes index rebuilds go faster. I've never taken the time to prove that either way.

Also, using REORGANIZE is worse than futile. On a lot of indexes, especially the ones that you've lowered the Fill Factor on to try to avoid page splits, REORGANIZE actually causes and perpetuates page splits because it doesn't actually work the way most people think it does. It does NOT "follow the Fill Factor" like rebuild does. It only compresses data UP to the Fill Factor. Because it cannot make new pages, it WILL NOT clear the area above the Fill Factor and it compresses the pages at the worst time possible and that's when you need the free space to prevent fragmentation.

And, it's a lot tougher on the log file than has been advertised.... a WHOLE lot tougher.

In fact, REORGANIZE is the one of the major reasons why people think that Random GUIDs are fragmentation problem. They DO have the problem of being a lot bigger than an INT or a BIGINT but I've done an experiment where I simulated adding 100,000 rows Random GUID keyed rows per day (123 bytes wide) and, before the simulated year passed, I went 58 days inserting 100,00 rows per simulated day with less than 1% fragmentation.

If you're interested in seeing all that, help me out... Here's the same shameless beg I just provided on SQL Server Central ... even if you don't want to vote for me to speak, there's some awesome sessions to vote fore and attend.

Please help that SQL Server centric community and vote for your favorite sessions that you'd like to see during the even. There's a link at the top of the pages (on for the Americas and one for Europe) that has more details. Here's the link

Once you're done voting for your favorites, vote for mine. They're titled "Black Arts Index Maintenance - GUIDs v.s. Fragmentation - They're not the problem... WE ARE! " and I can assure you... if you haven't seen the presentation before, it'll knock your socks off because I use GUIDs to destroy more than 2 decades of multiple myths having to do with the supposed "Best Practices" of index maintenance, which actually aren't "Best Practices" and were never meant to be "Best Practices". It even says so right in the documentation and I prove it all and then show you how to make Random GUIDs (and other similar types of indexes) fragment free.

In the end, I demonstrate the results of inserting 100,ooo rows per day into a GUID keyed Clustered index for 58 days with less than 1% fragmentation, which also destroys the more than 2 decade myth of Random GUID fragmentation. I also demonstrate the real cause of your fragmentation problems with ever-increasing indexes and you get to see the tool I wrote to actually see what indexes look like.

1 Like

Thankyou Jeff. I did not know of these issues with re-org.

We currently have a re-org threshold and a (higher) rebuild threshold. We shall now consider getting rid of Re-orgs.