SQL Server Performance

does rebuilding indexes update statistics?

Discussion in 'SQL Server 2005 General DBA Questions' started by Trev256b, Aug 9, 2011.

Page 1 of 2

  1. Trev256b Member

    somebody told me that when you rebuild indexes via a maintenance plan then the statistics are updated automatically. is this correct as i don't recall reading this - and it doesn't seem to make sense?

    is there a good link/ white paper on this to expalin how these are separate?
    Trev256b, Aug 9, 2011
    #1

  2. Luis Martin Moderator

    Luis Martin, Aug 9, 2011
    #2

  3. satya Moderator

    You should take your time in reading the SQL SERVER Books online first, it is clearly documented about the REINDEX and REORG methods.
    satya, Aug 10, 2011
    #3

  4. Trev256b Member

    thanks - will do - I couldn't spot it when I scanned BOL
    Trev256b, Aug 10, 2011
    #4

  5. Trev256b Member

    Thanks to Luis and Satya - but after reading the Link from Luis - it looks like the answer is actually 'No - rebuilding the indexes will not automatically update statistics - you will need to select or run a separate job to update the statistics'. It looks like you can select a separate option to rebuild the statistics.
    I don't think I was clear enough when I asked the question, but what I meant to ask was: 'If you rebuild the indexes (and only rebuild the indexes) will rebuilding the indexes automaticallty update the statistics, or will you need to select the update statistics option in the maintenance plan or run a separate job to update the statistics?'
    I hope this is clear, as after reading the links it looks like you have to select a separate option. I want to make sure about this as I want to ensure the statistics are updated and I'm not hoping that by just rebuilding the indexes then the statistics will be updated at the same time. Many thanks for checking this out.
    Trev256b, Aug 10, 2011
    #5

  6. Luis Martin Moderator

    When you rebuild the indexes the stats are rebuilt as well.
    Now if your plan is rebuild indexes each week end then, may be, you can update statistics between weeks end.
    Luis Martin, Aug 10, 2011
    #6

  7. satya Moderator

    I believe you haven't gone through the Books Online information, the reindex operation will take care of the table statistics by arranging the pages in order. Also as a part of REBUILD INDEX process you can specify whether you want to disable the stats update or not, see reference from BOL:
    Specifies whether distribution statistics are recomputed. The default is OFF.
    ON

    Out-of-date statistics are not automatically recomputed.
    OFF

    Automatic statistics updating are enabled.
    To restore automatic statistics updating, set the STATISTICS_NORECOMPUTE to OFF, or execute UPDATE STATISTICS without the NORECOMPUTE clause.
    satya, Aug 11, 2011
    #7

  8. Trev256b Member

    Thanks guys. yes - i've actually read BOL - but for some reason it isn't 100% clear.
    1) I can see why the stats would be updated when indexes are rebuilt if the pages are re-ordered, however, are you saying when the indexes are rebuilt and pages re-ordered SQL Server automatically recompiles the statistics? This is what I assume is happening but I can't find anywhere that explicitly says this. Sorry to be so precise but I just want to understand exactly what is going on.
    2) Also, I've seen the option to auto recompute out-of-date statistics. But why is this option possible to turn on if rebuilding the indexes always does this?

    Thanks - once I've cleared this up my understanding will be 100%. :)
    Trev256b, Sep 4, 2011
    #8

  9. Luis Martin Moderator

    2) Also, I've seen the option to auto recompute out-of-date statistics. But why is this option possible to turn on if rebuilding the indexes always does this?
    Because not all people use to re-index as part of maintenance plan.
    Luis Martin, Sep 4, 2011
    #9

  10. Trev256b Member

    ok - so are you saying ONLY when you rebuild indexes via a maintenance plan the statistics are updated automatically?
    Trev256b, Sep 4, 2011
    #10

  11. Luis Martin Moderator

    No. What I said is: you have the option "auto update statistics" and " auto create statistics". So the option is there. You can turn off if you rebuild as part of maintenance plan, and those options are there because not all sql server users (or companies) have one DBA inside or outside.
    Luis Martin, Sep 4, 2011
    #11

  12. satya Moderator

    Just another question that how frequently do you perform UPDATE statsitics on volatile tables?
    satya, Sep 8, 2011
    #12

  13. preethi Member

    Just to clarify, my understanding is, rebuilding indexes does not mean all the statistics will be updated automatically. rebuilding indexes will update only the connected statistics. There could be more statistics on a table which are not covered by an index, and they needs to be updated manually.
    preethi, Sep 9, 2011
    #13

  14. MichaelB Member

    Peethi speaks the truth. you can have stats on fields that are not part of any index. Too many stats can be a bad thing too. but if you are only joining and filterning on indexed fields then a rebuild will update the stats you care about.
    MichaelB, Sep 13, 2011
    #14

  15. Trev256b Member

    michaelb - are u saying that if a db is joining/filtering on fields that are not indexed then i should run a separate update stats job? sounds like it would be advisable.
    Trev256b, Sep 25, 2011
    #15

  16. FrankKalis Moderator

    It depends! :)

    In my understanding does an index rebuild (not reorganise!) only update the statistics for the columns in the index. If you have other statistics objects in your database, that were either created by you or SQL Server, and are needed for queries, then you can help SQL Server by maintaining those statistics manually yourself or let SQL Server do this for you by auto updating the statistics. But that depends on whether this setting is enabled or not on the database level. In any case, do not blindly update the statistics after rebuilding indexes. See this for an explanation why:
    http://www.sqlskills.com/BLOGS/PAUL...building-Indexes-and-Updating-Statistics.aspx

    Also, if you are joining/filtering on column(s) that are not indexed, you should probably consider having an index on them. :)
    FrankKalis, Sep 25, 2011
    #16

  17. preethi Member

    Jumping before Michael :)
    I prefer to have indexes on joining columns if it happened to be on large tables. (Joining on a column means you already have a relationship. It has more chance of happening) It will increase the performance of the query.
    Where ever you don't have index, you need to have stats,
    Since auto update statistics could happen even during peak hours, I prefer to have an off peak time job to update stats.
    Hope Michael will confirm this.
    preethi, Sep 25, 2011
    #17

  18. preethi Member

    hmmm... it not only I who jumped before Michael answers :)
    preethi, Sep 25, 2011
    #18

  19. FrankKalis Moderator

    I'm not sure I understand you here. You will have statistics on indexed columns as well as (un)indexed ones. Depending on that statistics the optimizer may decide on whether to use the index or not.
    Well, unless you have turned it off altogether, stats auto update is triggered by execution of a plan and/or by the optimizer. For a table with heavy data modification activities, it's not all that unlikely to get statistics update throughout the day. And usually it happens when you don't want it to :)
    FrankKalis, Sep 26, 2011
    #19

  20. preethi Member

    I should be more specific in my communication :) This is what I was trying to say....
    • Statistics will not be there in all columns, and it is better to create statistics on columns used in JOIN /Where when they are missing.
    • I Prefer to have index on join columns - which automatically creates statistics.
    • If you decide not to have index on a particular column used in WHERE clause, and if there is not statistics available, create one.

    This is exactly what I was trying to say.
    I was refering to large tables only. On small tables, the impact is minimal.

    Sorry for the confusion and thanks Frank for your helping hand..
    preethi, Sep 26, 2011
    #20
Page 1 of 2

Share This Page