Post reply

Statistics - 3

  • October 8, 2012 at 8:46 pm

    #268198

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

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

    Ron

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

  • October 8, 2012 at 10:52 pm

    #1547017

    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 --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • October 9, 2012 at 12:18 am

    #1547028

    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 https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • October 9, 2012 at 2:00 am

    #1547058

    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.

  • October 9, 2012 at 2:51 am

    #1547073

    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!

  • October 9, 2012 at 3:58 am

    This was removed by the editor as SPAM

  • October 9, 2012 at 4:01 am

    #1547112

    Very informative Ron! Thanks 🙂

    Sujeet Singh

  • October 9, 2012 at 5:25 am

    #1547162

    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

  • October 9, 2012 at 6:45 am

    #1547196

    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?

    Cheers!

  • October 9, 2012 at 7:03 am

    #1547202

    A very good question. I learn something new..

    Thanks Ron!!:-)

  • October 9, 2012 at 7:05 am

    #1547203

    Thanks.

  • October 9, 2012 at 7:21 am

    #1547212

    Very interesting, thanks for the question!

  • October 9, 2012 at 7:23 am

    #1547214

    Nice, learnt something new today. Thanks.

  • October 9, 2012 at 8:40 am

    #1547245

    Thanks for the question!

  • October 9, 2012 at 8:59 am

    #1547257

    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