April 17, 2012 at 1:03 pm
We rebuild our indexes online. Since data and indexes are huge and our tables are partitioned, we rebuild them on partition level. The ULTER INDEX REBUILD has STATISTICS_NORECOMPUTE option which we don't use. Therfore, statistics is updated every time we rebuild indexes, because default is OFF.
However, as far as I know, statistics is updated on entire index, not on partition level. And if we rebuild multiple partitions for an index, the statistics is updated as many times ? Or how it works in this case ?
Thanks
April 17, 2012 at 1:12 pm
Rebuilding an index automatically rebuilds the stats on it.
If you reorganize an index, you might need to rebuild stats, but not if you rebuild the index.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 17, 2012 at 1:32 pm
Yes, I know this. But my question is this: for example we have 100 partitions and we need to rebuild index only on 10 of them. We issue ALTER INDEX REBUILD PARTITION XX - 10 times.
Does entire index update statistics 10 times ? Does it do exactly same thing 10 times in a row ?
April 17, 2012 at 3:59 pm
I doubt it goes as many table scans as there are partitions, but you can check logical reads with profiler.
Do they match partition size or whole table size, and you will be certain.
Statistics are by default unfiltered and generated for the data of the whole table, no matter is it partitioned or not.
It is recommended to create multiple filtered statistics that match your partitions.
You don't have to create them for all partitions, just for the active ones.
June 20, 2012 at 11:07 am
SQL Guy 1 (4/17/2012)
Yes, I know this. But my question is this: for example we have 100 partitions and we need to rebuild index only on 10 of them. We issue ALTER INDEX REBUILD PARTITION XX - 10 times.Does entire index update statistics 10 times ? Does it do exactly same thing 10 times in a row ?
In my experience when a table is partitioned and the indexes are aligned, the statistics are not automatically rebuilt.
If the table is NOT partitioned, then yes, they are rebuilt and they are rebuilt with 100% sampling which is a lot better than using autoupdate statistics.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply