Post reply

Update Statistics & Rebuild index (MaintenancePlan)

  • August 19, 2015 at 7:49 am

    #307023

    Hello

    I hope you are fine.

    I have a questions about Update Statistics & Rebuild index.

    Every day I have a maintenance Job of my DBs where I Include Update Statistics & Rebuild index.

    1-Is it necessary to have these two activities in the maintenance Job or only one?

    In the past I read that was not necessary to have the two process but I’m not sure

    What is your recommendation for Daily Maintenance Job ?

  • August 19, 2015 at 9:04 pm

    #1821241

    lgluna20 (8/19/2015)

    Hello

    I hope you are fine.

    I have a questions about Update Statistics & Rebuild index.

    Every day I have a maintenance Job of my DBs where I Include Update Statistics & Rebuild index.

    1-Is it necessary to have these two activities in the maintenance Job or only one?

    In the past I read that was not necessary to have the two process but I’m not sure

    What is your recommendation for Daily Maintenance Job ?

    If you're talking about what comes canned in an SQL Server "Maintenance Plan", you shouldn't be using either because it will rebuild indexes and stats that don't need it and that's blowing your logfile up in size unless you're not much interested in point-in-time backups, which you should absolutely be interested in.

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

  • August 19, 2015 at 9:06 pm

    #1821242

    One of the more recognized solutions for index maintenance can be found at the following link.

    https://ola.hallengren.com/

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

  • August 20, 2015 at 2:53 am

    #1821292

    Rebuilding an index updates the statistics at the same time. So the order in which you run these things does matter. Understand that if an index is not rebuilt, it may still need to have statistics updated. They are independent issues.

    Another tool to take a look at is Minion Reindex[/url].

    "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

  • August 20, 2015 at 8:49 am

    #1821433

    One other caveat to keep in mind is that while rebuilding an index will update the stats for that index, column-level statistics will not get updated by the rebuild. I've occasionally run into cases where some column-level statistics were quite unrepresentative, and that ended up causing some performance issues.

    It's not so common, but it's something to keep in mind. For those you would need UPDATE STATISTICS.

    Cheers!

  • August 21, 2015 at 6:30 am

    #1821652

    Thanks for your kind support and answer

    I want to know

    1-Is it necessary to execute update static and rebuild index daily ?

    2-I not sure but some comment mentioned that if I rebuild index it’s not necessary update static because the rebuild updated the static, is it true?

    3-The automatic or internal maintenance plan that include SQL Server is good or is better do this activity my sql command ?

    Thanks for your support and comments

  • August 21, 2015 at 6:52 am

    #1821662

    lgluna20 (8/21/2015)

    Thanks for your kind support and answer

    I want to know

    1-Is it necessary to execute update static and rebuild index daily ?

    It really depends on your system. Most of the systems I maintained we rebuilt indexes on a weekly basis and updated statistics daily. In some instances, for some tables or some indexes, we had a more frequent statistics update process run. There's no single way to do this. It really depends on your system. If you're doing it daily now and it's not breaking anything, keep it daily until you begin to notice it's causing pain.

    2-I not sure but some comment mentioned that if I rebuild index it’s not necessary update static because the rebuild updated the static, is it true?

    When an index is rebuilt, it also does a full update of the statistics for that index. So no, you don't need to update the statistics of an index that has been rebuilt. However, there are also table statistics. Those are not updated with the index because they're not associated with an index. In some cases, you may find that you need to update those as well. Indexes don't need to be rebuilt as often as statistics need to be updated though. So you can't just rely on the rebuilt index to guarantee up to date statistics. That's why you can, and should, do both.

    3-The automatic or internal maintenance plan that include SQL Server is good or is better do this activity my sql command ?

    If you mean AUTO UPDATE and AUTO CREATE of statistics, yes, leave that turned on (although, there are some, extremely large, systems where you may want to turn these off, most systems absolutely need them as is). However, the thing to know about the automatic statistics update process is that they only sample the data. If you run UPDATE STATISTICS against a table, index, or statistic, then that scans the complete data set instead of sampling (although you can tell it to sample too). This leads to more accurate statistics. Over time, determined by your system, not any common schedule, the automatic statistics maintenance needs to be overridden with a full UPDATE STATISTICS command.

    In short, this is actually a pretty complex topic, driven by the behavior of your data, your indexes, your queries, your execution plans. You can, and should, have a standard maintenance routine (and using Ola's scripts or Minion Reindex would be the best way), but you may also have to have custom routines, again, depending on your system.

    "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

  • August 21, 2015 at 3:39 pm

    #1821804

    Hello

    Thanks you very much for your answer

    Sorry in case of the questions number 3 I was not clear about it.

    Basically as you know SQL Server include Maintenance plan section where you can create maintenance task with the wizard (eg. Clean Up History, Check Database Integrity Task, Maintenance Cleanup Task, Rebuild Index Task, etc.) therefore my questions is these tasks are OK or is it better to create by SQL command or the result is the same.

    Thanks

  • August 21, 2015 at 4:55 pm

    #1821810

    lgluna20 (8/21/2015)

    Hello

    Thanks you very much for your answer

    Sorry in case of the questions number 3 I was not clear about it.

    Basically as you know SQL Server include Maintenance plan section where you can create maintenance task with the wizard (eg. Clean Up History, Check Database Integrity Task, Maintenance Cleanup Task, Rebuild Index Task, etc.) therefore my questions is these tasks are OK or is it better to create by SQL command or the result is the same.

    Thanks

    See my previous answer above at http://www.sqlservercentral.com/Forums/FindPost1713193.aspx

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

  • August 21, 2015 at 10:29 pm

    #1821818

    Jeff Moden (8/21/2015)

    lgluna20 (8/21/2015)

    Hello

    Thanks you very much for your answer

    Sorry in case of the questions number 3 I was not clear about it.

    Basically as you know SQL Server include Maintenance plan section where you can create maintenance task with the wizard (eg. Clean Up History, Check Database Integrity Task, Maintenance Cleanup Task, Rebuild Index Task, etc.) therefore my questions is these tasks are OK or is it better to create by SQL command or the result is the same.

    Thanks

    See my previous answer above at http://www.sqlservercentral.com/Forums/FindPost1713193.aspx

    Jeff's exactly right in his first post. The maintenance plan is a brute-force approach to database maintenance. It does work that isn't necessary and fully logs those operations. The log file bloats nicely with all the activity. I don't like the maintenance plans for these reasons.

    Like Grant said, database maintenance isn't exactly a simple topic. There's significant complexity involved and "it depends" comes up a lot. If you want to write your own maintenance procedures, there's a lot of learning that has to be done. If you're unwilling or don't have the time, then take a look at the aforementioned products. I don't use either of them, so I can't recommend either one. There are other commercial products available as well.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • August 22, 2015 at 4:35 am

    #1821833

    lgluna20 (8/21/2015)

    Hello

    Thanks you very much for your answer

    Sorry in case of the questions number 3 I was not clear about it.

    Basically as you know SQL Server include Maintenance plan section where you can create maintenance task with the wizard (eg. Clean Up History, Check Database Integrity Task, Maintenance Cleanup Task, Rebuild Index Task, etc.) therefore my questions is these tasks are OK or is it better to create by SQL command or the result is the same.

    Thanks

    Jeff and I both have answered this. You're better off with one of the tools mentioned above than you are with the maintenance tasks for indexes and statistics. The tasks are adequate, but they're not good. For good, Minion or Ola's scripts.

    "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 11 posts - 1 through 10 (of 10 total)

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