Post reply

Index rebuild Job

  • March 13, 2016 at 5:57 am

    #313934

    Hi All,

    We have an index rebuild job running over night, unfortunately it fails many time due to activity on system and it's chosen by sql server as deadlock victim.

    Any way to boost it's priority as if it fails for multiple consecutive times , system becomes very slow.

    Thanks

    Nader

  • March 13, 2016 at 8:21 am

    #1864793

    nadersam (3/13/2016)

    Hi All,

    We have an index rebuild job running over night, unfortunately it fails many time due to activity on system and it's chosen by sql server as deadlock victim.

    Any way to boost it's priority as if it fails for multiple consecutive times , system becomes very slow.

    Thanks

    Nader

    Typically and contrary to what many will say, fragmentation of indexes usually isn't the big problem. Out of date stats is. It frequently looks like index rebuilds add performance but, if they are rebuilds and not just reorgs, it's frequently the fact that index rebuilds also rebuild the statistics.

    With that, I would make sure that statistics updates are done separately and after index maintenance whether said maintenance is successful or not.

    Getting to the index maintenance failures... what version of SQL Server do you have? Standard Edition or Enterprise Edition?

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

  • March 14, 2016 at 2:20 am

    #1864851

    Jeff Moden (3/13/2016)

    nadersam (3/13/2016)

    Hi All,

    We have an index rebuild job running over night, unfortunately it fails many time due to activity on system and it's chosen by sql server as deadlock victim.

    Any way to boost it's priority as if it fails for multiple consecutive times , system becomes very slow.

    Thanks

    Nader

    Typically and contrary to what many will say, fragmentation of indexes usually isn't the big problem. Out of date stats is. It frequently looks like index rebuilds add performance but, if they are rebuilds and not just reorgs, it's frequently the fact that index rebuilds also rebuild the statistics.

    With that, I would make sure that statistics updates are done separately and after index maintenance whether said maintenance is successful or not.

    Getting to the index maintenance failures... what version of SQL Server do you have? Standard Edition or Enterprise Edition?

    Thanks Jeff for your reply.

    I assume i should use the full scan method men updating statistics.

    Also should i use all ,columns or indexes.

    For SQL it's enterprise edition but can you tell please why did you ask about this.

    Thanks

    Nader

Viewing 3 posts - 1 through 2 (of 2 total)

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