Determining uncompressed size when removing Row level table compression

Question

Determining uncompressed size when removing Row level table compression

phil.martin 65477

Valued Member

Points: 65
More actions
January 7, 2019 at 3:12 am

#373459

Hello,
I'm finding one particular table with very high CPU usage. This table has ROW level compression.
Does anyone know a way to calculate the uncompressed size?
I have found lots of ways to ascertain the saving by compressing and have been BINGing every search I can think of.
Thanks in advance.

Viewing 10 posts - 1 through 9 (of 9 total)

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

DinoRS SSCrazy Points: 2683 More actions · Answer 1

Hi there,

Glenn wrote a good Article on this a while ago, if you use that script and pass in NONE for CompressionType you should see the additional disk space required.

phil.martin 65477 Valued Member Points: 65 More actions · Answer 2

phil.martin 65477

Valued Member

Points: 65

January 7, 2019 at 4:25 am

#2018139

Brilliant thanks. Worked a treat

DinoRS SSCrazy Points: 2683 More actions · Answer 3

DinoRS

SSCrazy

Points: 2683

January 7, 2019 at 4:29 am

#2018141

cheers, happy to help! 🙂

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

phil.martin 65477 - Monday, January 7, 2019 4:25 AM
Brilliant thanks. Worked a treat

So you're saying that going from Row Compression to No Compression improved performance? Or are you just saying that Glenn's code worked well in discovering the estimates?

--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)

phil.martin 65477 Valued Member Points: 65 More actions · Answer 5

Glenn's code worked well in determining the uncompressed size. We have plenty of disk space so I'm going to remove the compression and see if it does improve CPU usage. I'll post when I find out.

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

phil.martin 65477 - Monday, January 7, 2019 4:38 AM
Glenn's code worked well in determining the uncompressed size. We have plenty of disk space so I'm going to remove the compression and see if it does improve CPU usage. I'll post when I find out.

Thanks for the feedback, Phil. It's a unique opportunity to find this type of information out and so would love to hear what you find after uncompressing the table.

--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)

Eric M Russell SSC Guru Points: 125522 More actions · Answer 7

While there is some marginal CPU overhead, from what I've seen, data compression actually doesn't explain high CPU utilization.

If you havn't done this already, I'd suggest confirming if it's mssql cpu usage versus system usage, and then narrow it down to a specific database and top x cpu intensive queries. See if these queries are all referencing this table in question and also rule out whether specific sql operations like type conversions are causing the issue.

This msdn article by Don Castelino explains how to methodically diagnose what's consuming your CPU.
https://blogs.msdn.microsoft.com/docast/2017/07/30/sql-high-cpu-troubleshooting-checklist/

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

phil.martin 65477 Valued Member Points: 65 More actions · Answer 8

The table in question has 30million rows and is almost 500GB in size (compressed). It has constant UPDATEs and DELETEs. Having performed other due dilligence it's worth removing the compression as a test.
Thanks

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

phil.martin 65477 - Monday, January 7, 2019 8:09 AM
The table in question has 30million rows and is almost 500GB in size (compressed). It has constant UPDATEs and DELETEs. Having performed other due dilligence it's worth removing the compression as a test.
Thanks

Unless you're including all indexes in that, that means that each row contains more than 16KBtyes. Can you post the CREATE TABLE statement for the table? I believe I may know a part of the problem but need to see the DDL for the table before I open mouth too much. Even if what I suspect isn't the problem, there are some things I might be able to help you with that will benefit the performance of all queries with changing the basic DDL for the table (PFM :D)

--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)