Post reply

DBCC Reindexing on huge sized table

  • May 9, 2006 at 6:55 am

    #114136

    On one of the database, I support has

    16957722 records in a table and it's clustered index size is 27606.30.

    I used to run the Reindex job created on weekly Sunday starting at 11:00 PM. It takes more than 7 hours.

    Since it is a 24x7 server, when Reindexing job runs it causes blocks so I am cancelling the job.

    In this situation what would you suggest to reindex the table of such big one ( 27 GB ) ?

    Sivaprasad S - [ SIVA ][/url]http://sivasql.blogspot.com/[/url]

  • May 9, 2006 at 9:20 am

    #636785

    DBCC INDEXDEFRAG



    A.J.
    DBA with an attitude

  • May 9, 2006 at 9:39 am

    #636796

    It has external fragmentation. It requirs DBCC REINDEX to rebuild index.

    DBCC INDEXDEFRAG will be useful if it is internal fragmentation that too if it less.

    Sivaprasad S - [ SIVA ][/url]http://sivasql.blogspot.com/[/url]

  • May 10, 2006 at 2:59 am

    #636940

    upgrade to 2005 where you can rebuild an index on-line, otherwise, I don't think you have any choice.

  • May 10, 2006 at 6:25 am

    #636979

    Here's something you could try:

    1. Partition the table based on a date element, year is a common one or month if the table is archived regularly. You can also use a data element that pertains to your business, say Region for example.

    2. Now you have smaller tables to manage and Reindex and can split the workload.

    3. Use views to access the data across the tables. If you are using Enterprise Edition, you can use Indexed Views. There are some caveats to using Indexed Views so check them out in BOL.

    HTH,

    Mark

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

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