SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Other SQL Server 2008 Topics
 2008 Data Compression
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 01/29/2009 :  17:07:25  Show Profile  Reply with Quote
I have been looking into using SQL Server 2008 Enterprise Edition to take advantage of the data compression feature. We have about 2 TB of data in a rapidly growing database, and testing indicates we will get about 70% compression for most tables, so this looks like a very attractive option to keep storage requirements under control.

Has anyone else implemented row or page level compression in a 2008 database? Were there any issues?

Also, are there any features of SQL Server 2008 that will not be available if we install it on Windows 2003, instead of Windows 2008?






CODO ERGO SUM

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 01/29/2009 :  17:36:15  Show Profile  Visit spirit1's Homepage  Reply with Quote
i have seen compression in use at a friends setup however i haven't got one.
as he tells me they sped their up backup from whole night to 1 hour. approx 1 Tb of data if i remember correctly.

win server 2008 doesn't have any extra features compared to 2003 sql server wise that i know of.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

Edited by - spirit1 on 01/29/2009 17:36:31
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 01/29/2009 :  19:33:00  Show Profile  Reply with Quote
Is your friend using table compression in the database, or just backup compression?



CODO ERGO SUM
Go to Top of Page

robvolk
Most Valuable Yak

USA
15688 Posts

Posted - 01/29/2009 :  23:30:04  Show Profile  Visit robvolk's Homepage  Reply with Quote
I did some tests on much smaller databases (100 GB or smaller) but I was very happy with both data and backup compression. I've found that data compression doesn't work well with GUID's. Even though it's dictionary based, and I have tables with lots of redundant values, indexes are sometimes larger when compression is turned on. I usually got 30% on most tables with GUIDs, never better than 50%. I changed my data around and substituted ints where I could, and got 60-70% savings. My guess is that it can't compress the GUID type like it can for ints and dates, and that's where much of the advantage lies.

The backup compression is nearly identical in speed and compression ratio with SQL Litespeed and Red Gate, both at their medium settings. I've found LiteSpeed's higher compression ratios (6 or higher) to take much longer and were not worth it. I give Microsoft serious props for hitting the sweet spot on that, it's one setting and it's just about perfectly balanced.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 01/30/2009 :  17:04:27  Show Profile  Reply with Quote
Did you do row level or page level compression in your testing?

How was query performance with compressed tables compared to uncompressed data?

I agree on the LiteSpeed compression. I always use the lowest compression level for backups. The higher compression levels take much longer to run, and we didn't see much additional compression.



CODO ERGO SUM

Edited by - Michael Valentine Jones on 01/30/2009 17:05:17
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 01/30/2009 :  18:20:39  Show Profile  Visit spirit1's Homepage  Reply with Quote
i'm certain that they used backup compression. not sure about the page and row compression, but i think they used that too. i'll ask.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

Edited by - spirit1 on 01/30/2009 18:20:57
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 01/31/2009 :  00:18:53  Show Profile  Visit SwePeso's Homepage  Reply with Quote
See how compression works in SQL Server 2008.
http://weblogs.sqlteam.com/peterl/archive/2008/10/09/Excerpt-from-The-Compression-Session.aspx



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

saurabhsrivastava
Posting Yak Master

USA
216 Posts

Posted - 02/03/2009 :  22:52:30  Show Profile  Reply with Quote
There is downside of compression; CPU overhead. Dont overlook that if you're implemention it on Production systems.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 02/03/2009 :  23:46:40  Show Profile  Visit SwePeso's Homepage  Reply with Quote
The CPU overhead is almost negligable.
1-2 percent CPU. And all compression happens when reading and writing to disk anyway.
Pages are always uncompressed in memory. I/O is much more expensive than a few percent overhead of compression/uncompression.




E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 02/04/2009 :  05:48:23  Show Profile  Visit spirit1's Homepage  Reply with Quote
just poping in to say that they use page compresion also.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!
Go to Top of Page

adsingh82
Starting Member

India
20 Posts

Posted - 05/13/2014 :  02:16:39  Show Profile  Reply with Quote
Applying data compression on a huge table will consume more time. It is wise to partition the table first and apply compression on each and every partition. This will take less time as you can apply the compression in parallel on all the partitions at once.

To know the advantages and disadvantages of data compression please refer the below thread
unspammed
Regards,
Alwyn.M
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000