Statistics - 3

  • 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....

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

  • 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
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

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

    Not all gray hairs are Dinosaurs!

  • Good question. Thanks for submitting.

  • learn something

    good answer by pure chance !

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

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