Compression

  • Igor Micev

    SSC-Dedicated

    Points: 33109

    Comments posted to this topic are about the item Compression

    Igor Micev,
    My blog: www.igormicev.com

  • free_mascot

    One Orange Chip

    Points: 27168

    Good one.

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • Carlo Romagnano

    SSC-Insane

    Points: 21809

    It is too much easy and logical!

    πŸ˜€

  • This was removed by the editor as SPAM

  • patricklambin

    SSCrazy Eights

    Points: 9964

    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

  • pmadhavapeddi22

    SSCertifiable

    Points: 5306

    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

  • SQLDoubleG

    Hall of Fame

    Points: 3090

    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! πŸ˜€

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    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

  • John Mitchell-245523

    SSC Guru

    Points: 148360

    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 correct

    thanks

    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

  • patricklambin

    SSCrazy Eights

    Points: 9964

    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

  • John Mitchell-245523

    SSC Guru

    Points: 148360

    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

  • Igor Micev

    SSC-Dedicated

    Points: 33109

    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

  • Thomas Abraham

    SSChampion

    Points: 10761

    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

  • Hany Helmy

    SSChampion

    Points: 13321

    Koen Verbeeck (1/30/2014)


    Thank goodness logic still prevails πŸ˜€

    Good question, thanks.

    +1 logic.

  • mtassin

    SSC-Insane

    Points: 23096

    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.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

Viewing 15 posts - 1 through 15 (of 38 total)

You must be logged in to reply to this topic. Login to reply