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

Question

Post reply

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

quinn.jay

SSCertifiable

Points: 5505
More actions
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

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

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

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)

quinn.jay SSCertifiable Points: 5505 More actions · Answer 2

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

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)

quinn.jay SSCertifiable Points: 5505 More actions · Answer 4

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/

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

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)

quinn.jay SSCertifiable Points: 5505 More actions · Answer 6

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.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

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)