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?