Post reply

TDE and Page/Row level compression

  • July 26, 2014 at 5:33 am

    #288209

    I have a huge database 600+ GB and it is TDE enabled. I want to make database smaller. I found some tables are more than 1 million rows and one table cosumes 300+ GB .

    I need your suggestion if I can do Page compress for that 300+ GB table and few other ones? is there any script I can compress 5+ tables same time? what are the consequences since it's Prod database and TDE enabled.

    Thanks in advance

  • July 26, 2014 at 6:01 am

    #1732599

    smtzac (7/26/2014)

    I have a huge database 600+ GB and it is TDE enabled. I want to make database smaller. I found some tables are more than 1 million rows and one table cosumes 300+ GB .

    I need your suggestion if I can do Page compress for that 300+ GB table and few other ones? is there any script I can compress 5+ tables same time? what are the consequences since it's Prod database and TDE enabled.

    Thanks in advance

    First of all you will not see much of a benefit because of the nature of the encrypted data, there will be some but not huge.

    Compressing is simple, an alter table statement with DATA_COMPRESSION = PAGE is all there is to it. For compressing multiple tables at the same time, open multiple queries and issue a separate statement from each one.

    😎

  • July 26, 2014 at 1:11 pm

    #1732616

    Eirikur Eiriksson (7/26/2014)

    First of all you will not see much of a benefit because of the nature of the encrypted data, there will be some but not huge.

    Not so.

    True for compressed backups, not true for page compression. If a page is both compressed and encrypted, the compression is applied first (because pages are compressed on disk and in memory) and then the encryption (because the page will be encrypted on disk and decrypted in memory)

    So the process is that the compressed page is encrypted, not the encrypted page compressed.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 26, 2014 at 1:14 pm

    #1732617

    Before you apply compression to everything, test and make sure that the CPU overhead is acceptable. On page compression it can get nasty. Also don't compress indexes unless you expect a good compression ratio.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 26, 2014 at 2:02 pm

    #1732620

    GilaMonster (7/26/2014)

    Before you apply compression to everything, test and make sure that the CPU overhead is acceptable. On page compression it can get nasty. Also don't compress indexes unless you expect a good compression ratio.

    In my experience, even more so if combined with TDE, especially on marginally resourced systems it can be a tripping point.

    😎

  • July 26, 2014 at 3:07 pm

    #1732625

    Thank you much for you time and inputs

Viewing 6 posts - 1 through 5 (of 5 total)

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