locked
statistics RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi All,

    I need some clarifications on update stats.

    1. I have seen in cx's scenario's where they have a maintenace plan like

      checkdb --> re-organize index -> rebuild index - > update stats 

    Question 1:  I dnt thick this is correct. Either we need to go for reorg or rebuild based on fragmentation level. I dnt really understand what they are trying to achieve do so,

    Question 2 : As per knowledge, the rebuild index will update the stats , then why do we need to use again update stats again ?  is there something we are missing updating stats on other columns which will updated using update stats? if so, what actually will get updated ? please correct me if i am wrong.

    Question 3: if i have AUTO_UPDATEStats ON on the database , is there any chance of getting stats getting updated wrongly or in the first place is that right thing to turn ON AUTO_STATS option on the database?

    Thanks in advance.

    Thursday, November 1, 2012 1:13 PM

Answers

  • Question
    Sign in to vote
    1
    Sign in to vote

    1: You are correct. No need to do both reorg and rebuild.

    2: You are correct again. You'd want to chose "column statistics only" if you first did rebuild. Cleary the person who configured this maint plan jst did "next, next, next", so to speak.

    3: Auto update is nice to have. But since it will rather agressively (for parge tables) sample data, you might want to disable it for tables/indexes where it doesn't play nice. You can do that using sp_autostats. The reason to only *only* rely on auto-stats is that it doesn't kick in before you modified 20% (slightly smplified) of the data, which is quite a lot.

    Tibor Karaszi, SQL Server MVP | web | blog

    • Proposed as answer by Kieran Patrick Wood Thursday, November 1, 2012 1:47 PM
    • Marked as answer by Maggie Luo Tuesday, November 13, 2012 10:03 AM
    Thursday, November 1, 2012 1:18 PM