Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Compress Everything Expand / Collapse
Author
Message
Posted Monday, September 17, 2012 11:40 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:55 PM
Points: 31,278, Visits: 15,736
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
Post #1360566
Posted Tuesday, September 18, 2012 8:39 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:03 PM
Points: 1,334, Visits: 3,069
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..

[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. ..."
Post #1360836
Posted Tuesday, September 18, 2012 9:45 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 10:15 AM
Points: 2,464, Visits: 1,551
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!
Post #1360864
Posted Tuesday, September 18, 2012 9:52 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:55 PM
Points: 31,278, Visits: 15,736
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
Post #1360868
Posted Wednesday, September 19, 2012 3:05 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, November 10, 2014 3:14 AM
Points: 334, Visits: 2,272
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.
Post #1361208
Posted Wednesday, September 19, 2012 7:32 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:55 PM
Points: 31,278, Visits: 15,736
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
Post #1361335
Posted Tuesday, October 30, 2012 8:54 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 7:17 AM
Points: 2,445, Visits: 1,027
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! 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


SQL 2K acts like a spoilt child - you need to coax it round with lollipops.
Post #1378823
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse