April 6, 2020 at 3:09 pm
In what table design/scenarios should I prefer a Row level compression and not Page level?
Like if we were to name one major advantage of one over another, and possibly one disadvantage of each compared to another, what would it be?
In this amazingly helpful in many ways article I see that Update% and Scan/Select estimations as well as actual estimated space gains are the decisive factors:
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008/dd894051(v=sql.100)
HOWEVER, if we really are not concerned about a slight difference (between Row and Page compression) in space saving after table is compressed but also want to ensure that further Read and Write operations to this table will perform at least not worse than before compression then what type should I choose ? (to minimize risk of possible issues with performance after compression).
Also, a sub-question:
after I compress a 500-million-row table, how can I un-compress it /rollback? Are there any pitfalls involved in rolling back?
( I am doing a detailed POC on applying compression to several largest tables in our environment, those tables are huge and critical, and I want to make sure I am well prepared for the unexpected). This will be the statement that I ll use to compress: (or do you suggest to do individual indexes instead of entire table? if yes then why would I want to do that?).
ALTER TABLE My2TBTable_1 REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);
THANK YOU!
Likes to play Chess
April 6, 2020 at 4:07 pm
That link contains enough information to guide you on which one to chose - other than that it is TEST TEST TEST and pick the one that performs better for your workload.
to remove compression - rebuild all again without the compression clause. do note that in both cases log file will GROW a lot and so will data file.
compress both DATA and Indexes - the command you gave only compresses the table (and its clustered index if it has one as it is the same).
on my shop the majority of critical databases are page compressed (exception of CRM as not supported by the vendor - but if it had it it would perform a lot better)
April 6, 2020 at 5:04 pm
Have you run the estimate procedure?
I haven't seen many people saw they do row without page, but I'm sure some have done this. What I'd really do is not compress your table as a POC, but make a copy of it temporarily, including all indexes. Storage is relatively cheap here, and you don't want to get into trouble. Then compress the copy with row, do a live comparison with the original and the repeat with page. That might help you understand for your data, what happens. You can then drop these quickly without uncompressing them (truncate the tables, then drop).
As Frederico mentioned, the log will grow, so check that. Be sure you compare table+indexes.
April 7, 2020 at 5:14 am
How else can I compress DATA? You mean COMPRESS function?
or What do you mean by this:
<<Compess both DATA and Indexes - the command you gave only compresses the table (and its clustered index if it has one as it is the same).>>
Likes to play Chess
April 7, 2020 at 2:26 pm
Row compression is a good choice for OLTP data, high updates. Page compression is good choice for data having low updates.
April 7, 2020 at 3:14 pm
In what table design/scenarios should I prefer a Row level compression and not Page level?
Like if we were to name one major advantage of one over another, and possibly one disadvantage of each compared to another, what would it be?
In this amazingly helpful in many ways article I see that Update% and Scan/Select estimations as well as actual estimated space gains are the decisive factors:
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008/dd894051(v=sql.100)
HOWEVER, if we really are not concerned about a slight difference (between Row and Page compression) in space saving after table is compressed but also want to ensure that further Read and Write operations to this table will perform at least not worse than before compression then what type should I choose ? (to minimize risk of possible issues with performance after compression).
Also, a sub-question:
after I compress a 500-million-row table, how can I un-compress it /rollback? Are there any pitfalls involved in rolling back?
( I am doing a detailed POC on applying compression to several largest tables in our environment, those tables are huge and critical, and I want to make sure I am well prepared for the unexpected). This will be the statement that I ll use to compress: (or do you suggest to do individual indexes instead of entire table? if yes then why would I want to do that?).
ALTER TABLE My2TBTable_1 REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);
THANK YOU!
I ran the code to calculate the "U" value from the article you linked to. I've not done that before. The results were nothing like I expected. The totally static tables that have suffered no updates for up to a decade were all calculated with a "U" percentage of 99.9% and the most actively updated tables were calculated as 0.0%. Something is definitely wrong with that code. I'm not sure I'd rely on it even if you subtracted the percentages from 100 to (IMHO) invert the percents to be what the writeup says.
Be careful.
p.s. I should know better by now than to trust such code in any Microsoft article. Sheesh!
--Jeff Moden
Change is inevitable... Change for the better is not.
April 7, 2020 at 5:09 pm
How else can I compress DATA? You mean COMPRESS function?
or What do you mean by this:
<<Compess both DATA and Indexes - the command you gave only compresses the table (and its clustered index if it has one as it is the same).>>
If you have a clustered index, the table is the index. IF you have non clustered indexes, they are not compressed when you compress the table. You need to compress them separately, and it's a good idea as sometimes you have more space used by indexes than by the data in the table.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply