| Author |
Topic |
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
 Posted - 2009-01-29 : 17:07:25
|
| 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
11752 Posts |
Posted - 2009-01-29 : 17:36:15
|
| 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 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.1 out! |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2009-01-29 : 19:33:00
|
| Is your friend using table compression in the database, or just backup compression?CODO ERGO SUM |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-01-29 : 23:30:04
|
| 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. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2009-01-30 : 17:04:27
|
| 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 |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2009-01-30 : 18:20:39
|
| 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 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.1 out! |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
saurabhsrivastava
Posting Yak Master
216 Posts |
Posted - 2009-02-03 : 22:52:30
|
| There is downside of compression; CPU overhead. Dont overlook that if you're implemention it on Production systems. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-02-03 : 23:46:40
|
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" |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2009-02-04 : 05:48:23
|
| just poping in to say that they use page compresion also.___________________________________________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.1 out! |
|
|
|
adsingh82
Starting Member
20 Posts |
Posted - 2014-05-13 : 02:16:39
|
| 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 threadunspammedRegards,Alwyn.M |
|
|
|
|
2008 Data Compression