SQL Server Performance

Reindex taking long time.

Discussion in 'SQL Server 2005 General DBA Questions' started by AJITH123, Aug 29, 2011.

  1. AJITH123 Member

    Hi Guys,
    I have a table with more than3 Billion records nad tried to rebuild the indexes. the rebuild has ran around 16 hours but still not finished (hence i killed the session). Can anybody suggest better approach to do this activity. I will get max 8 hour down time.
    AJITH123, Aug 29, 2011
    #1

  2. Luis Martin Moderator

    Do you have to rebuild all indexes?.
    Did you check each index fragmentation?
    Luis Martin, Aug 29, 2011
    #2

  3. AJITH123 Member

    No, I donot want all, however those have frag > 10% I should do. As mentioned the big tables needs to do the reindexing periodically (i mean atleast once in a month) to perform better. But this is taking long time and i could not predict when it finish :(. I do not find any method to find out the time to finish.
    AJITH123, Aug 29, 2011
    #3

  4. FrankKalis Moderator

    FrankKalis, Aug 29, 2011
    #4

  5. AJITH123 Member

    Not much, since the issue is "taking time". I need to go for some technique to reduce the indexing time. I have procedure to find out the fragmented tables and initiate the indexing task. If try powershell script or some index filter method will I can achive something?
    AJITH123, Aug 29, 2011
    #5

  6. FrankKalis Moderator

    No, don't think so, since this doesn't reduce time as well. Have you considered partitioning?
    FrankKalis, Aug 30, 2011
    #6

  7. satya Moderator

    How frequently you are performing this REINDEX on this large table?
    How about REORG of indexes in order to address the logical fragmentation?
    satya, Aug 31, 2011
    #7

  8. AJITH123 Member

    I tried to reorganise the index initially, but it took long time (around 40 Hours), so i drop that method. Now i am tring to do the reindex offline by taking the table back up and reindex it and restore it. What you say?
    AJITH123, Sep 2, 2011
    #8

  9. AJITH123 Member

    Yes, the table is already partioned!!
    AJITH123, Sep 2, 2011
    #9

  10. satya Moderator

    If it is partitioned why you are performing REORG or REINDEX for old partitions?
    Just make the old ones are READ only to reduce the time.
    satya, Sep 8, 2011
    #10
    preethi likes this.

  11. preethi Member

    just check whether you partitioned the data in the right way so that you can keep the old data in different partitions from current data (I mean the inserts/updates/deletes should happen in current partition)
    preethi, Sep 8, 2011
    #11

Share This Page