4

I would like to know if the following is an error in our setup or a bug in SQL Server: if we run a certain stored procedure with three parameters, it takes about 3 minutes.

CREATE PROCEDURE [dbo].[ourProcedure] 
    @param1 INT,
    @param2 INT,
    @param3 DATETIME
AS
BEGIN...

If we run the same procedure, but in the creation we have created local copies of the parameters, it takes only 11 seconds!

CREATE PROCEDURE [dbo].[ourProcedure] 
    @param1_x INT,
    @param2_x INT,
    @param3_x DATETIME
AS
BEGIN
    DECLARE @param1 INT 
    DECLARE @param2 INT 
    DECLARE @param3 DATETIME

    @param1 = @param1_x
    @param2 = @param2_x
    @param3 = @param3_x
...

Can someone tell my WHY? Why doesn't SQL Server handle parameters like C#?

Improve this question
5
  • 1
    Can you show the rest of the SP? Or better: Compare the query plans between the two variants. I Think it coudl be related to some sort of parameter sniffing. It is not a "sql performance" issue (as in: the interpreter) but I think it is a different query plan for some selects you generate. Commented Apr 9, 2014 at 8:03
  • No, that's can't be the cause for slowness of the SP. Could be the query inside SP. post the query. Commented Apr 9, 2014 at 8:08
  • "slowness of the SP" Huh? Did you read my question? The ONLY thing that differs the SPs are that I added local variables that copies the parameters. Commented Apr 9, 2014 at 8:10
  • Have you tried to create the SP WITH RECOMPILE? Is it still the same? Commented Apr 9, 2014 at 8:10
  • @TomasPastircak Yes, we tried that. Commented Apr 9, 2014 at 8:11

1 Answer 1

Reset to default
6

This is what is usually called "parameter sniffing". The thing is that SQL Server optimizer uses values of the parameters along with internal statistical information on distribution of values to estimate cardinalities for the values passed into the procedure, and produce an execution plan. However, this can go wrong for many reasons. Other point to make is that the execution plans are cached so the procedure is being optimized just the first time it's executed. If parameter used for optimizing the procedure are different enough from the current ones it could lead to bad performance.

Optimizer cannot use values of variables to do the same, because the variable assignement is part of the very same batch that's being optimized and not known beforehand. In this case it uses heuristics and averages which results probably in different execution plan.

In short, you have two different execution plans here, the first one also probably optimized for different set of parameter values.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Wouldn't RECOMPILE and clearing the cache solve this? (Note: We tried without succes) At least so you would get the same performance on running the SP again the first times?
Recompiling will solve only the cached plan issue, but (because of the variables used in the second procedure) you'll still get two different plans. Can't say why the first plan is inefficient without looking at the execution plan (and I dont mean just the picture of it)
@JoakimM If you add option (recompile) to the query in the SP you should get the same in both cases. And by the the looks of it you should get the bad plan in both cases. Another thing to test is to use option (optimize for unknown) on the query. Then you should still get the same plan as well but it would be the good plan since you are optimizing without using the known values in the parameter.
@MikaelEriksson The first SP is already optimized with current parameter values (OP cleared the cache) but still the execution plan is less efficient than the second one. I'd save option(recompile) and option(optimize for unknown) as a last resort and try to find reason why the bad plan was produced in the first place.
@dean I agree, figuring out why there is a bad plan is what should be focused on here. I just tried to point out the difference between using with recompile on the SP and option (recompile) on the query. If you have with recompile on the SP and using local variables the end result is the same as using option(optimize for unknown). The values used is unknown to the optimizer. But if you use option (recompile) on the query then the values is known even in the case where you use local variables.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.