January 29, 2014 at 9:43 pm
Comments posted to this topic are about the item Compression
Igor Micev,My blog: www.igormicev.com
January 29, 2014 at 11:08 pm
Good one.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
January 30, 2014 at 1:09 am
It is too much easy and logical!
😀
January 30, 2014 at 1:37 am
This was removed by the editor as SPAM
January 30, 2014 at 2:01 am
I would suggest to read
http://msdn.microsoft.com/en-us/library/bb964719.aspx
you will discover
"•Whether the database is compressed.
If the database is compressed, compressing backups might not reduce their size by much, if at all."
Maybe I have missed something with my poor understanding of the English language, but , according to me , it means that you must not hope a significant gain of space with a compressed backup on a compressed database.
I have done several tests with a Developer 2008 R2 edition 2 years ago and I have obtained a decrease of 1% which is ridiculous.
I recognize that I have only a Windows 7 desktop with "classical" disks. Maybe , on a "super" Windows Server 2012 with marvelous disks , the gain could be more visible, but if you have to pay twice ( or more ) to gain 20% of space , I am not sure that it is valuable solution ( less space is good but , if my remembrance is good , what a waste of time especially in case of disaster )
Have all a nice day
PS : I hope you will excuse my poor written English
January 30, 2014 at 2:12 am
I too agree with patrick, When we are compressing already compressed db we wont get much space in it. That is why i feel option C is correct
thanks
January 30, 2014 at 2:20 am
Thanks for the question!
It's pretty much the same as if you try to zip (or rar or whatever) an already compressed file... there's no much room for compression there! 😀
January 30, 2014 at 2:59 am
Thank goodness logic still prevails 😀
Good question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 30, 2014 at 3:24 am
pmadhavapeddi22 (1/30/2014)
I too agree with patrick, When we are compressing already compressed db we wont get much space in it. That is why i feel option C is correctthanks
Precisely. If one database is 50GB native and the other 50GB compressed, then you're going to get more compression when backing up the first database than when backing up the second. Hence the first backup will be smaller. There are ciircumstances in which C would be the correct answer - for example if your uncompressed backup contained mainly uncompressible LOB data - JPEG files, maybe. In that case, you wouldn't get much compression when backing it up.
John
January 30, 2014 at 5:07 am
Hello John Mitchell ,
Thanks for your reply which is confirming what I was thinking especially about LOB and maybe FILESTREAM ( and what about the FileTables ? )
Maybe the creator of the question could tell us the amount of memory he/she used for the tests. My own tests were done with 16 GB , but maybe with 64 or 128 GB ( or more ) , the results should be different. I am retired ( 67 years old ) and so I have not the same necessities about memory or disks or processors than a big company. So my tests may be not reflecting the usual state in the "work" world.
A little general reproach for the creators of this Question of Day : maybe it would be useful to indicate the version(s) of SQL Server which is(are) concerned by the question. It is not the st time I am seeing a possible problem in the "good answer". Even on the forums ( MSDN/TechNet/SQL Server Central ) it is difficult to obtain a full view of his/her SQL Server ( full edition, physical configuration ...).
Have a nice day
January 30, 2014 at 5:21 am
I think you're assuming this question is more complicated than it actually is. What it's actually saying is this: "I have two databases that take up the same size on disk. One is already compressed, one isn't. Which one will I be able to compress more when I back them up?".
As for the version, I think the convention is that you assume it's the most recent supported version if not explicitly stated.
John
January 30, 2014 at 6:21 am
patricklambin (1/30/2014)
Hello John Mitchell ,Thanks for your reply which is confirming what I was thinking especially about LOB and maybe FILESTREAM ( and what about the FileTables ? )
Maybe the creator of the question could tell us the amount of memory he/she used for the tests. My own tests were done with 16 GB , but maybe with 64 or 128 GB ( or more ) , the results should be different. I am retired ( 67 years old ) and so I have not the same necessities about memory or disks or processors than a big company. So my tests may be not reflecting the usual state in the "work" world.
A little general reproach for the creators of this Question of Day : maybe it would be useful to indicate the version(s) of SQL Server which is(are) concerned by the question. It is not the st time I am seeing a possible problem in the "good answer". Even on the forums ( MSDN/TechNet/SQL Server Central ) it is difficult to obtain a full view of his/her SQL Server ( full edition, physical configuration ...).
Have a nice day
Hi all, and thanks for the comments.
I was working on a system with a 32GB RAM, 24 cores, and enough disk space.
First I made a data (clustered+nonclustered indexes) compression for a database and I reduced its size from about 123GB to 50GB. Then I made a backup of that database. Further I had to make e backup of another database which was 50GB in size, but wasn't compressed. Then I noticed the difference in sizes of the backups (given in the answer of this QotD) for both databases.
A good remark is the case of databases with LOB data. Honestly I didn't get that in mind. It's difficult to find such scenario. However the reference does not mention a case of database with LOB data.
Regards,
IgorMi
Igor Micev,My blog: www.igormicev.com
January 30, 2014 at 7:16 am
Thanks for the question Igor. I thought it was pretty much common sense. Although I note that quite a few went for answer C. I assume this is mostly due to not knowing how compression works.
A simple way to understand it is that common occurrences of values are replaced with a shorter substitute. Here's a link that might give those not familiar with the process a quick overview: http://en.wikipedia.org/wiki/Data_compression
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
January 30, 2014 at 7:26 am
Koen Verbeeck (1/30/2014)
Thank goodness logic still prevails 😀Good question, thanks.
+1 logic.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
January 30, 2014 at 8:02 am
John Mitchell-245523 (1/30/2014)
I think you're assuming this question is more complicated than it actually is. What it's actually saying is this: "I have two databases that take up the same size on disk. One is already compressed, one isn't. Which one will I be able to compress more when I back them up?".As for the version, I think the convention is that you assume it's the most recent supported version if not explicitly stated.
John
Except who would do that? Since http://support.microsoft.com/kb/231347 states that it's not recommended or supported
so we're talking about page and row compression no? And if we are, then it falls into a total "it depends" status. Which tables have page and row compression on them? All of them?
This question is a crapshoot.
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply