Post reply

Errror rebuilding index

  • March 16, 2009 at 2:08 pm

    #131831

    Hi!

    Trying to rebuild a very big table's index I'm getting this error:

    "The query has been canceled because the estimated cost of this query (7798) exceeds the configured threshold of 3000..."

    I understand what It means but.... I have to rebuild it anyway since that index and some others are on a very bad condition.

    There is other option for rebuild with less cost? Which would be the difference between a rebuild and manually delete and create again the index.

    Thanks!

  • March 16, 2009 at 7:44 pm

    #960739

    Sounds like you have the Resource Governor in place. I'm not that familiar with it, but I would suspect there would be some way to set this query to not be limited by it while you run it.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • March 16, 2009 at 8:10 pm

    #960743

    Thanks for the answer. I'm afraid that It could kill the server for long and it's a production server. I wouldn't like to knock down the entire system for 2 hours 🙂

    According the message the query it's too expensive, that's why I'm wondering if there any other (cheaper) way to rebuil the index.

  • March 17, 2009 at 1:10 am

    #960838

    crfenix (3/16/2009)

    According the message the query it's too expensive, that's why I'm wondering if there any other (cheaper) way to rebuil the index.

    Not really. You could use REORGANISE instead of rebuild, but it doesn't do all that rebuild does and it's not going to be that much faster. Do the rebuild overnight or whenever there's some quiet time.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 17, 2009 at 5:25 am

    #960927

    Also remember that those estimated costs, are estimated, not actual. Execution can be quite a bit different than what is represented, and in a good way.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

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

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