none
Rebuild clustered index on 500 million row table??? RRS feed

  • Question

  • My environment is SQL 2000.  I have a table with 500 million rows.  The table is consistently getting updated and inserted.  I can not take the table offline.  My clustered index needs to be rebuilt due to decreased performance.  How do I accomplish this?

    Thursday, January 17, 2008 12:21 AM

All replies

  • andre cole,

     

    I think it is time to start thinking about partitioning that table.

     

    Microsoft SQL Server 2000 Index Defragmentation Best Practices

    http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

     

     

    AMB

    Thursday, January 17, 2008 1:37 AM
    Moderator
  • You will need upgrade to SQL Server 2005 Enterprise for use Partitioning.

     

    What you think in copy this table to a new database than rebuild the index?

     

    Than you copy the table to original database but with a new name, than you rename the original table to a old name and rename the table rebuilded to real name.

     

    I dont know if this work but you can try and tell me.

     

    I hope help you.

    Thursday, January 17, 2008 7:13 PM
  •  Fabiano Amorim wrote:

    You will need upgrade to SQL Server 2005 Enterprise for use Partitioning.

     

    What you think in copy this table to a new database than rebuild the index?

     

    Than you copy the table to original database but with a new name, than you rename the original table to a old name and rename the table rebuilded to real name.

     



    You can partition in SQL 2000.


    I agree with you that a you can do all the work on a new table and then rename, except I would do it in the same database to avoid copying the table from db to db

    Thursday, January 17, 2008 7:16 PM
  • SQL-PROD i guess whit you are speak about Partitioned Views rigth? The concept to Partition in SQL Server 2005 is very diferent.

     

    Ok, he can do the copy in the same database, i'm just do a tip about how do. I spoke to do in other database or in other machine to avoid problems in the production database.

    Thursday, January 17, 2008 7:53 PM
  • Yes I am speaking of Partitioned Views.  And I don't want to argue but I know the implementation is different but isn't the concept or goal really the same -  To break up large tables into smaller more managable subsets and increase performance?
    Thursday, January 17, 2008 8:04 PM
  • Yes you is rigth, my english its poor :-(, the implementation is different.

     

    Thanks,

     

    Friday, January 18, 2008 10:24 AM
  • If I were you, I would try to defrag the index insted of rebuild it, as you can stop then resume a defrag operation. Just let it run durring off-peak hours, then stop it when things start to pick up again. The amount of time to completely defrag the index will depend heaviliy on the size of the table (in bytes, not rows), speed of the disks, and availablity of IO bandwidth.

     

    dbcc indexdefrag('<Database Name>', '<Table Name>', '<Index Name>')

     

    Also, just as hunchback suggested, you should think about partitioning that table.

    • Proposed as answer by Josh Patterson Tuesday, January 26, 2010 7:54 PM
    Tuesday, January 22, 2008 2:31 PM