Post reply

Reorgnize index

  • October 30, 2012 at 4:55 pm

    #269443

    When I use sql server maintenance plan, there is a task called reorganize index.

    If I setup a job to reorgnize an index, for example every 6 hours, should each time be shorter than previous one?

    I mean, does it only reorganize those defragmented, or everytime it wil reorganize all indexes ?

    Thanks

  • October 30, 2012 at 5:03 pm

    #1554422

    sqlfriends (10/30/2012)

    If I setup a job to reorgnize an index, for example every 6 hours, should each time be shorter than previous one?

    Maybe. If you mean reorganise, yes, it likely will be. If rebuild, no

    I mean, does it only reorganize those defragmented, or everytime it wil reorganize all indexes ?

    It will reorganise every single index in the DB every time it runs.

    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

  • October 30, 2012 at 5:10 pm

    #1554428

    Thanks, I DO mean reorgnize.

    But if everytime it will reorganize every single index in the db every time it runs, why it maybe each time is shorter than the other?

    thanks

  • October 30, 2012 at 6:16 pm

    #1554445

    That is one of the downsides to the index maintenance plans in SQL Server. Most DBA's that I know use a more targeted approach that checks the fragmentation levels of the index and either reorganizes them or rebuilds them based on a threshold parameter. There are many script examples available on the internet, we run a customized version of the one that Andew Kelley published in SQL Server magazine some time ago.

  • October 31, 2012 at 1:13 am

    #1554493

    Because reorganise has less to do on a index that's a little fragmented and more to do on an index that's very fragmented.

    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

  • October 31, 2012 at 10:29 am

    #1554694

    sqlfriends (10/30/2012)

    When I use sql server maintenance plan, there is a task called reorganize index.

    If I setup a job to reorgnize an index, for example every 6 hours, should each time be shorter than previous one?

    I mean, does it only reorganize those defragmented, or everytime it wil reorganize all indexes ?

    Thanks

    No matter what your solution for reorganizing indexes is, sometimes the reorganizing will be faster, sometimes slower. It depends on the fragmentation.

    Regards

    IgorMi

    Igor Micev,My blog: www.igormicev.com

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

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