Post reply

Patitioning

  • March 1, 2015 at 8:53 pm

    #295588

    When you load the data into a new partition table, can it to done online without any downtime? because I have few tables that are around 250 gigs and more.

  • March 1, 2015 at 9:35 pm

    #1781017

    iqbalbutt (3/1/2015)

    When you load the data into a new partition table, can it to done online without any downtime? because I have few tables that are around 250 gigs and more.

    Not totally zero downtime but definitely subsecond (several milliseconds, in most cases) depending on how much free disk space you have.

    Before you even think of that, though, what are these tables being used for and what is the reason for partitioning them?

    --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)

  • March 2, 2015 at 5:40 am

    #1781070

    iqbalbutt (3/1/2015)

    When you load the data into a new partition table, can it to done online without any downtime? because I have few tables that are around 250 gigs and more.

    What really happens internally is that the "pointers" are moved, not the information itself. So if you have 1 year of sales data on table A and the partition logic moves it, then the pointers or metadata change from table A to table B but the data is not moved via normal TSQL commands like INSERT or DELETE. You are basically instructing the SQL engine via partition functions, to identify table B as the new holder for the information. And this activity is extremely fast.

    But like Jeff mentioned, the main question you should ask yourself is why do you need partitioning or what are you trying to solve?

    Partitioning is an important and I would say, complex topic. You can decrease performance if it's not properly used or you picked the wrong Cluster Index.

  • March 2, 2015 at 9:05 am

    #1781130

    sql-lover (3/2/2015)

    iqbalbutt (3/1/2015)

    When you load the data into a new partition table, can it to done online without any downtime? because I have few tables that are around 250 gigs and more.

    What really happens internally is that the "pointers" are moved, not the information itself. So if you have 1 year of sales data on table A and the partition logic moves it, then the pointers or metadata change from table A to table B but the data is not moved via normal TSQL commands like INSERT or DELETE. You are basically instructing the SQL engine via partition functions, to identify table B as the new holder for the information. And this activity is extremely fast.

    But like Jeff mentioned, the main question you should ask yourself is why do you need partitioning or what are you trying to solve?

    Partitioning is an important and I would say, complex topic. You can decrease performance if it's not properly used or you picked the wrong Cluster Index.

    To add to that, you also have to consider that every unique index will need to have the partitioning column added to it if you want to keep "Aligned Indexes" to support SWITCH In/Out technology and to keep index maintenance down to only those partitions that need it. That would also mean that no FK's could point at the table and that's a large (no pun intended) reason why I want to know what the tables that you're thinking of partitioning are actually used for.

    To coin a phrase, "If you think the partitioning you just did was easy, you probably did it wrong". 😛

    --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)

  • March 2, 2015 at 9:50 am

    #1781147

    Is this the correct process to do partitioning?

    Rename constraints, indexes, and table

    Create table

    Create clustered index using Partition Scheme

    Create constraints

    Back fill data from renamed table

    Create non clustered indexes

    Verify row counts

    Drop old table

  • March 2, 2015 at 12:23 pm

    #1781192

    iqbalbutt (3/2/2015)

    Is this the correct process to do partitioning?

    Rename constraints, indexes, and table

    Create table

    Create clustered index using Partition Scheme

    Create constraints

    Back fill data from renamed table

    Create non clustered indexes

    Verify row counts

    Drop old table

    Yes and No. It depends. Please answer my previous questions so that we can advise you properly.

    --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)

Viewing 6 posts - 1 through 5 (of 5 total)

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