Post reply

Rebuilding large index with limited free space

  • November 9, 2015 at 8:56 am

    #304994

    Hello

    I have a large database (almost 2TB) with a table in the range of the 200gb, not that many rows but the each row is big, I've been deleting data from it due a regular process that archives data and the ghost cleanup is doing a poor job at recovering the space, now I now the table is consuming more almost 100gb more than what it actually needs due to index fragmentation and padding, so I need to rebuild the cluster index as to recover it, but I do not have extra space at that drive and the rebuild will consume what is left. what are my options? Will a reorganize do the trick since that can run in batches?

    Thanks

    I am using SQL Server 2012 SP1 CU7

  • November 9, 2015 at 9:30 am

    #1838599

    Reorganize could help, but I can't tell how much.

    Another possibility is BCP the data to a file, drop the table and BCP the data back in. Index rebuild does almost the same thing, it only does so internally.

    -- Gianluca Sartori

  • November 9, 2015 at 9:43 am

    #1838607

    Thanks

    I will do something similar, add a new file group and move those tables over there

  • November 10, 2015 at 4:35 am

    #1838790

    I did something similar with a 500GB table.

    The table had no index, so I needed to create a clustered index on the table for partitioning.

    50GB free and no space on the server for growth. So yeah fun and games.

    What I did was batch insert into a permanent table with the PK clustered where I wanted it, with same structure, with an identity, doing insert with identity_insert.

    So first I created a non clustered index on the Identity column of the heap to help in deleting else it would have been real chaos.

    THEN while (oldtable is non empty)

    Begin

    select max(ID) from (select top 1000000 ID from oldtable order by id asc) as window

    Insert into Newtable Select * from oldtable where ID < max(ID) from temptable

    Delete from old table where ID < max(ID) with TabLock.

    END

    Repeat

    All in a while loop till the heap was empty.

    Then finally, did a drop table and an sp_rename of the table I was pushing data into.

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

  • November 17, 2015 at 7:17 pm

    #1840514

    ricardo_chicas (11/9/2015)

    Thanks

    I will do something similar, add a new file group and move those tables over there

    What is the table used for? For example, is it an audit or closed invoice table where once a row is written or closed, it's never updated ever again?

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

  • November 20, 2015 at 9:17 am

    #1841394

    Jeff Moden (11/17/2015)

    ricardo_chicas (11/9/2015)

    Thanks

    I will do something similar, add a new file group and move those tables over there

    What is the table used for? For example, is it an audit or closed invoice table where once a row is written or closed, it's never updated ever again?

    It is a table that is mostly used for history, that is why it was ok for me to move it to another FG at a different drive

  • November 20, 2015 at 1:17 pm

    #1841452

    ricardo_chicas (11/20/2015)

    Jeff Moden (11/17/2015)

    ricardo_chicas (11/9/2015)

    Thanks

    I will do something similar, add a new file group and move those tables over there

    What is the table used for? For example, is it an audit or closed invoice table where once a row is written or closed, it's never updated ever again?

    It is a table that is mostly used for history, that is why it was ok for me to move it to another FG at a different drive

    If that's the case, then it might be worthwhile to partition it by month using a separate FileGroup and File for each month for the older months. It's a bit of a pain to pull off correctly but the benefits are...

    1. Absolutely zero index maintenance on the Read_Only months.

    2. Except for a "final" backup, absolutely no addition backups of the Read_Only months.

    3. Creates the possibility of "Piecemeal Restores" incase just one part of the table goes corrupt.

    4. Done correctly, allows for nearly instant removal of older months for final archive purposes. No DELETEs required. Log file loves it.

    5. Makes auditors really, really happy.

    The only real disadvantage is that (with the understanding that required month-end code to prep old, current, and new months will need to be automated), since each partition will have it's own B-TREE (no matter whether you use a "Partitioned View" or "Partitioned Table"), queries can run a tiny bit slower but usually not so slow that most would notice.

    --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 7 posts - 1 through 6 (of 6 total)

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