Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Data compression


Data compression

Author
Message
dinolg
dinolg
SSC-Enthusiastic
SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)

Group: General Forum Members
Points: 136 Visits: 94
MSDN clearly notes that "FILESTREAM filegroups can be on compressed volumes." Everyone seems to mention BOL, but fails to mention a quote, or any kind of reference. Perhaps BOL is just wrong. It certainly wouldn't be the first time.

How are so many people oblivious to compression options ? Are the people answering "No" the people who don't use compression ?

The answer is only "No" if you use the most basic form of compression, where rows over 8K or so disallow the basic compression.

There are also more advanced options, such as using a CLR procedure to compress your data, or doing compression outside SQL Server. All of these answer the question "Is data compression available for the data that is stored separately?", but we don't even need to look that far. Obviously the poster and about half the respondents read about row level compression, and stopped there.

The correct answer is YES. Yes, Data compression is available for large-value data types when the data is stored off row, and in many different ways. Doesn't anyone compress their large data types ? What do people actually compress if not the biggest things, the things that (usually) benefit MOST from compression ?
Revenant
Revenant
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6093 Visits: 4764
Yogeshwar Phull (1/29/2013)
Thanks for the question. BOL does describe this..

+1, and thanks!
sqlnaive
sqlnaive
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3781 Visits: 2774
Thanks a lot for this good question. :-)
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8766 Visits: 11719
roger.plowman (1/30/2013)
However, the question now becomes, why *doesn't* it compress seperately stored data?

I started to reply here, but as the length of my reply grew I realised I probably better put it on my blog. So here is the link: http://sqlblog.com/blogs/hugo_kornelis/archive/2013/01/31/why-does-sql-server-not-compress-data-on-lob-pages.aspx.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
sipas
sipas
Say Hey Kid
Say Hey Kid (684 reputation)Say Hey Kid (684 reputation)Say Hey Kid (684 reputation)Say Hey Kid (684 reputation)Say Hey Kid (684 reputation)Say Hey Kid (684 reputation)Say Hey Kid (684 reputation)Say Hey Kid (684 reputation)

Group: General Forum Members
Points: 684 Visits: 702
dinolg (1/30/2013)
Everyone seems to mention BOL, but fails to mention a quote, or any kind of reference.


The BOL reference is in the explanation.

I thought it was pretty clear that the question referred to data compression as implemented by SQL server.

Hugo Kornelis (1/31/2013)
roger.plowman (1/30/2013)
However, the question now becomes, why *doesn't* it compress seperately stored data?

I started to reply here, but as the length of my reply grew I realised I probably better put it on my blog. So here is the link: http://sqlblog.com/blogs/hugo_kornelis/archive/2013/01/31/why-does-sql-server-not-compress-data-on-lob-pages.aspx.

Great explanation - read this everyone.
Miles Neale
Miles Neale
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2750 Visits: 1694
Hugo Kornelis (1/31/2013)
I started to reply here, but as the length of my reply grew I realized I probably better put it on my blog...


Great question with good discussion!

Hugo, again thank you for blogging this. As do excellent work!

Not all gray hairs are Dinosaurs!
@Cassie
@Cassie
Say Hey Kid
Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)Say Hey Kid (683 reputation)

Group: General Forum Members
Points: 683 Visits: 123
+1
Strange though, no compression
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22794 Visits: 18261
EZ-PZ



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Andre Ranieri
Andre Ranieri
Mr or Mrs. 500
Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)

Group: General Forum Members
Points: 503 Visits: 379
+1 - thanks for the question. I got it wrong (guessed) but learned something.

Cheers,

Andre Ranieri
jfgoude
jfgoude
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1182 Visits: 299
interesting and useful one !
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