January 10, 2011 at 9:30 am
I attended a local users group recently and in the course of discussion it was mentioned that you needed to run an update statistics after doing an index reorg, but that you didn't need to do it after an index rebuild. I asked why, since it didn't make sense that rebuilding or reorganizing an index would change the distribution of the data at all - there would (taking a date field for example) be just as many '7/15/2008' values as before and just as many '1/1/2011' value as before and just as many values between them. The distribution and statistics doesn't change just because the place where they are stored on a page changes. Eventually the group decided they didn't know why, just that it was what they had always known to do.
So I did some research.
It appears that rebuilding an index does also update the statistics, but a reorg does not. So, my conclusion (and explanation for the "best practice") is that if you want to both reduce index fragmentation AND update statistics, doing an index rebuild will do both. If you choose to reorg the index and you want to accomplish both you must update statistics afterwards, it doesn't do it for you. The update statistics only needs to follow the reorg if you also wanted to update statistics, it doesn't necessarily have to follow since statistics haven't changed just because the index was reorganized.
Does that sound correct?
Thanks,
Chad
January 10, 2011 at 10:44 am
Correct but not complete.
An index rebuild updates the stats on that index with a fullscan. Makes sense, because SQL's reading the entire index to rebuild it. It does not update column statistics - statistics that are not associated with an index. If you want to update those, you need to do it manually.
It's not always necessary to manually update stats, there is the auto-update. It's not always sufficient though, especially on larger tables.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 10, 2011 at 11:26 am
That's correct and Gail gave a good explanation as always.
Thank You,
Best Regards,
SQLBuddy.
January 10, 2011 at 11:26 am
Thanks Gail. I appreciate the sanity check and the clarification. I think that the index and statistics maintenance often go hand-in-hand and that's where the idea to do either a rebuild or a reorg+stats came from. Although it's obvious, I didn't take the next step and realize that if you have a regular rebuild, you want to be selective in which stats you update (and not just bulk do everything) to avoid duplicating work.
Good timing too, since I'm giving a short presentation on Wednesday that includes a section on statistics.
Thanks,
Chad
January 10, 2011 at 11:29 am
sqlbuddy123 (1/10/2011)
That's correct and Gail gave a good explanation as always.Thank You,
Best Regards,
SQLBuddy.
Yup. I usually take Gail's stuff to the bank, I love it when she replies to one of my posts.
Thanks,
Chad
January 10, 2011 at 12:19 pm
Chad Crawford (1/10/2011)
Good timing too, since I'm giving a short presentation on Wednesday that includes a section on statistics.
If you'd like a copy of my deck from PASS 2009 (not to use, just to get ideas from) PM me an email address. Presentation title: Lies, damned lies and Statistics.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 10, 2011 at 1:01 pm
Thank you for the generous offer! My BS is in statistics so I cringe a little when I hear that quote, but I have to admit that even statisticians don't trust each others data. If you didn't calculate it yourself, it's always a little suspect.
PM sent.
Chad
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply