Database Maintenance

  • Oracle_91

    SSChampion

    Points: 14548

    Hi All,

    What is the best order while creating maintenance jobs so that sql optimizer can pickup the best execution plan.

    Daily i have 3 jobs configured to run in the midnight.

    job1 : reorgising the indexes

    job2 : integrity checks

    job3 : update statistics

    I believe if i execute the jobs in the below order makes sense , please correct me if am wrong.

    Also, would like to know does index re-org will do auto update stats or do we need to create a separate job or maintenance plan ?

    Order

    -----

    1. Check integrity checks

    2. Re-organise all indexes

    3. update statistics

    Also, can anyone tell me whether index rebuild is better option or index reorganise is fine ?

    Thanks in Advance.

  • colin.Leversuch-Roberts

    SSC Guru

    Points: 52551

    an index re-org will not update stats. Auto update stats will also not update all your stats, so if you want to update stats do it after any index operations.

    I did look at index re-orgs once but ditched in favour of index rebuilds.

    Rebuild indexes ( or re-org if you must )

    do stats

    do constancy checks last

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • Oracle_91

    SSChampion

    Points: 14548

    He Colin,

    Why cant have consistency checks in second place ?

    Any reasons keep checks at last before stats?

    Just wanted to understand logically 😉

  • Welsh Corgi

    SSC Guru

    Points: 116520

    You may want to consider Rebuilding the Indexes depending upon the level of fragmentation.

    It will Update the Statistics when the index is rebuilt.

    http://sqlserverpedia.com/blog/sql-server-bloggers/update-statistics-before-or-after-an-index-rebuild/

    http://blog.sqlauthority.com/2007/12/22/sql-server-difference-between-index-rebuild-and-index-reorganize-explained-with-t-sql-script/

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Oracle_91

    SSChampion

    Points: 14548

    Hi ,

    Both links are not working.

  • Welsh Corgi

    SSC Guru

    Points: 116520

    Sorry about that, they work now.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Malcolm Daughtree

    SSCrazy

    Points: 2373

    Oracle_91 (8/30/2011)


    Hi All,

    What is the best order while creating maintenance jobs so that sql optimizer can pickup the best execution plan.

    Daily i have 3 jobs configured to run in the midnight.

    job1 : reorgising the indexes

    job2 : integrity checks

    job3 : update statistics

    I believe if i execute the jobs in the below order makes sense , please correct me if am wrong.

    Also, would like to know does index re-org will do auto update stats or do we need to create a separate job or maintenance plan ?

    Order

    -----

    1. Check integrity checks

    2. Re-organise all indexes

    3. update statistics

    Also, can anyone tell me whether index rebuild is better option or index reorganise is fine ?

    Thanks in Advance.

    Your jobs will help the overall performance no matter what order you do them. You execution plan will try to use that information but first I'd have a look at the query plans. A good general area is have look at the long running queries and determine whether you have a covering index, whether you are using clustered or non-clustered indexes. Are you 'scanning' or 'seeking', there is a big difference. If you can, are you using the included columns capability. All these thing and more 🙂

    Woot ! Love these indexes

    CodeOn

    😛

  • Welsh Corgi

    SSC Guru

    Points: 116520

    Malcolm Daughtree (9/5/2011)


    Oracle_91 (8/30/2011)


    Your jobs will help the overall performance no matter what order you do them.

    Bad Advice.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • TheSQLGuru

    SSC Guru

    Points: 134017

    never, ever use maintenance plans! get the awesome free and fully documented maintenance suite from ola.hallengren.com.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 9 posts - 1 through 9 (of 9 total)

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