Post reply

Performance issue due to update statistics

  • November 12, 2009 at 3:13 am

    #213655

    I have got a database backup from production system with the reported issue of performance degradation of SP execution.

    Once I update statistics by sp_updatestats / update statistics with fullscan on that table the problem resolved.

    The SP is using two large tables.

    The Auto Update Statistics property is true for all the indexes and statistics for the tables moreover periodic maintenance task update the statistics. Stats_Date showing last updated timestamp as last night for all the indexes.

    After the last statistics update date approx 20k records have been added to one table and approx 100K records added to the other table.

    I have the following queries . It will be really great if any one can help me ...

    -- STATS_DATE still showing last night date although there are 100K records are added today. is it

    expected behaviour or there is some issue in auto update statistics operation

  • November 12, 2009 at 3:15 am

    #1078223

    which sql version are you on?

    2k / 2k5 and service packs please...

  • November 12, 2009 at 3:20 am

    #1078225

    It is on SQL Server 2008 with Compatibility level 90

  • November 12, 2009 at 3:32 am

    #1078231

    Expected behaviour. Stats are only updated after a certain threshold of rows change. On larger tables that's 20%. If you find that you need to manually update stats then either add an UPDATE STATISTICS after whatever adds the 10k rows or schedule it regularly enough that the problem goes away.

    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

  • November 12, 2009 at 3:41 am

    #1078234

    thanks a lot for the info.

    The table has almost 10 mn records and for today only 100K has added so as per the default threshhold the behaviour is fine.

    But the intresting thing is that the SP execution time is improved from 150 secs to 10 secs after updating the statistics.

    Is it also an expected behaviour or there could be something wrong with SP query.

    For your info the index fragmentation for the table is less than 5%

  • November 12, 2009 at 4:29 am

    #1078250

    Hi Gail,

    you have mentioned the non updation of statistics is an expected behaviour for this case as the data insert volume not yet reached 20% till last statistics update. But when I executed sp_updatestats (which update statictics only if it requires) updates statistics for all indexes and other statistics for this tabel.. so statistics update was required and my SP performance also improved .. so should we need to configure some schedule task to update statistics after 20K record insert or so without waiting for the implicit stat update by sql server?

    Pls guide..

  • November 12, 2009 at 5:24 am

    #1078268

    Bhaskar Basak (11/12/2009)

    thanks a lot for the info.

    The table has almost 10 mn records and for today only 100K has added so as per the default threshhold the behaviour is fine.

    But the intresting thing is that the SP execution time is improved from 150 secs to 10 secs after updating the statistics.

    20% is only the threshold for triggering an update. It's not the point where the query plan goes wrong. That can be a much lower number of changes

    Bhaskar Basak (11/12/2009)

    you have mentioned the non updation of statistics is an expected behaviour for this case as the data insert volume not yet reached 20% till last statistics update. But when I executed sp_updatestats (which update statictics only if it requires) updates statistics for all indexes and other statistics for this tabel

    sp_updatestats dosn't work on the 20%. It'll update stats that have had at least one row change since the last stats update.

    so should we need to configure some schedule task to update statistics after 20K record insert or so without waiting for the implicit stat update by sql server?

    Well you've seen a dramatic performance increase by doing so, so yes.

    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

  • November 12, 2009 at 5:47 am

    #1078280

    thanks a lot for your valuable guidence.

    sp_updatestats has any locking ipmact on the the table during the execution of the command. Can we run it for few tables only instad of whole database.

  • November 12, 2009 at 7:32 am

    #1078338

    SP_updatestats takes no locks. It always runs on the entire database. If you want to update just a table, use UPDATE STATISTICS

    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

  • November 18, 2009 at 5:17 am

    #1080800

    Thats a lot of usefull info Gail.. thanks

    "Keep Trying"

Viewing 10 posts - 1 through 9 (of 9 total)

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