Post reply

Update statistics

  • March 5, 2011 at 3:47 am

    #247741

    Under which circumstance do we need to update statistics?...whats its purpose?.. any real time scenario?...

    Thanks in Advance.....

  • March 5, 2011 at 8:06 am

    #1294987

    When the auto update doesn't do a good enough job and you have poorly performing queries due to incorrect cardinality estimations. Big tables typically

    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

  • March 6, 2011 at 5:08 am

    #1295087

    You'll have to have been observing consistent disparity between the actual data and the statistics. The most frequent place you'll catch this is in execution plans. But it's possible to simply look at the histogram on the statistics and compare that to actual data to begin to see the disparity.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • March 6, 2011 at 9:24 am

    #1295109

    And every once in a blue moon, especially in older versions of SQL server, the statistics can become corrupt, causing SQL server to retrieve data 'the slow way' (explained for simplicity sake).

    The solution is not only to update the statistics yourself, but to specify WITH FULLSCAN. This will totally rebuild the stats and heal any stat errors. Course, this also causes a full read of all pages in that table so it'll take longer than the default sample size.

    Jim

    Jim Murphy
    http://www.sqlwatchmen.com
    @SQLMurph

  • March 7, 2011 at 6:45 am

    #1295303

    Given the question the OP asked, I imagine he/she has little ability/knowledge about query plans and how statistics are used to generate them. Time for some reading, since this is a VERY deep (and critical) subject for achieving and maintaining an efficient SQL Server. I recommend this white paper as a start (there is an easily found one for 2005 as well): http://msdn.microsoft.com/en-us/library/dd535534(v=SQL.100).aspx

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 5 posts - 1 through 4 (of 4 total)

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