January 30, 2013 at 6:58 am
I freely admit to having gotten it wrong. 🙂
However, the question now becomes, why *doesn't* it compress seperately stored data? If you're using data compression, you NEED it, and if your application makes extensive use of seperate data, it would seem you would really need it. 🙂
January 30, 2013 at 8:08 am
roger.plowman (1/30/2013)
if your application makes extensive use of seperate data, it would seem you would really need it. 🙂
Yes - exactly why I thought this was a good question. I had to look it up on BOL but am really glad to know this now, sure it'll come in useful.
January 30, 2013 at 8:32 am
roger.plowman (1/30/2013)
However, the question now becomes, why *doesn't* it compress seperately stored data? If you're using data compression, you NEED it, and if your application makes extensive use of seperate data, it would seem you would really need it. 🙂
Just guessing, but I would imagine SQL doesn't compress off-row data because of its size--the time taken to uncompress it would severely impact server performance, and since a lot of off-row stuff will be things like binary JPEG files that don't compress much anyway, you might as well just miss it out. I'd be interested to know myself what the real reason is, though!
January 30, 2013 at 9:14 am
Koen Verbeeck (1/30/2013)
Nice question, which absolutely no trickery or ambiguity. Thanks!
I'll second that! I'd prefer if there was a way to compress off page data such as varchar(max), which to me seems to be pretty compressible, but at least I remembered that you presently can't.
January 30, 2013 at 9:53 am
I think a lot of people are getting this wrong because the question is a bit ambiguous. SQL Server cannot compress this data, but it can be NTFS compressed and handled by SQL Server just fine. The question just asked if it can be compressed, not specifically compressed by SQL Server.
January 30, 2013 at 1:26 pm
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 ?
January 30, 2013 at 3:43 pm
Yogeshwar Phull (1/29/2013)
Thanks for the question. BOL does describe this..
+1, and thanks!
January 30, 2013 at 11:46 pm
Thanks a lot for this good question. 🙂
January 31, 2013 at 2:09 am
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.
January 31, 2013 at 4:35 am
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.
January 31, 2013 at 9:54 am
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!
February 2, 2013 at 1:23 pm
+1
Strange though, no compression
February 4, 2013 at 11:36 am
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[/url]
Learn Extended Events
February 11, 2013 at 5:44 pm
+1 - thanks for the question. I got it wrong (guessed) but learned something.
Cheers,
Andre Ranieri
February 22, 2013 at 2:26 am
interesting and useful one !
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply