February 8, 2010 at 6:38 am
Hey
I am in the process of starting to parition some of our larger tables. I am doing this by creating the partition function/scheme and then dropping & re-creating the clustered index on the partition scheme.
My main question is does anyone have any hints on how to improve the time it takes to both drop and re-create the clustered indexes? At present it takes about 1hr to drop the clustered index and 2 & 1/2 to re-create it.
I only have a small maintenance window in which to do this work for about 12 tables, so any advice is welcomed.
I have already dropped the non-clustered indexes prior to this operation taking place.
Numbers:
Server - 2 x Intel Quad Core Xeon 2.53GHz
RAM - 48GB
Disk - Dell EqualLogic iSCSI SAN
Table sizes: 500 - 850 million rows
Cheers guys and gals
😀
February 8, 2010 at 9:12 am
I don't beleive that there is any easy way to speed up the creation although if you have a different IO path that you can use, that would help. Also, you can use the Drop_Existing option in the Create Database statement so that you don’t have to perform separate drop and create steps and if the table does not have any LOB datatypes you can specify Online = On to help further mitigate any impact to queries against the table.
February 10, 2010 at 7:40 am
Cheers for the ideas.
In the end I chose to break things down and did things in the following manner
1. Create partition function & scheme
2. Rename data table with sp_rename
3. Create table on partition scheme with desired indexes etc
4. Create staging table with same structure as source/target
4.5. Apply check constraint that matches the partition I want to populate
5. Load staging table with data
6. Apply indexes that are on partitioned table to the stagin table
7. Switch staging table into desired partition
8. Drop staging table
9. Repeat from stage 4 until new table mirrors the source, only this time with nice chiny new partitions.
This process allowed me to reduce the time taken for the partitioning of a table with 815 million rown from 10hrs to about 3hrs 30mins.
Lucky for me that I had a lot of extra storage that allowed me to effectivly duplicate a 200GB table.
😀
February 11, 2010 at 6:23 am
Could you provide more details, such as partition key? Not sure what the staging table for. What data you were loading to that staging table?
Thanks.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply