February 9, 2012 at 2:11 pm
How long should it take to turn on Data Compression on a 300GB table?
I'd like to use Data Compression on this table at the page level, and it looks like I can save about 50% on storage space. My concern is how long will this take to create? I will also be releasing the saved space back to the file system so I will have to run a Reorg on the indexs following the compression. We do a weekly rebuild of our index's and this task currently takes about 1/2 a day. So my concern here is how is there a calculation as to how I can estimate (give or take a bit) how long setting up compression may take. I know server size/memory and all the other variants will impact this, I'm just looking for a way to get a approximation.
The wizard is very straight forward on how to do it, I'm just worried it will chug for hours and go outside my maintenance window which is only 24 hours and I can absolutely not go over.
Any suggestions are appreciated.
We are running SQL Server 2008 R2, O/S 2008 R2, 3 Node Cluster
Thanks,
Kimberly Killian
Sr. DBA / DB Engineer
www.sitedataview.com
Follow me on Twitter
Follow me on Facebook
February 9, 2012 at 2:28 pm
It will take approximately the same amount of time to compress an index as it does to rebuild it.
February 9, 2012 at 2:35 pm
Kim Killian-SiteDataView (2/9/2012)
I will also be releasing the saved space back to the file system so I will have to run a Reorg on the indexs following the compression.
A reorg won't release space back to the OS. To do that you'll need a shrink. So you're looking at rebuild index (it will take about the time it usually does, maybe a little more, maybe a little less), then a database shrink (could take anything from seconds to hours) and then another index reorg or rebuild to remove the fragmentation that the shrink introduced
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
February 9, 2012 at 2:35 pm
I'm going for the entire table and Index at the page level.
Thanks,
Kimberly Killian
Sr. DBA / DB Engineer
www.sitedataview.com
Follow me on Twitter
Follow me on Facebook
February 9, 2012 at 2:36 pm
yes sorry I didn't say shrink first
Compess---->Shrink---->Reorg
Thanks,
Kimberly Killian
Sr. DBA / DB Engineer
www.sitedataview.com
Follow me on Twitter
Follow me on Facebook
February 9, 2012 at 2:36 pm
Kim Killian-SiteDataView (2/9/2012)
I'm going for the entire table and Index at the page level.
Cluster and nonclustered? You've tested and ensured that the CPU overhead of page compression is acceptable and not going to negativly impact the application performance?
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
February 9, 2012 at 2:41 pm
Both. And no not yet. I'm planning on running tests after I figure out how long it will take to create.
Thanks,
Kimberly Killian
Sr. DBA / DB Engineer
www.sitedataview.com
Follow me on Twitter
Follow me on Facebook
February 9, 2012 at 2:42 pm
There's no hard and fast answer for how long it will take to compress that table. There are too many factors which would impact the time such as the CPU available on your SQL Server, the MAXDOP settings, memory, I/O performance and server usage when you attempt to perform the compression. A good estimate of the time taken to initially compress the indexes would be the time that it takes to rebuild them now (however this is probably not accurate).
Rather than use the wizard you should manually execute the scripts just to be aware what is happening. The GUI can do some strange things at times (such as create an entirely new copy of your table) and you want to avoid this.
Your best course of action to get an approximation would be to restore the database to another server and actually run the compression. This should give you a good idea.
Given (as you say) that you'll be performing a shrink against the database after performing the compression I'd recommend a rebuild rather than a reorg. All of this should be an online operation (unless you are using LOB data types).
February 9, 2012 at 2:46 pm
Thanks for the comments!
Thanks,
Kimberly Killian
Sr. DBA / DB Engineer
www.sitedataview.com
Follow me on Twitter
Follow me on Facebook
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply