1

I have a stored proc that takes 2 dates as parameters. The dates are used to specify a range of dates for the first in a series of CTEs to limit the scope.

When I run the query in SSMS as a query, it runs in 15 seconds and the query plan shows 81% on a sort.

When I make it a proc with the two date variables as parameters, it runs 19 MINUTES. 46% is on a clustered index scan.

Removing the parameters from the stored proc and adding them as variables inside the proc, it's back to 15 seconds and 81% on a sort.

Can someone explain to me why this would be?

Improve this question
5
  • 3
    It sounds to me like parameter sniffing. Check blogs.msdn.com/b/turgays/archive/2013/09/10/… , there are some workarounds.
    – a1ex07
    Jun 2, 2014 at 16:24
  • 4
    Different plans due to (a) different permissions (b) different SET options (c) different query text (even white space), and execution runtimes could be different due to e.g. parameter sniffing. You'll need to show the exact query text, procedure definition and the two plans to give anything more exact.
    – Aaron Bertrand
    Jun 2, 2014 at 16:24
  • @AaronBertrand - it's frustrating as heck but I can't post the actual query or even the plan due to company policy. I understand that it limits the quality of help I can count on, but I have to live with that.
    – Metaphor
    Jun 2, 2014 at 16:49
  • 1
    If they are date params it's almost certainly parameter sniffing.
    – JNK
    Jun 2, 2014 at 17:27
  • Another thing to check for is your Arithabort settings. We had a similar, nasty performance problem. We found out that Arithabort was set to OFF for that procedure and after we switched it to ON everything worked as expected. msdn.microsoft.com/en-us/library/ms190306(v=sql.110).aspx Additional note, you can set your SSMS Arithabort settings under Tools > Options > Query Execution > SQL Server > Advanced
    – Alf47
    Jun 4, 2014 at 19:15

1 Answer 1

Reset to default
2

This might be a case of parameter sniffing.

You could try simply defining two local date variables at the top of your stored procedure, and assign them the value of the two input date parameters. Then replace the usage of the input parameters in the stored procedure code with those new local variables.

This has usually done the trick for me in the past, when dealing with parameter sniffing.

Improve this answer
5
  • If the procedure runs for more than a couple of seconds it may be easier just to put a WITH RECOMPILE in the definition so it always generates a new plan.
    – JNK
    Jun 2, 2014 at 17:30
  • 1
    That is true, but you might want to be a little more careful with the RECOMPILE option, since constantly recompiling a procedure might give you a performance penalty when used with a large or a frequently used procedure.
    – druzin
    Jun 2, 2014 at 17:38
  • 2
    I'm not sure how using the internal-parameter workaround would be any different. It will force a recompile which is why it works.
    – JNK
    Jun 2, 2014 at 17:44
  • 1
    Or you could use the OPTIMIZE FOR UNKNOWN option introduced with SQL Server 2008. This examines the statistical data, but not the parameter values, to optimize.
    – RLF
    Jun 2, 2014 at 17:56
  • 2
    @druzin well, you would use RECOMPILE at the statement level, not the procedure level.
    – Aaron Bertrand
    Jun 2, 2014 at 19:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.