Post reply

Performance Problem Resolved by Update Statistics

  • September 21, 2005 at 4:29 am

    #89996

    We are experiencing a performance problem on a SQL 2000 database.  It is always resolved by running an sp_updatestats.  I have auto update stats turned on and also have a scheduled job which runs an sp_updatestats every morning at 4:30am on the database. 

    Even if very few data modifications occur between the time the scheduled sp_updatestats occurs and the times we experience the problem - running an sp_updatestats always resolves it.

    The performance problem is seen in Perfmon - the processors flat line at 100% and drops dramatically as soon as the update stats has completed.

    Can anyone tell me what could be causing the stats to become so quickly out of date - when minimal data modifications have taken place?

  • September 21, 2005 at 5:13 am

    #591662

    I've seen this happen frequently, and it may not be because the stats are out of date.

    It could be that SQL Server has cached a bad query plan for a stored procedure, based on a query that is best served by a table scan (e.g. surname = 'Smith'). That plan is cached and used for other executions that would work better using an index seek (e.g. surname = 'Zachary').

    The act of running a statistics update will invalidate any cached plans using the table whose stats have been updated. SQL Server then creates a new plan, based on a query that uses an index seek.

  • September 21, 2005 at 5:23 am

    #591666

    Thanks Ian - how can I identify which query is causing this to happen?

  • September 21, 2005 at 6:10 am

    #591676

    Could there be index hints forcing the plan to be built poorly that need removed? Statistics won't help a bad plan being built if the code is forcing a path to be used that may not be optimal.

  • September 21, 2005 at 6:21 am

    #591680

    There are no index hints in the code.

  • September 21, 2005 at 6:27 am

    #591685

    It could also be something as simple as WHERE FieldX <> 'V' when there are only a few items and when changed to WHERE FieldX IN ('A', 'B', 'C') the execution changes dramatically FOR THE BETTER.

    Please Note:  This did happen to me....



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • September 21, 2005 at 6:46 am

    #591699

    SQL Server doesn't like inequality comparisons.

    Equality operations are generally fastest

    Greater than or less than operators are next

    Inequality is last.

    I find that WHERE FieldX IN('A','B','C') works better for me also, provided that the list isn't too big.

  • September 21, 2005 at 7:33 am

    #591750

    All of the other suggestions are sound ideas, but shouldn't be affected by a stats update.

    SQL Profiler will be your friend here. Look for queries with an abnormally high number of reads and/or cpu at the time you are experiencing the slowdown.

  • September 22, 2005 at 5:40 am

    #592150

    The Best way to solve this by using the Sql Profiler that can inform you about the Long running Queries as well as it can track the estimated amount of CPU Time.

    Hope so this resolves your Problem.

    Thanks.

    Kindest Regards,

    Jeetendra

  • September 22, 2005 at 1:22 pm

    #592374

    You don't have to do any diagnostic work on this. Just change to a 'best practice'. That practice is: When running UPDATE STATISTICS on a table the very next thing is to execute sp_recompile TALE_NAME ! If your performance does not return to normal, then you can execute profiler, or scan SQL as you choose.

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • December 19, 2005 at 11:35 am

    #610387

    Please make sure you sun a sp_recompile on all tables or procedures after running sp_updatestats..Execution plans will be marked invalid in memory. So, just run this after update stats and that should resolve the issue.

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

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