Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Westley
Posting Yak Master
229 Posts |
 Posted - 2007-11-01 : 22:41:38
|
| Hi all, From BOL, it stated that stats got updated after index rebuild as update stats is a side product from index rebuild. The question I have is, will the stats be always be fullscan? So far from what I notice, it was, but is there some document from MS somewhere that stated that? I cannot seems to find it anywhere, does anyone know? For stats columns, do you guys normally create/include them in an index? So there will not be any stats column flowing around. As rebuild index do not update the stats for those columns, how do you guys maintain it at the moment?Thanks |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-01 : 23:20:15
|
| You can set db options 'auto create stats' and 'auto update stats'. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-02 : 05:07:18
|
| Note that if you need Defrag, rather than Reindex, you will need to update the stats on those separately.Kristen |
|
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2007-11-15 : 21:19:29
|
| Kristen, When you rendex, it stated that it will do stats update, if you only do defrag, you will have to do the stats update manually (via another process). The question I have was, when you do reindex, does it do update stats with FULLSCAN? As auto-update stats is not doing it on fullscan, it just pick a random sample number which "it" thinks is best. By the way rmiao, auto-update stats is not a very good option to turn on, just letting you know :) |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-15 : 23:26:19
|
| >> auto-update stats is not a very good option to turn on, just letting you know :)You should double check what Microsoft said on this.By the way, dbcc dbreindex doesn't update stats with FULLSCAN. |
|
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2007-11-16 : 02:00:38
|
| it doesn't update with fullscan? ok, as from checking the result, it looks like it does, but i cannot find any doc that stated that.auto-update stats, i believe there will be 2 way of looking at it, it does help since it will update the stats for you when its really out-dated (base on that formula), but in prac, normally it will happen during the most busy time as that is when most changes happens and most likely it will hit that point to trigger the auto-update. For small DB it good to have it, as update stats doesn't take long anyway, but for large DB, update stats with a sample of like 1% is useless and might cause your execution plan wrong (i got that before). When you want to fix it by running a fullscan of a large table, you will feel the pain :) |
|
|
|
|
|
|
|
|
index rebuild & stats