January 25, 2011 at 8:07 am
We are using CDC to log changes.
Inserts on the cdc tables happen quite frequently, resulting in millions of rows for some tables.
A report shows the indexes are fragmented and need to be rebuild.
My question is: Data is deleted every 24h, so does it make sense so rebuild the indexes? Maybe just before an extraction happens?
Thanks for suggestions.
January 25, 2011 at 8:19 am
That's an interesting question. The delete should mark pages as free, but whether they get freed or not before you insert again is something I don't know.
How long does an extraction take? How long does an index rebuild take?
January 25, 2011 at 8:37 am
Extraction depends heavily how often we did it and wich period of the year. Sometime it took just to long.
We did no rebuild yet. I was not sure if it will have an effect as the data after 48h is completely changed. But probably, if it does not take too much time to rebuild it would make sense to to it every time before an extraction. right? (Two time a day.)
What are your experiences with index rebuilds on tables with constant inserts? Note that some of these tables grow up to 2GB with millions of rows (only when there is high season....).
Thanks
January 25, 2011 at 8:46 am
rebuilds take time and space. They can lock the table, so that can be an issue for you. If there aren't LOB tables (don't think CDC has this) , then they can be online w/ Enterprise Edition.
The clustered key is what matters most. I'm not a CDC expert, so I'd have to go dig this up. With constant inserts, and little deletes/updates, the inserts could be happening at the end of the table, which means that you shouldn't be too fragmented. If they happen throughout the table, then you can be heavily fragmented.
January 25, 2011 at 8:55 am
For the cdc tables there are only inserts. Thats why it seems really strange to mee that the indexes are fragmented. clustered index: 48 %, non-clustered index: 99,54 %
February 1, 2011 at 2:34 am
Any other suggestions?
- Does a table with "inserts-only" need index rebuilds? consider that it is some sort of statistics raw data, extracted every night (old data is cleaned up)
February 1, 2011 at 2:56 am
Even i felt the same in one of my projects with many extent fragmentation too. Use SHOWCONTIG and check the fragmentation.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply