Statistics - 3

  • Comments posted to this topic are about the item Statistics - 3

    If everything seems to be going well, you have obviously overlooked something.


    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Thanks Ron for this great question today as well. It required a little bit of reading before attempting it.

    Happy that I got it correct in the end πŸ™‚

    ~ Lokesh Vij

    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post -->[/url]

    Follow me @Twitter

  • Thanks for another great question Ron.

    I choose Yes as I thought it would be sad if there wasn't such an option πŸ˜€

    Need an answer? No, you need a question
    My blog at
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • After extensive search of MSDN and BOL, I (incorrectly) came to the conclusion that the only way to change the frequency was by scheduling a job to update stats.

    Some obscure traceflag does not strike me as a bone fide way of acheiving this. Hurrumph! πŸ˜‰

    Aren't traceflags things left behind by the Microsoft developers for their own purposes, and shouldn't be relied upon.

  • Thanks, good question. Actually guessed at the answer (hoped the specific versions mentioned was a clue), but it's taught me something I didn't know!

    It is interesting that it's been implemented as a trace flag rather than say through sp_configure.

    Maybe it will make it there in a future version or SP.

    Definitely a setting you only want to change if you know what you're doing!

  • This was removed by the editor as SPAM

  • Very informative Ron! Thanks πŸ™‚

    Sujeet Singh

  • Koen Verbeeck (10/9/2012)

    Thanks for another great question Ron.

    I choose Yes as I thought it would be sad if there wasn't such an option πŸ˜€

    Same here. I thought that it wouldn't be worth posting if there wasn't a solution. Thanks for the question Ron.

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • Hmm... I sort of still think IΒ΄m right on this one (and that the answer is no). "Is there a method / setting that will alter the fixed rate of change threshold" I assumed to be no since there is no way to alter the fixed rate. You can however set sql server to instead use a dynamic rate but you canΒ΄t change it to another fixed rate such as 40%... Or have I misunderstood something?


  • A very good question. I learn something new..

    Thanks Ron!!:-)

  • Thanks.

  • Very interesting, thanks for the question!

  • Nice, learnt something new today. Thanks.

  • Thanks for the question!

  • Summary !!

    By default, SQL Server updates index statistics automatically. Frequency of automatic updates depends on number of rows in the table. If you allow SQL Server to update statistics automatically it will use the following rules:

    1. If a table has 6 or fewer rows, statistics will be updated after 6 changes

    2. If a table has 500 or fewer rows, statistics will be updated after 500 changes

    3. If a table has more than 500 rows, statistics will be updated after 20% of the total rows plus 500 rows are changed (INSERTED, UPDATED or DELETED)

    4. SQL Server uses the rowmodctr column of the sysindexes table to determine the number of changes since the last update of statistics

Viewing 15 posts - 1 through 15 (of 20 total)

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