Post reply

Statistics Update

  • December 12, 2007 at 5:28 am

    #179868

    Hi Experts,

    I am sorry I sound dumb to myself but I have never worked in a environment where I ever needed to update statistics Primarily cause (i) I never knew I had to which was cause I never realized I had to (ii) I only knew they had to be updated but never knew when and what decides the reason to update them ?

    But I wish to know what is the criteria to update statistics. Should they be updated each day cause even rebuild index is a costly operation ??? Any pointers please

  • December 12, 2007 at 5:59 am

    #759356

    Generally statistics are automatically updated by SQL server. The only time it's necessary to update them manually is when the auto update is disabled (which it should never be) or on larger tables where the threshold for an auto update is large.

    I only update stats manually if I know that out of date stats on a particular table is causing a problem with a query. The most commonly affected are tables with clustered indexes of a dateinserted column and lots of range queries on that column.

    You can see when a statistic was last updated with the Stats_date function.

    SELECT OBJECT_NAME(object_id), name, STATS_DATE(object_id,index_id) FROM sys.indexes

    Index rebuilds will update statistics. Index defragment won't

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 1 (of 1 total)

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