Post reply

add primary key to heap question

  • December 13, 2013 at 1:43 pm

    #283590

    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!

  • December 13, 2013 at 4:15 pm

    #1673416

    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.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 16, 2013 at 7:20 am

    #1673677

    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.

  • December 17, 2013 at 2:06 am

    #1673975

    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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply