SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Compress Everything


Compress Everything

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)

Group: Administrators
Points: 224988 Visits: 19638
Comments posted to this topic are about the item Compress Everything

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
TravisDBA
TravisDBA
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4522 Visits: 3069
Your own Brad McGehee put out an excellent presentation on compression in which he lays out the pros and cons on compression quite nicely. In that document he stated: "Other than through testing, it is hard to tell whether compression’s benefits exceeds its disadvantages." I tend to agree with that so far as well and am I am still not so sure I would just compress everything..:-D

[url=http://bradmcgehee.com/wp-content/uploads/presentations/SSC204--Using%20SQL%20Server%20Compresson%20to%20Boost%20Database%20Performance.pdf][/url]

"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
Miles Neale
Miles Neale
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5262 Visits: 1695
Of those zillion of bytes of data it would be interesting to determine what amount of that data is in relational databases incased in products like SQL Server. I would venture that the percentage of data in RDBMs format is not what most of us might think. There is a new technology being used that I believe is called noSQL being a data storage system that does not use an RDBMs.

With the need for larger more searchable data stores that are not the traditional row, field, table approach to a data model, new technologies are coming to the forefront. These data stores do now hold and will continue to hold in the future more data then we see in the traditional RDBMs. Since this technology is emerging and they are just starting to understand searching of the vast numbers of related clusters within a data store there is little chance that those stores will be encrypted over the short term.

That leaves us with the question of compression of data within an RDBMs only. And the key issue as I see it centers around if the speed of searching + the speed of retrieval + the speed of compression and or decompression is acceptable to the user. And if the complexity of performing the required activities by the programmer and DBA are not overly difficult. Others can answer that.

Should we compress, possibly or the classic answer of "It depends" might be appropriate.

M.

Not all gray hairs are Dinosaurs!
Steve Jones
Steve Jones
SSC Guru
SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)

Group: Administrators
Points: 224988 Visits: 19638
Miles Neale (9/18/2012)
Of those zillion of bytes of data it would be interesting to determine what amount of that data is in relational databases incased in products like SQL Server. I would venture that the percentage of data in RDBMs format is not what most of us might think. There is a new technology being used that I believe is called noSQL being a data storage system that does not use an RDBMs.



I would guess the vast majority of data is sitting in a file system, on a PC, server, cell phone, iPod, etc. Most of it isn't in a database.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
peter-757102
peter-757102
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2551 Visits: 2559
Steve Jones - SSC Editor (9/18/2012)
Miles Neale (9/18/2012)
Of those zillion of bytes of data it would be interesting to determine what amount of that data is in relational databases incased in products like SQL Server. I would venture that the percentage of data in RDBMs format is not what most of us might think. There is a new technology being used that I believe is called noSQL being a data storage system that does not use an RDBMs.



I would guess the vast majority of data is sitting in a file system, on a PC, server, cell phone, iPod, etc. Most of it isn't in a database.


And most of what is on file systems, is already compressed as those are mostly movies and audio files.

As for MS-SQL databases, as long as the lower priced versions do not offer it, it doesn't exist for the vast majority of the user base. I would assume that covers most of the data stored with SQL Server products.

Personally I would happily use compression on some kinds of data, but unfortunatly the primary type of data that comes to mind (blobs) can't be compessed in ány version of SQL Server (to my knowledge). This unfortunatly makes compression in SQL Server really not that usefull as blobs with auxialiary data, not required by the relational engine is what often consumes most of the space in big import tables for example. On the main active tables blobs are often used for user comments and verbose change logs. Thus in the area where compression would be most welcome, there is none available.

The one truely always beneficial compression is backup compression and that is restricted to certain versions, again, major a downer.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)

Group: Administrators
Points: 224988 Visits: 19638
peter-757102 (9/19/2012)


Personally I would happily use compression on some kinds of data, but unfortunatly the primary type of data that comes to mind (blobs) can't be compessed in ány version of SQL Server (to my knowledge). This unfortunatly makes compression in SQL Server really not that usefull as blobs with auxialiary data, not required by the relational engine is what often consumes most of the space in big import tables for example. On the main active tables blobs are often used for user comments and verbose change logs. Thus in the area where compression would be most welcome, there is none available.

The one truely always beneficial compression is backup compression and that is restricted to certain versions, again, major a downer.


Some of the third party products do compress blobs. SQL Storage Compress from Red Gate will compress them in the db, though I don't believe it does filestream data.

Disclosure: I work for Red Gate.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
quackhandle1975
quackhandle1975
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5173 Visits: 1244
As a production SQL DBA contracting for many clients I have found 3rd party compression tools totally invaluable. SQL 2008 compression is fine, but many of my clients aren't even using that version, all their critical systems are SQL2K/2K5 and space has become so bad the storage guys are now stopping any local sql backups! w00t And then when an IT director says we can't afford a SQL upgrade, but still demands something to be done about "all those damn databases" then it pays to be aware of what is out there.

On a personal level I like using Quest Litespeed, however I have used whatever the client has in place, such as Idera's SQL Safe backup or Red Gat's Storage Compress, but there are others. What is find it whatever the make and model it is how you use it. The more I have played with all the aforementioned products the more it has helped me on a day-to-day basis, they all have some really cool features.

A few years ago one IT director even asked me if the email was still working as all the "SQL Server: Disk Space Alert" emails had stopped firing after utilising a SQL compression tool enterprise wide. :-)

qh

Who looks outside, dreams; who looks inside, awakes. – Carl Jung.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search