Reclaim space from compressed partition table

Question

Reclaim space from compressed partition table

UserRR

SSC Eights!

Points: 848
More actions
October 13, 2014 at 9:13 pm

#291705

Hi All,
SQL 2008 R2 EE
I've scoured quite a few blogs /forums and posts but I've yet to solve this one issue I'm having with PAGE Compression.
I have several partitioned tables and I've been trying to reclaim a large amount of wasted space on partitions that are no longer growing.
Most of my tables are working great with the following actions, but a few are not reclaiming the space and I'm not sure exactly why.
Here's my actions:
1.Create TEMP Partition Functions/Schema
2. Create TEMP File Groups and Files
3. Rebuild Clustered Index (or add/drop CI) and Non-CI to TEMP FILE GROUP
4. Shrink then Drop the Original Files, then backup the log
5. Rebuild the Original Files on the Original File Groups (with autogrow set to a small amount to compact as much data as possible)
6. Rebuild Clustered Index (or add/drop CI) and Non-CI to Original FILE GROUP with DATA_COMPRESSION = PAGE
7. Drop TEMP Partition Functions/Schema
8. Shrink then Drop the TEMP Files, then backup the log
The problem is that when I perform step #6, the partition files are compressing, but there is still a large amount of wasted space (that I'm trying to reclaim)
Now I've observed that if I rebuild the CI back without compression, the file grows via small auto-grow increments and uses 95% of its grown space.
Bear in mind this "over-growth" only happens with a few of my partitioned tables, the rest compress and reclaim the space nicely.
Will it be the data and /or datatypes that's causing this gap of space when it tries to compress the tables? I also notice the gap of space is less when row compression is done, but still not as space efficient as eliminating compression all together during the rebuild.
More info is that the tables are Data Warehouse tables with 30+ columns of half numeric(30,10) datatypes, other types would be date,varchar, decimal(20,3)
Any clarifications would be very helpful.
Thank you in advance
-RR

Viewing 3 posts - 1 through 2 (of 2 total)

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

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

UserRR (10/13/2014)
I've been trying to reclaim a large amount of wasted space on partitions that are no longer growing

1. What do you consider to be a "large amount" in this case?

2. How big is the table in bytes and rows?

3. How many partitions in the table?

4. How do you identify partitions that are "no longer growing"? And, when you say that, you specifically mean that absolutely no modifications or additions are being made to the partition and the partition could actually be set to "READ_ONLY", yes?

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

UserRR SSC Eights! Points: 848 More actions · Answer 2

Hi Jeff,

1.Upon my analysis, the Database is 2x the size of it's data files.

Now, I believe this had been caused by the archaic ETL processes over time,

but I have yet to review that. I know large is an arbitrary amount,

but double the size is very wasteful from a storage perspective.

Agreed?

2. One example is a table that's 20 GB in size allocated,

10 GB in data/index space, I've been able to PAGE compress that down to

a few GB, so we're talking 30 million rows and more than half that space

in waste.

3. The table in the example has 31 partitions/FG/Files at the moment.

4. I believe that a partition by date doesn't get updated

after that data has been loaded for the month. At least that's

my current understanding.

If there were additional modifications/additions made to the

partition, then yes, some space would be needed, but I can't

see the point in having a 20 GB File/FileGroup for that specific

partition that's barely half full. And even less full after I finish

compressing that data. Read only could be an option, but I haven't

had that conversation yet.

That being said, I did find a way to "fix" my original issue of

a compressed file still not reclaiming the space.

After step #6, I would perfrom a shrinkfile up to a certain % free of space of the datafile size on the Original partition files built.

Now I know shrinkfile causes fragmentation and is bad, but when step #6 is performed,

this should defrag the data onto the Originally named/newly rebuild datafile (Step #5)

I appreciate your opinions on these comments/steps.

Thank you again.

-RR