Statistics - 3

  • LadyRuna

    SSCrazy

    Points: 2174

    Thanks for posting this! I'd been hoping that there was a secret way of forcing SQL to automatically update stats more frequently. Our usual solution has always been to create a SQL job that runs update stats frequently... but this trace flag may be just the trick. I'll try it out on a test server....

  • asifkareem

    Ten Centuries

    Points: 1089

    Thanks For good Question, its allow reading to different Article before you are going to attempt. but at the end +1

  • SQLRNNR

    SSC Guru

    Points: 281243

    saurabh.x.sinha (10/9/2012)


    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

    FWIW - In SQL 2008 and up it uses colmodctr to track those changes.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Miles Neale

    SSChampion

    Points: 13147

    I answered with hope there was such a thing and won. Excellent!

    Not all gray hairs are Dinosaurs!

  • Britt Cluff

    SSCertifiable

    Points: 5083

    Good question. Thanks for submitting.

    http://brittcluff.blogspot.com/

  • jfgoude

    SSCrazy

    Points: 2586

    learn something

    good answer by pure chance !

Viewing 6 posts - 16 through 21 (of 21 total)

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