Strange problem with SQL Server procedure execution plan

Question

I was wondering if you guys could help me get to the bottom of a weird problem I have recently had on SQL Server.

I have a stored procedure (lets call SPold) which is reasonably large with a lot of calculations (can't possibly do this in app as info for around 6000 users needs to come back in a one-er (I reduce this to 1000 based on Surname)). The stored procedure usually executes in a couple of seconds, and is called once every couple of minutes.

Now this morning, the stored procedure was suddenly taking 4-10 times as long to execute, causing a number of timeouts. I discovered that by making a copy of the procedure with a new name (SPnew) and executing, I would get the fast execution times again. This indicated to me that the execution plan was the problem with the original, SPold, so I decided to execute it with recompile. This would return the results a quicker (although not as fast as SPnew), but subsequent calls from users to SPold were once again slow. It was like the new plan wasn't being kept.

What I have done is to fix this is put Exec SPnew into SPold, and now calls to SPold are returning fast again.

Does anyone have any idea what is going on here? The only thing that updated overnight was the statistics, although I think that this should affect both SPold and SPnew.

Answer 1

Sounds like you are experiencing an incorrectly cached query plan due to parameter sniffing.

Can you post the stored procedure?

• Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005

• I Smell a Parameter!

In SQL Server 2005, you can use the OPTIMIZE FOR query hint for preferred values of parameters to remedy some of the problems associated with parameter sniffing:

OPTIMIZE FOR Instructs the query optimizer to use a particular value for a local variable when the query is compiled and optimized. The value is used only during query optimization, and not during query execution. OPTIMIZE FOR can counteract the parameter detection behavior of the optimizer or can be used when you create plan guides. For more information, see Recompiling Stored Procedures and Optimizing Queries in Deployed Applications by Using Plan Guides.

Although SQL Server 2005 does not support OPTIMIZE FOR UNKNOWN (introduced in SQL Server 2008) which will eliminate parameter sniffing for a given parameter:

OPTION (OPTIMIZE FOR (@myParam UNKNOWN))

You can achieve the same effect in SQL Server 2005 by copying the parameter into a local variable, and then use the local variable in the query.

Answer 2

I've also encounterred two "strange" cases with Sql Server 2005, which might relate to your problem as well.

In the first case my procedure executed prety fast, when being run as dbo, and it was slow when being run from the application, under a different user account.

In the second case the query plan of the procedure got optimized for the parameter values with which the procedure was called for the first time, and this plan was then reused later for other parameter values as well, resulting in a slow execution.

For this second case the solution was to copy the parameter values into local variables in the procedure, and then using the variables in the queries instead of the parameters.