In SQL Server 2005 SP3, I have a stored procedure performing fairly complex aggregation and formatting for reporting. The stored procedure normally executes in 4-5 seconds. Without anything changing (data, code, server hardware, statistics, etc),
the stored procedure will start executing in 20 minutes instead of 4-5 seconds. This application is in the "testing" phase... data is changing only minimally. There is no other activity on the database server, and the execution plans match whether
running 4-5 seconds or 20 minutes. The parameter values supplied to the stored procedure are static as well.
I have attempted to flush the plan cache, to recompile the proc, to update statistics, to update indexes, to drop/create the procedure... nothing causes it to go from 20 minutes back to 5 seconds. And then suddenly, the next day - back to 5 seconds...
even though nothing within the SQL instance has changed.
Anyone have any thoughts on what could cause this behavior?
Thanks in advance.
View Complete Post