Table Partitioning Question

Question

Post reply

Table Partitioning Question

alex_pixley

SSC-Addicted

Points: 436
More actions
August 6, 2014 at 9:19 am

#288747

I've inherited a database that primarily houses imports from an external source. The Trx_Header table is a normal table, but Trx_Details is partitioned by year. There are partitions covering years 2009-2021. I am familiar with the idea of sliding window partition where you swap in an empty partition at the front while at the same time removing the oldest partition from the end; my concern is that we will run out of space well before 2021. Is there a way to just remove an unused partition without having to rebuild the entire table?
TIA,
Alex
August 7, 2014 at 3:42 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 14 (of 14 total)

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

mssqlsrv SSCrazy Points: 2375 More actions · Answer 1

Pls check on below link. Might be helpful.

http://www.patrickkeisler.com/2013/01/how-to-remove-undo-table-partitioning.html

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

An empty partition is not going to be taking up any space.

Or do you mean swap out and merge old partitions as time goes on? If so, that's just the sliding window that you know, you just don't do the step to split the partition, you do all the others (swap, merge, truncate swapped out table)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

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

mssqlsrv (8/7/2014)

Pls check on below link. Might be helpful.

http://www.patrickkeisler.com/2013/01/how-to-remove-undo-table-partitioning.html%5B/quote%5D

I wouldn't remove table partitioning... it's not the problem and "undoing" partitioning won't save much space at all and will only undo all of the extreme maintenance benefits of storing temporal data in a partitioned 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)

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

alex_pixley (8/6/2014)
I've inherited a database that primarily houses imports from an external source. The Trx_Header table is a normal table, but Trx_Details is partitioned by year. There are partitions covering years 2009-2021. I am familiar with the idea of sliding window partition where you swap in an empty partition at the front while at the same time removing the oldest partition from the end; my concern is that we will run out of space well before 2021. Is there a way to just remove an unused partition without having to rebuild the entire table?
TIA,
Alex

As Gail stated, unused partitions won't take up any space to be concerned about. Someone did you a favor by setting up "future use" partitions and I wouldn't mess with them. Almost everything will be "auto-magic" for you until 2021. You've got 7 or 8 years to relax. 😉

Yes, you can still SWITCH older partitions out of the table as in the sliding window example if all the indexes are "aligned" in the partitioned table. It's pretty easy to add space to a set of disks though, especially if it's on a SAN or ISCSI system. Are you sure you want to get rid of such history from 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)

alex_pixley SSC-Addicted Points: 436 More actions · Answer 5

Yes, adding disk space is easy. In some environments, however, getting additional disk space approved and implemented is a painful process. Right now, I have 13 partitions and I'd really rather have 7 partitions. Is it possible to remove partitions 8-13 without having to rebuild the entire table, assuming that the indexes are aligned? If so, can you point me in the right direction on how to do it?

alex_pixley SSC-Addicted Points: 436 More actions · Answer 6

mssqlsrv (8/7/2014)

Pls check on below link. Might be helpful.

http://www.patrickkeisler.com/2013/01/how-to-remove-undo-table-partitioning.html%5B/quote%5D

This link doesn't show exactly what I'm looking for, but I think it might get me to where I want to be.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

alex_pixley (8/7/2014)
Yes, adding disk space is easy. In some environments, however, getting additional disk space approved and implemented is a painful process. Right now, I have 13 partitions and I'd really rather have 7 partitions. Is it possible to remove partitions 8-13 without having to rebuild the entire table, assuming that the indexes are aligned? If so, can you point me in the right direction on how to do it?

Yes, merge them if empty, otherwise switch them out with a table and then merge them. It's exactly the same as the switch out and merge part of sliding window, which yu said you were familiar with.

If partitions 8-13 are empty, this will not save you any disk space.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

alex_pixley SSC-Addicted Points: 436 More actions · Answer 8

GilaMonster (8/7/2014)
If partitions 8-13 are empty, this will not save you any disk space.

It's like insulating your home. There is the upfront cost of time and effort. The savings come in the future from bills that you would have had to pay had you not done the work. 🙂

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

alex_pixley (8/7/2014)
GilaMonster (8/7/2014)
If partitions 8-13 are empty, this will not save you any disk space.
It's like insulating your home. There is the upfront cost of time and effort. The savings come in the future from bills that you would have had to pay had you not done the work. 🙂

More like bricking up a door I'll need next month...

Dropping empty partitions won't save any space. If you merge them now, when the time comes that you need them, you'll need to recreate them. So it's upfront work, saving nothing, that will cost you more work in the future. I really don't see the point.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 10

alex_pixley (8/7/2014)
GilaMonster (8/7/2014)
If partitions 8-13 are empty, this will not save you any disk space.
It's like insulating your home. There is the upfront cost of time and effort. The savings come in the future from bills that you would have had to pay had you not done the work. 🙂

Unless the partitions have their own pre-allocated separate files and those files are obnoxiously large, no disk space will be saved. Merging the empty out years is nothing but a waste of time. In your terms, you're sucking a layer of insulation out of the walls instead of adding insulation.

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

alex_pixley SSC-Addicted Points: 436 More actions · Answer 11

If I get rid of partitions 8-13 and then implement sliding window on a yearly basis, I will never have more than seven years worth of data. In reality, I have several of these databases for several different imports. If I were to wait until 2021 to implement sliding window, each of these databases would have 13 year's worth of data, which would require nearly double the disk space. I have worked at companies that would add disks at the drop of a hat and would not blink at keeping many TBs of historical data for yearsandyearsandyears, but I'm not at one of those companies now. I respect your opinions (why else would I have posted here?), but it is not necessary to point out that you disagree with someone more than once in the same thread.

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 12

alex_pixley (8/8/2014)
If I get rid of partitions 8-13 and then implement sliding window on a yearly basis, I will never have more than seven years worth of data. In reality, I have several of these databases for several different imports. If I were to wait until 2021 to implement sliding window, each of these databases would have 13 year's worth of data, which would require nearly double the disk space. I have worked at companies that would add disks at the drop of a hat and would not blink at keeping many TBs of historical data for yearsandyearsandyears, but I'm not at one of those companies now. I respect your opinions (why else would I have posted here?), but it is not necessary to point out that you disagree with someone more than once in the same thread.

Oh, no. I wasn't suggesting that you wait to get rid of the data that you no longer need. I was suggesting that you don't need to merge the partitions that have been setup for the future.

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

alex_pixley (8/8/2014)
If I get rid of partitions 8-13 and then implement sliding window on a yearly basis, I will never have more than seven years worth of data.

And if you don't get rid of those extra partitions and just implement the switch-out and merge part of sliding window (since you don't need to create new partitions), you'll never have more than 7 years of data. You can add extra logic in to see if a new partition needs to be created, in case you don't want the risk of something breaking in 7 years time

Your comments seem to suggest that you view 'sliding window' as a single operation. It's not. It's a phrase describing a combination of operations (split latest partition, switch out oldest, truncate the table the partition was switched into, merge the oldest partition).

There is nothing that will prevent you implementing part of that (switch out, truncate and merge) since you don't need to split because the new partitions are already there.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass