Post reply

Compression Script on existing tables in databases that handles the alters and indexes and tells how much savings?

  • October 11, 2017 at 10:10 am

    #402588

    Hi, is there a tried and true generic script available that I can run that will go through each database I have, and go through and tell me about the savings of compression if applied, and then go forth and compress all compressible columns and deal with the alter statements and indexes? Thanks

  • October 11, 2017 at 1:13 pm

    #1963249

    Tons.  See the following link, which will also teach another essential skill in the process.

    http://bfy.tw/EQFy

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 11, 2017 at 1:53 pm

    #1963259

    This is only the estimate, I'm not only wanting to see that, but more importantly actually compress columns in the tables in the databases if not locked

  • October 11, 2017 at 2:09 pm

    #1963263

    Why couldn't you just use the same method played against a table of results generated by the first run to control which columns get compressed?

    Another search will scare up code to compress all tables in a database.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 11, 2017 at 2:24 pm

    #1963269

    I was able to find a script I was looking for to do the alter statements for the compressable columns and indexes on this website, I'm testing it out on a small test DB. Thanks

    http://www.sqlservercentral.com/scripts/COMPRESS/69005/

  • October 11, 2017 at 2:35 pm

    #1963273

    Great.  My next question is, have you actually tested in your environment to see if compression is actually going to help?  On the few large tables I've tested in my work environment, I've found that compression actually slowed things down.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 12, 2017 at 8:19 am

    #1963374

    Jeff Moden - Wednesday, October 11, 2017 2:35 PM

    Great.  My next question is, have you actually tested in your environment to see if compression is actually going to help?  On the few large tables I've tested in my work environment, I've found that compression actually slowed things down.

    This is my next step, I hear that it can make no difference or harm performance, and I'm not hurting so much on space as I need a performance boost and don't want to compress just for the sake of compressing.

  • October 12, 2017 at 10:59 am

    #1963438

    quinn.jay - Thursday, October 12, 2017 8:19 AM

    Jeff Moden - Wednesday, October 11, 2017 2:35 PM

    Great.  My next question is, have you actually tested in your environment to see if compression is actually going to help?  On the few large tables I've tested in my work environment, I've found that compression actually slowed things down.

    This is my next step, I hear that it can make no difference or harm performance, and I'm not hurting so much on space as I need a performance boost and don't want to compress just for the sake of compressing.

    Sounds like you have a good plan.  Thanks for the feedback.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 8 posts - 1 through 7 (of 7 total)

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