I had a server with some issues the other day (SQL 2005 SP3). I tracked down a job that was running every 5 minutes that was taking 3 minutes to run. Looking at the history it was previously running in under a second.
After looking at the plan it was doing a clustered index scan on a large table. I run sp_updatestats every morning at 3:00 AM and when the job ran at 7:40 AM it took under a second; when it ran at 7:45 AM it started taking 3 minutes. No major changes or data loads occurred from what I can tell between that time. When I ran sp_updatestats again, the plan changed to an index seek and execution time reduced to under a second again.
I am trying to determine what may have caused the query to start using the "Bad Plan" at that time especially since stats were just updated and no major changes to the data occurred. Any ideas?
Does the job have IF ...ELSE statements?
Is it using a proc?
I belive that sp_updatestats clears the proc cache. So that would resolve the slow performace as new stats are used.
answered Oct 22, 2009 at 04:37 PM
Based on the various comments, this may not be an answer for your situation, but I'd like to describe a similar situation we had with a stored procedure and how we resolved it.
A certain procedure was running fine until one day it kept timing out for one of our customers. Upon investigation, we learned that their dataset was roughly twice the average size. That said, the query still should have executed in less than 30 seconds. We ran sp_recompile for that sproc and tried again. This time, it worked fine. Unfortunately, the same problem occurred again 2 days later when the same customer had another large dataset.
We determined the problem was caused by a nightly reindex job that was running. (This was added by a Microsoft consultant and was written to only reindex if fragmentation was over a certain threshold.) We tried adding sp_recompile for the problem stored procedure after the reindex, but it didn't help. Then the consultant came up with a plan that worked. We ran sp_recompile and then immediately ran the procedure for a large dataset. This forced SQL Server to cache the plan for the large dataset. That change was made months ago, and we haven't had a single complaint since.
It turns out the cached execution plan generated for a large dataset was much better for large ones and only very slightly slower for small datasets. On the other hand, the plan generated for a small dataset was optimized for that but terrible for a large one.
I don't know if this will help for the original question, but it may help others with similar issues.
answered Oct 23, 2009 at 01:19 AM
From the sounds of it, it's probable that you have some parameters passed to your query that cause a bad plan and some that cause a good plan, independent of the statistics. You could run a server side trace to try to capture recompile events or take a look at the sys.dm_exec_query_stats to see when the plan was compiled. To fix it you would want to address it like it was a parameter sniffing problem (which it sort of is). Either use the OPTIMIZE FOR query hint and supply a good parameter, or use local parameters to get a more generic plan. You might even consider creating the query WITH RECOMPILE to get a new plan each time. It really depends on if you can afford the recompile cost.
answered Oct 23, 2009 at 09:00 AM
Grant Fritchey ♦♦