I have a SQL Server 2005 that has become unpredictable of late, and I'm scratching my head as to why. Queries that executed in seconds are changing plans and taking minutes (taking the time in full table scan or index spool). Now the first and most obvious thing is, the statistics are obsolete causing the optimizer to get confused but I am convinced this is not the case – firstly because the underlying data isn't significantly changing (e.g. adding one day's data ontop of a year's data already in a table) and secondly because Auto Create Statistics and Auto Update Statistics are both true. However the optimizer is getting confused; running the SQL in the Tuning Advisor gives me lots of multi-column CREATE STATISTICS statements which do seem to fix it (until the next bit of SQL misbehaves).
Any ideas of a strategy I can use to approach root-causing this? Any why the "normal" statistics aren't sufficient?
Best Answer
If your top wait is SOS_SCHEDULER_YIELD, then it would appear you have some pressure on CPU. But this could be a result of something else, such as your design no longer being sufficient for your queries. I know you said that you are only adding one day's worth of data, but you could have hit a tipping point.
How are your queries being issued? Is it dynamic SQL? Are you using stored procedures? Are you using sp_executesql? Is it possible that you have a case of parameter sniffing? What does your db design look like? What are the PK and FK relationships?
Do you have an example of a good plan? If you are able to determine a good plan, you could use plan guides to force the query to execute in a specific way.
Can you give an example of a good plan gone bad?
Lastly, go grab a copy of sp_whoIsActive (http://whoisactive.com/) from Adam Machanic and use that to determine more about the queries that are running. And if you want to be able to capture the output from sp_whoIsActive, go here http://www.littlekendra.com/2011/02/01/whoisactive/