I'm trying to understand an issue we're having with SQL Server 2000. We are a moderately transactional website and we have a stored proc called
sp_GetCurrentTransactions which accepts a customerID, and two dates.
Now depending on the dates, and the customer, this query can return anything from zero to 1000s of rows.
The problem: what we've experienced is that suddenly we will get a number of errors (typically
Execution Timeout Expired or similar) for a particular client while they try execute that stored proc. So we examine the query, run it in SSMS and and find that it takes 30s. So we recompile the stored proc and -bang- it runs now in 300ms.
I've spoken to our DBA about this. He has told me that the database created a query plan when we created the stored proc. He said that it was a good plan for that set of parameters, but if you throw a certain set of parameters at it, then the plan will not be the best plan for that data, and so you will see it running slow.
The options presented to me are the move that problem query from a stored proc and back into dynamic SQL that has it's execution plan created on every run.
This feels like a step back to me and I feel like there must be a way around this. Is there any other way to deal with this issue?
Any and all responses are appreciated.