Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


add primary key to heap question


add primary key to heap question

Author
Message
madzebra
madzebra
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 83
I want to change the range (left to right) of a partition function that a very large table (4TB) is built on. I could do this by dropping the PK and adding a new PK on a different scheme with range right function.

Am I correct that this could take a very long time? I believe I cannot use online=on because the table has a varbinary column?

I need a miracle here. Any ideas? Thoughts?

Many thanks!
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45119 Visits: 39918
You might be able to use the ONLINE option if the VARBINARY column is NOT a blob.

What is the partitioning column and function based on? Month's for ever increasing DATETIME values?

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
madzebra
madzebra
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 83
Thanks for the reply Jeff.

Unfortunately the varbinary is BLOB. The partition key is a bigint. There are currently 2 filegroups being used and the one that doesn't have a range value is close to filling up the drive it is on. The reason for the partition is because our SQL Servers run on VMs and it seems that 3TB drives are the limit.

What I'm thinking I'll have to do (since I believe that I do not have the window to drop and rebuild the index on the scheme/function with right range) is to split the range into new partitions so that the new partition receives the new "slice" of data (which is smaller than the entire partition). I hope that makes sense; I realize it may be hard to follow.

This will have to be an ongoing thing since the data keeps coming in.

What do you think would take longer? Splitting the range where 2TB of data had to be moved or rebuilding an index on a 4TB heap? I don't really know the internals of what is going on there.
Van Heghe Eddy
Van Heghe Eddy
Right there with Babe
Right there with Babe (737 reputation)Right there with Babe (737 reputation)Right there with Babe (737 reputation)Right there with Babe (737 reputation)Right there with Babe (737 reputation)Right there with Babe (737 reputation)Right there with Babe (737 reputation)Right there with Babe (737 reputation)

Group: General Forum Members
Points: 737 Visits: 876
Hi MadZebra,

Just a thought,
But can't you not just create a new filegroup on another disk (new datafile) and assign the next range partition to the new filegroup.
Then set a new boundary so new data will be pushed to the new filegroup.
That way the data can stay where it is and new data can be filled in the nex datafiles (from the new filegroup)

ALTER PARTITION SCHEME [ps_YourpartionRange] NEXT USED [YouNewFileGroup]
ALTER PARTITION FUNCTION pf_YourpartitionRange() SPLIT RANGE (YouNewMaxUpperBoundary)

This should be very fast as there is no data moved beside the new loads that are coming in.
Eddy
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