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.
Best Answer
This issue is called parameter sniffing.
Later versions of SQL Server give you more options in dealing with it such as
OPTION (RECOMPILE)orOPTIMIZE FORhints.You might try declaring variables in the stored procedure, assigning the parameter values to the variables and using the variables in place of the parameters as it sounds as though most of the time you are getting a reasonably satisfactory plan.
Normally the most catastrophically bad plans are those compiled for parameters with very high selectivity but ran with parameters with low selectivity.
Assuming the plan generated is more robust with this approach and satisfactory for all parameter values then the advantage of this approach over that suggested by JNK is that it does not incur a compilation cost for every call.
The disadvantage is that for some executions run time might be greater than for a plan tailored specifically for those parameter values so it is a trade off of compile time vs execution time.