Performance Problem Resolved by Update Statistics

Question

Performance Problem Resolved by Update Statistics

Laura-243449

SSC Rookie

Points: 31
More actions
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?

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

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

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 1

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.

Laura-243449 SSC Rookie Points: 31 More actions · Answer 2

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

SW Skeen Mr or Mrs. 500 Points: 547 More actions · Answer 3

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.

Laura-243449 SSC Rookie Points: 31 More actions · Answer 4

Laura-243449

SSC Rookie

Points: 31

September 21, 2005 at 6:21 am

#591680

There are no index hints in the code.

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 5

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

David Poole-249495 Ten Centuries Points: 1106 More actions · Answer 6

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.

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 7

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.

Jeetendra SSCrazy Points: 2076 More actions · Answer 8

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

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 9

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

cnappoly SSC Enthusiast Points: 130 More actions · Answer 10

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.