August 22, 2011 at 10:27 am
GilaMonster (8/22/2011)
Ninja's_RGR'us (8/22/2011)
Let's say that you run update stats but the distribution didn't really change, nor the min / max values.Do you still get a plan recompile at next run?
Yes.
Getting way off topic here....
Agreed, but this is resolved AFAIK unless the OP has proof that absolutely no reindex or dataload is going on here.
August 22, 2011 at 10:30 am
SanDroid (8/22/2011)
GilaMonster (8/22/2011)
Recompiles aren't logged.Besides, procedures aren't recompiled when the statistics update. They recompile next time they run if they used any of the statistics that changed.
Good to know. That is not very clear in the BOL.
It just says"
sp_updatestats updates only the statistics that require updating based on the rowmodctr information in the sys.sysindexes catalog view, thus avoiding unnecessary updates of statistics on unchanged rows.
sp_updatestats can trigger a recompile of stored procedures or other compiled code. However, sp_updatestats might not cause a recompile, if only one query plan is possible for the tables referenced and the indexes on them. A recompilation would be unnecessary in these cases even if statistics are updated.
That's explicitly for the sp_updatestats procedure, not for updating of statistics in general.
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
August 22, 2011 at 10:36 am
GilaMonster (8/22/2011)
SanDroid (8/22/2011)
GilaMonster (8/22/2011)
Recompiles aren't logged.Besides, procedures aren't recompiled when the statistics update. They recompile next time they run if they used any of the statistics that changed.
Good to know. That is not very clear in the BOL.
It just says"
sp_updatestats updates only the statistics that require updating based on the rowmodctr information in the sys.sysindexes catalog view, thus avoiding unnecessary updates of statistics on unchanged rows.
sp_updatestats can trigger a recompile of stored procedures or other compiled code. However, sp_updatestats might not cause a recompile, if only one query plan is possible for the tables referenced and the indexes on them. A recompilation would be unnecessary in these cases even if statistics are updated.
That's explicitly for the sp_updatestats procedure, not for updating of statistics in general.
You are right.
I should have explictitly mentioned updates using sp_updatestats with or without the RESAMPLE. Not an auto update of stats that happens very differently during the day.
I jus thought that was what we where talking about when you mentioned running it against a table with a Million rows etc...
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply