Data compression techniques and trade offs

Techy News Blog » Data compression techniques and trade offs

Techy News Blog » Data compression techniques and trade offs — Sun, 30 Sep 2007 10:04:14 GMT

PingBack from http://www.artofbam.com/wordpress/?p=3966

Data compression e SQL Server 2008

SQL Server, BI and .NET — Sun, 30 Sep 2007 21:19:42 GMT

Dal team dello Storage Engine di SQL Server arrivano alcune notizie interessanti a proposito delle nuove

Data Compression: Why Do we need it?

SQL Server Storage Engine — Mon, 01 Oct 2007 00:17:17 GMT

As announced in Tech-Ed 2007, data compression is a new and exciting feature targeted to be available

re: Data compression techniques and trade offs

Michael Reinhard — Fri, 15 Feb 2008 18:35:18 GMT

By the way it would be really interesting to hear at first hand about effectiveness curve of using compression for storing various data. It's also interesting how compression impacts encryption. I absolutely agree here with you about the importance of compression for backup operations. I used to use scripts to apply external compression to SQL backup files before I copied them to storage, but that solution performed very poorly. Furthermore, such a solution usually prevents you from running fast and seamless operations on backup files because you have to involve a stand-alone decompressor and a script before performing every operation on that backup file you have created using this scheme. That was one of the reasons why we purchased Scriptlogic's LiteSpeed http://scriptlogic.com/products/litespeed/ . That seriously increased our performance thanks to a fantastic SQL 2005 database engine and techniques implemented in Litespeed. You needn't go far to get some examples. I've just backed up a mid-size database of around 1.5 gigs on SQL Express 2005 where it achieved a compression ratio of 77%. I am keen to see SQL Server 2008 in my environment. I think that it's one of those best technology cases where a pair of solutions from different vendors make a burning mixture that does the job the best way you'd have preferred. What I like in such tools like Litespeed is the ease of procedures you can perform using the intuitive and effectively built configuration controls. It's like with SQL Express' 2005 setup wizard. You need to select a couple of settings and you are done with the setup. It really was a surprise to me when I saw that a backup operation takes shorter time if you do it by applying compression on the fly. The high CPU speed of modern systems really does the trick. In your very first article you've told that "Well, if the data is compressed, you can fit more data in the same memory." Isn't it effective only in the case of asymmetric algorithms without? By the way, you said that the data is decompressed by row. That is it's JIT compressed. Do you use caching to somehow analyze the frequency of fetching rows? Or the mechanism works similarly to deflate implemented with NTFS compression? Does it have any problems with compressing large files http://support.microsoft.com/kb/927912 like NTFS does? If it is practically impossible I would be really great to be able to effectively use a combination of built-in SQL server compression and the compression applied with Litespeed. I've seen such problems with compressed NTFS files many times.That's why I decided that I leave production system uncompressed and apply compression only to the DBs that I backup.

re: Data compression techniques and trade offs

Sunil Agarwal — Mon, 18 Feb 2008 23:38:42 GMT

Thanks for your comment. Some asnwers here

(1) if data is encrpted before comrpession, you will not get much comrpession. TDE (a new featrure in SQL2008) encrupts after compression so you can have both compression and encryption

(2) SQL Native backup compression is a very competitive offering. I suggest that you compare with lite-speerd. Other point that I want to make is that backup is typically IO bound, So if you use data comrpession and say you are able to compress 50%, then your backup will finish in 1/2 the time.

(3) Data is kept compressed in the buffer pool and we 'decompress' only the requested columns, not the entire row or page. We don't cache 'decompressed data at this time. I did not understand your point on "Isn't it effective only in the case of asymmetric algorithms without"

(4) The compression/decompression happens at column/row/page level. The size of the file only matters because there are lot more pages to compresss but the effort is linear with respect to the number of pages. I recommend you try data compression and see the impact on your workload.

re: Data compression techniques and trade offs

kanchangehlot — Tue, 26 Feb 2008 07:56:05 GMT

Nice to read to such a nice thing in easy-to-understand language.I amn doing

a short project on information and entropy and I am interested in knowing

factors which limit the extent to compress data without any loss of information and

secondly how images are compressed under JPEG. Well ,I would like to tell you

That I am a Physics student , so please don't use engineering jargons.

re: Data compression techniques and trade offs

Sunil Agarwal — Tue, 26 Feb 2008 20:06:52 GMT

I would suggest looking at the web. I found the following links.

http://www.prepressure.com/library/compression_algorithms/jpeg

http://www.jpeg.org/jpeg/jpegls.html?langsel=en

thanks

re: Data compression techniques and trade offs

RajeshEMC — Wed, 22 Apr 2009 15:18:06 GMT

Hi Sunil,

I saw your articles ,it's Awesome .

I am facing some issue in data compression , My client having around 20 TB of data in SQL Server . The client need to do data Compression on this and need to make a SSAS Cube for SQL Reporting.

I just want to know where can have compression of Data's , It can be in SSAS level or SQL Server Database Level .

If it SSAS Level : How and What are the step should be consider ?

If it SQL Server Level : What the better solution for 20 TB Data Compression ?

NB: The entire data store into a single Table in SQL 2005.

Please help to achieve this.

Thanks,

Rajesh

re: Data compression techniques and trade offs

Sunil Agarwal — Fri, 24 Apr 2009 01:33:24 GMT

I saw your articles ,it's Awesome .

[sunila] thanks

I am facing some issue in data compression , My client having around 20 TB of data in SQL Server . The client need to do data Compression on this and need to make a SSAS Cube for SQL Reporting.

I just want to know where can have compression of Data's , It can be in SSAS level

[sunila] the data compression I described in not in SSAS...I am not sure if SSAS has its own compression

or SQL Server Database Level .

If it SSAS Level : How and What are the step should be consider ?

If it SQL Server Level : What the better solution for 20 TB Data Compression ?

[sunila] please look at the compression strategy part of the blog...if you have read-mostly data, we recommend PAGE compression

NB: The entire data store into a single Table in SQL 2005.

[sunila] not sure what you mean..looks like you have one dominant table...this is definitely a manageability challenge...Have you partitioned it?