6

I've just worked through a problem at a clients site which it turned out was caused by the statistics being wrong which caused the Optimizer to time out. Running exec sp_updatestats fixed the problem and all is now good.

What I am a bit confused about now is how did the statistics get out of line in the first place?

The database has both auto_create_stats and auto_update_stats switched on. So SQL Server should have kept the statistics up to date without any intervention.

So why did it fail in this instance?

This client had recently upgraded their database server. They handled it themselves, so I'm not exactly sure what procedure they went through, but I can't imagine it was anything more complicated than backing the database up on the old server and restoring it on the new one. Could this have caused the glitch somehow?

Improve this question
3
  • 2
    I downloaded the plan from your previous question. At one point it has 4,535,620 rows as the Left input to a Left Outer Join against a table spool with estimated 1 row and somehow ends up with an estimated 630,451,000 rows coming out. Not sure how that is possible. That plan makes my head hurt though. 50 joins!
    – Martin Smith
    Feb 3, 2012 at 16:17
  • I routinely use the sp_MSForEachTable procedure in a SQL Agent job to update database statistics (but then I do not turn on auto update statistics).
    – jl01
    Feb 3, 2012 at 16:23
  • @MartinSmith Believe me, it makes my head hurts more! :-) With updated statistics that table spool becomes a Compute Scalar returning an estimated 84 rows, this gets Right Joined to 823 estimated rows. I would like to understand why that went so wrong...
    – Mongus Pong
    Feb 3, 2012 at 16:38

2 Answers 2

Reset to default
2

I'll guide you to an answer to an older question. Please read the comments, too, they are very valuable.

In short, MSDN says:

"Insert Operations Occur on Ascending or Descending Key Columns Statistics on ascending or descending key columns, such as IDENTITY or real-time timestamp columns, might require more frequent statistics updates than the query optimizer performs. Insert operations append new values to ascending or descending columns. The number of rows added might be too small to trigger a statistics update. If statistics are not up-to-date and queries select from the most recently added rows, the current statistics will not have cardinality estimates for these new values. This can result in inaccurate cardinality estimates and slow query performance.

For example, a query that selects from the most recent sales order dates will have inaccurate cardinality estimates if the statistics are not updated to include cardinality estimates for the most recent sales order dates.

After Maintenance Operations Consider updating statistics after performing maintenance procedures that change the distribution of data, such as truncating a table or performing a bulk insert of a large percentage of the rows. This can avoid future delays in query processing while queries wait for automatic statistics updates."

So you would benefit from searching for statistics that were not updated recently.

Improve this answer
2
  • Not sure this answers the Q since auto updates should update on the same trigger mechanism as sp_updatestatistics
    – JNK
    Feb 3, 2012 at 16:15
  • I'm not very sure of that. If auto updates worked but didn't update some stats, while sp_updatestats did that, it looks like there are some differences.
    – Marian
    Feb 3, 2012 at 17:55
0

I'll tell you what happened in our situation. We run ETL on a bunch of tables. So there are transformations and bulk loads happening. We had changed the schema of some tables. We didn't realize we did not apply the clustered and non-clustered indexes until after we kicked our ETL process off again. It performed slowly. So then we ended up truncating some of our tables, and re-applying the primary key and clustered indexes (sometimes with multiple columns) on those same tables and on other tables. Then kicked off the ETL scripts again. Well, things still performed horribly slow. So then I happened to read about this stored procedure. Performance was back to normal again. Hurray! So obviously, changing the PK constraints and clustered indexes on those PKs did something bad with the query optimization.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.