Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Can you create a partition on an existing Fact table? Expand / Collapse
Author
Message
Posted Wednesday, May 14, 2014 1:08 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 12:27 PM
Points: 51, Visits: 83
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.
Post #1570991
Posted Wednesday, May 14, 2014 1:12 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:30 AM
Points: 43,010, Visits: 36,169
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 2008, MVP
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

Post #1570994
Posted Wednesday, May 14, 2014 1:17 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 12:27 PM
Points: 51, Visits: 83
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
Post #1570998
Posted Wednesday, May 14, 2014 1:59 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 12:27 PM
Points: 51, Visits: 83
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?
Post #1571020
Posted Wednesday, May 14, 2014 2:04 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:30 AM
Points: 43,010, Visits: 36,169
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 2008, MVP
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

Post #1571022
Posted Wednesday, May 14, 2014 2:08 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: Yesterday @ 7:56 PM
Points: 1,816, Visits: 3,461
Poor man's partitioning (though, this was the way we all used ot have to do it ) -> Partitioned Views. Details from msft here.


Steve.
Post #1571025
Posted Wednesday, May 14, 2014 2:12 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 12:27 PM
Points: 51, Visits: 83
stevefromOZ (5/14/2014)
Poor man's partitioning (though, this was the way we all used ot have to do it ) -> 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?
Post #1571027
Posted Wednesday, May 14, 2014 2:15 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:19 PM
Points: 37,075, Visits: 31,636
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1571028
Posted Wednesday, May 14, 2014 2:38 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 12:27 PM
Points: 51, Visits: 83
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
Post #1571038
Posted Wednesday, May 14, 2014 2:49 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: Yesterday @ 7:56 PM
Points: 1,816, Visits: 3,461
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.
Post #1571040
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse