SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Can you create a partition on an existing Fact table?


Can you create a partition on an existing Fact table?

Author
Message
skaggs.andrew
skaggs.andrew
Old Hand
Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)

Group: General Forum Members
Points: 365 Visits: 93
Hi All,

I have a Fact table that has been in use for 2 years now and I am getting to the point where the table is beginning to get big. It is currently sitting at roughly 40 mill records. I have BO reports that are built of this table and I have an ETL nightly scheduler that repopulates this table every night. (Along with other staging, dim, etc.)

What I am looking to do is improve the performance of recreating this fact table. The fact table has about 6 years worth of data already in it (education data). What I want to do (because I THINK this would help in improving performance) is only delete the current records and then load in the current records each night leaving all historical data untouched in this fact table. (If it's not changing, why delete and reload every night?)

When testing this process, I found that trying to delete 5 mill records from this table was taking me an hour to do so. After researching a little, I felt like maybe building partitions on this fact table would greatly help me achieve what I want to do. The fact table is already kind of broken into chunks with State Tests, Course Grades, Attendance, Incidents, etc.

Based on this, can I create partitions on this already existing FACT table or would I have to rebuild an all new Fact table with partitions, then transfer over the data?

Please let me know your opinions.

Thanks for the help.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)

Group: General Forum Members
Points: 224480 Visits: 46317
You can partition an existing table. Not by adding partitions to it, but by rebuilding the clustered index onto a partition scheme. This will take as long as rebuilding the clustered index usually takes, it won't be instant.

Once partitioned, you can switch out a partition of data (swap a partition with an empty table) to do fast 'deletes'

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


skaggs.andrew
skaggs.andrew
Old Hand
Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)

Group: General Forum Members
Points: 365 Visits: 93
GilaMonster (5/14/2014)
You can partition an existing table. Not by adding partitions to it, but by rebuilding the clustered index onto a partition scheme. This will take as long as rebuilding the clustered index usually takes, it won't be instant.

Once partitioned, you can switch out a partition of data (swap a partition with an empty table) to do fast 'deletes'


Thanks for the response GilaMonster. Would you by any chance know of any article that could help me through this process? I have read about creating the partition function and scheme.

When rebuilding the clustered index, I can just delete it and rebuild it so it is pointing to the newly created partition scheme?

As for switching out a partition of data (which sounds like that is what I am ultimately looking for) I dont know anything about this. Any info would be greatly appreciated.

Thanks
skaggs.andrew
skaggs.andrew
Old Hand
Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)

Group: General Forum Members
Points: 365 Visits: 93
Well, my dreams may have been crushed. I tried to create a Partition Function and when I hit the execute button I got the following message:

Partition function can only be created in Enterprise edition of SQL Server. Only Enterprise edition of SQL Server supports partitioning.

Is there any work around for this?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)

Group: General Forum Members
Points: 224480 Visits: 46317
skaggs.andrew (5/14/2014)
Thanks for the response GilaMonster. Would you by any chance know of any article that could help me through this process? I have read about creating the partition function and scheme.


Start with Books Online, there's a lot in there

When rebuilding the clustered index, I can just delete it and rebuild it so it is pointing to the newly created partition scheme?


No, you don't want to do that. Just do a CREATE ... WITH DROP_EXISTING and specify the partition scheme where the filegroup would usually go

As for switching out a partition of data (which sounds like that is what I am ultimately looking for) I dont know anything about this. Any info would be greatly appreciated.


Again, Books Online has a lot on the subject.

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


stevefromOZ
stevefromOZ
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: Moderators
Points: 9546 Visits: 3757
Poor man's partitioning (though, this was the way we all used ot have to do it :-D ) -> Partitioned Views. Details from msft here.

Steve.
skaggs.andrew
skaggs.andrew
Old Hand
Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)

Group: General Forum Members
Points: 365 Visits: 93
stevefromOZ (5/14/2014)
Poor man's partitioning (though, this was the way we all used ot have to do it :-D ) -> Partitioned Views. Details from msft here.


Is this essentially physically creating smaller tables then union them all together in a view? What are the down sides to doing it this way vs having the capability of using partitioning?

Would it be better to try and get the Enterprise Edition?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)

Group: General Forum Members
Points: 215088 Visits: 41979
skaggs.andrew (5/14/2014)
Hi All,

I have a Fact table that has been in use for 2 years now and I am getting to the point where the table is beginning to get big. It is currently sitting at roughly 40 mill records. I have BO reports that are built of this table and I have an ETL nightly scheduler that repopulates this table every night. (Along with other staging, dim, etc.)

What I am looking to do is improve the performance of recreating this fact table. The fact table has about 6 years worth of data already in it (education data). What I want to do (because I THINK this would help in improving performance) is only delete the current records and then load in the current records each night leaving all historical data untouched in this fact table. (If it's not changing, why delete and reload every night?)

When testing this process, I found that trying to delete 5 mill records from this table was taking me an hour to do so. After researching a little, I felt like maybe building partitions on this fact table would greatly help me achieve what I want to do. The fact table is already kind of broken into chunks with State Tests, Course Grades, Attendance, Incidents, etc.

Based on this, can I create partitions on this already existing FACT table or would I have to rebuild an all new Fact table with partitions, then transfer over the data?

Please let me know your opinions.

Thanks for the help.


Yes. Have a look at "Partitioned Views" in Books Online. They have a different set of caveats but can be just as effective as "Partitioned Tables".

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
skaggs.andrew
skaggs.andrew
Old Hand
Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)

Group: General Forum Members
Points: 365 Visits: 93
Thanks Jeff. I will read up on this. Sounds like my only option right now and would be better than using just this stand alone table. Thanks all
stevefromOZ
stevefromOZ
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: Moderators
Points: 9546 Visits: 3757
It's not show stopping but the management is a bit of a PITA. You would be well served to create some management procs that do things like dropping and recreating the view as/when things change (ie not as easy as just partition switching in/out - you can't just take a referenced table out from under the view).

Check out what your price would be for Enterprise, but if you're "off the street" without any pricing/discounts at your disposal, the price differential is going to be pretty large.

Steve.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search