May 12, 2009 at 5:40 pm
hello i have a job that i created with the maintenance wizard to rebuild all my indexes at night. But now i have the senior DBA teling me that is also necessary to create another job to run the sp_updatestats after? i always thought that once you reindex all the indexes it was not necessary to update the stats since they were updated with fullscan. Can someone tell me what is the gain by running sp_updatestats?
May 12, 2009 at 6:32 pm
If you do a full index rebuild, it has sampled all the data, just like an update statistics with a full scan. If you then run the sampled statistics update, you're actually degrading the statistics on the index. So no, whoever said that is wrong.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 13, 2009 at 1:25 am
Hi Grant,
What about statistics which are not related with indexes?
May 13, 2009 at 5:29 am
Off the top of my head, I'm not sure. I don't think they would be affected negatively by running after the index rebuild.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 13, 2009 at 5:53 am
That is exactly what he said,so would it be better to do add a second job to do this after or integrated into the maintenance package to rebuild the indexes? I read the sp only updates the stats needed...
May 13, 2009 at 6:23 am
No, running updatestats updates any and all statistics on the target you select. So if you need updated non-index statistics, great, but you're still going to get a sampled update on the statistics on the index where there was a full scan. If anything, just run the update statistics first. Then rebuild the indexes. You should be fine then.
You might want to track down a maintenance script (there are lots of them available) that checks the fragmentation level first, then defrags or rebuilds as needed.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 13, 2009 at 8:35 am
then if i use one of those scripts to that checks the fragmentation level first, then defrags or rebuilds as needed, what about the sp_updatestats, it won't be needed or the same thing, it should be run before then the new script?
May 13, 2009 at 8:59 am
If you're running it in conjunction, run it before and then run the new script.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 13, 2009 at 9:18 am
Here is a pretty interesting post on the topic from a different perspective. I have seen in my own update stats jobs "no update needed" and the job moves on. Since sp_updatestats only updates the needed statistics, I am not sure if it really matters anymore if you update before or after. Any thoughts?
Actually, the more I think about it, it makes more sense to me to do it after because it would mean the stats would not get rebuilt twice.
May 13, 2009 at 10:40 am
Hmmm... It's my understanding that sp_updatestats does nothing more than cursor through all the tables in the system running UPDATE STATISTICS against them. I just rechecked that in the Books Online (not a perfect resource, but generally very reliable).
I did a little searching around. According to Paul Randal, a source I'm inclined to trust on internals, the sampling default of the update statistics function will cause the recently rebuilt indexes to be resampled. Sometimes this is not an issue. Sometimes it is.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 13, 2009 at 11:11 am
Here is what I found in BOL...
"sp_updatestats updates only those statistics that require updating based on the rowmodctr information in the sys.sysindexes compatibility view; therefore, preventing unnecessary updates of unchanged items."
The biggest question is how do they determine what needs to be updated based on the rownodctr. For example, how many rows exactly or is it based on a percentage. I am going to disect the stored proc and see if I can see where they are determining that.
Paul's blog wasn't specifically talking about sp_updatestats. I agree that if you force an update that would not be a good idea, but if only the ones that need to be updated get updated, I would say that after an index rebuild would be completely fine.
May 13, 2009 at 11:31 am
It looks like if a single row is changed, sp_updatestats will update the statistics. I even had an Insert statement fail that caused the statistics to be updated. This is intersting to know. So, I guess it depends on the usage of your database. If you know that the tables are not being updated between the Index rebuild and sp_updatestats then after would be okay. If not, I would have to lean toward updating them before the Index rebuild (I guess this would be the best thing to do just to be on the safe side).
BTW - Good Book. I haven't finished it yet, but I have enjoyed it so far.
May 13, 2009 at 12:06 pm
Ah, I didn't see the bit about the rowmodctr. And I forgot all about it. However, since it only takes a single modification to make a statistic available for update... I'd tend to shy from running it after rebuilding an index, even still. Especially since you're running sp_updatestats which goes against the whole database and may not be hitting the table with the rebuilt index any time soon. It just doesn't seem like the kind of juggling that ought to go on inside a well maintained system.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 23, 2009 at 10:47 am
thank you all for your opinions.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply