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

add primary key to heap question Expand / Collapse
Author
Message
Posted Friday, December 13, 2013 1:43 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 24, 2014 9:35 AM
Points: 38, Visits: 79
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!
Post #1522862
Posted Friday, December 13, 2013 4: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 @ 7:27 AM
Points: 35,769, Visits: 32,437
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."

(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 #1522886
Posted Monday, December 16, 2013 7:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 24, 2014 9:35 AM
Points: 38, Visits: 79
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.
Post #1523213
Posted Tuesday, December 17, 2013 2:06 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, September 18, 2014 5:35 AM
Points: 647, Visits: 764
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
Post #1523568
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse