December 16, 2009 at 7:46 am
Does SQL 2008 offer data compression for active data files (.mdf's)?
We're already compressing our SQL backup files (and seeing tremendous savings in both space and backup runtimes) It would be great to have the option to compress our .mdf's for our 500GB reporting DB.
thanks
December 16, 2009 at 8:00 am
No, but SQL Server 2008 does provide data compression at the ROW or PAGE level on a table by table basis. I am looking at this myself right now. I have compressed the top 10 tables in one of our databases and dropped the spaced used by 3 GB. The first 4 tables dropped the space used by 2 GB, the next 6 only reduced it by another 1 GB.
I am looking at PAGE compression at the moment.
December 16, 2009 at 8:08 am
As Lynn suggested, you can compress the data on a Page level or Row level not on a MDF File level.
What I read in an article was having a page level compression enabled more # of pages in the buffer and there by allocating more number of pages in Buffer for faster query retrievals.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 16, 2009 at 8:47 am
Data compression white paper: http://msdn.microsoft.com/en-us/library/dd894051.aspx
The compression applies to structures coming off disk, and in memory in some cases, so it can increase the IO rates that you appear to get.
December 16, 2009 at 9:02 am
Compression is only available for Enterprise.
Cool stuff though.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply