October 3, 2011 at 10:08 am
[SOLVED]
Creating Indexes with "ONLINE = ON" repeatedly reduces compression effectiveness as significantly as 50%. I have made numerous copies and backups over time and restored them to various servers on same & different platforms. If i create or rebuild a indexes with ONLINE=ON, the compression ratio takes a serious hit.
For nonclustered indexes, I don't really find this to be a problem. use logic to drop then recreate said index, nobody's any more aware of the scenario.
For clustered indexes, I'd recommend being rather cognizant of timing for rebuilds. Unless, of course, you're OK with limited compression effectiveness.
This has -not- been confirmed by Microsoft support, YMMV. I have tested this theory rigorously with over 150GB of total index data. ONLINE = ON, I compress to just over 100GB. ONLINE = OFF, I compress to under 50GB. You decide what works best! 🙂
October 3, 2011 at 10:12 am
Thanks for the feedback. Did you post a connect item on this so that we may test & vote on it?
October 3, 2011 at 10:20 am
I have not, i mostly lurk the forum so I don't really know how it works 🙂
October 3, 2011 at 10:25 am
Here's the last one I posted, I'll give you an idea of what needs to be done / posted.
October 3, 2011 at 10:54 am
Submitted as BUG 692793
October 3, 2011 at 11:49 am
chandleya (10/3/2011)
Submitted as BUG 692793
For those of us who hate searching :w00t:
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply