SSMS 2008 Stored Proc WITH RECOMPILE

  • Hi,

    I have sort of a general question(s) that I'm hoping someone can help with....

    I am using SSMS 2008 and I have an SP that supports a web page. When an end user selects the "All" selection on the page, the page times out. I updated the stored proc to include a "WITH RECOMPILE" option and now the page does not time out and loads appropriately.

    However (there's always a however!), the performance on the site has taken a hit. While the page does load, it takes longer than it should and it's affecting other selections on the page.

    I understand why it's now taking longer, but I don't understand why I had to add the recompile code in order for the page to load correctly. My questions are as follows:

    1. If I keep the recompile code, is there anyway to speed up performance?

    2. If I eliminate the recompile code, are there any work arounds I can implement to fix the time out of the page?

    Can anyone offer any direction and/or documentation?

    Thanks,

    Matt

  • Almost certainly 'parameter sniffing'.

    Understand the issue, and the correct ways to resolve it:

    Statistics Used By The Optimizer

    Compilation

  • One possibility is to use the option (optimize for (@var = value)) query hint. This might work if the distribution/frequency of the parameter you are using is in fact average for most of the queries that come in. Are you using any table variables in your procedure? Are you changing the value of the parameter(s) before issuing the statement that is performing poorly. It could be parameter sniffing, but recompiling the whole procedure shouldn't have a positive effect if it is. Another alternative is to just recompile the statement that is poorly performing.

  • As Paul said, you really need to understand parameter sniffing to resolve this correctly. The performance impact *may* be due to recompiling every single time this proc is executed - if that is the case then try just recompiling the statement. If that does not work then a solution would be to change the proc to call one of two children - one for the "all" cases and the other for the filtered cases. You may not need any recompile statements then.

  • Toby White (4/1/2010)


    One possibility is to use the option (optimize for (@var = value)) query hint. This might work if the distribution/frequency of the parameter you are using is in fact average for most of the queries that come in.

    OPTIMIZE FOR UNKNOWN is a better way to achieve that.

    It could be parameter sniffing, but recompiling the whole procedure shouldn't have a positive effect if it is.

    Why do you say that?

  • matt stockham (4/1/2010)


    As Paul said, you really need to understand parameter sniffing to resolve this correctly. The performance impact *may* be due to recompiling every single time this proc is executed - if that is the case then try just recompiling the statement. If that does not work then a solution would be to change the proc to call one of two children - one for the "all" cases and the other for the filtered cases. You may not need any recompile statements then.

    Absolutely agree with this. I posted those links not because I couldn't be bothered to write a specific reply, but because they are so fundamental to understanding and resolving these sorts of issues. Thanks for a great summary, Matt.

  • It could be parameter sniffing, but recompiling the whole procedure shouldn't have a positive effect if it is.

    Why do you say that?[/quote]

    Parameter sniffing is the process whereby the optimizer uses the initial values of the parameters to generate an optimal execution plan. One of the problems can occur when the plan created with the initial parameter values is reused with atypical parameter values later. This would not be occurring if the procedure is recompiled every time it is executed.

    Parameter sniffing is a "feature" that is suppose to help the optimizer, and I know that this scenario is not the only possibility. Sometimes parameter sniffing has an adverse affect because the procedure either changes the value of the parameter before the long running statement, or because the distribution of the parameter values is radically different by the time the procedure gets to the long running statement. I agree with you that it could be a problem associated with parameter sniffing, and I definitely agree with you that whether it is or not, a good understanding of parameter sniffing is essential.

  • Toby White (4/2/2010)


    Parameter sniffing is the process whereby the optimizer uses the initial values of the parameters to generate an optimal execution plan. One of the problems can occur when the plan created with the initial parameter values is reused with atypical parameter values later. This would not be occurring if the procedure is recompiled every time it is executed.

    Exactly. But I read your comment as saying that if the problem were parameter sniffing, then recompiling the procedure each time it was called would not help:

    It could be parameter sniffing, but recompiling the whole procedure shouldn't have a positive effect if it is.

    Did I misunderstand you?

  • Did I misunderstand you?

    Yes. My understanding is that he is already recompiling the procedure each time and that hasn't solved the problem.

    1. If I keep the recompile code, is there anyway to speed up performance?

    Since this didn't solve the problem it is likely not parameter sniffing. However, I went back and read what I posted, and it does not say what I meant:

    It could be parameter sniffing, but recompiling the whole procedure shouldn't have a positive effect if it is.

    I am not sure how I arrived at the above statement because what I meant to say was that if it were parameter sniffing the performance would have improved after he started recompiling the procedure every execution. I was confused why you were questioning me until I read what I wrote!

  • Toby White (4/2/2010)


    I am not sure how I arrived at the above statement because what I meant to say was that if it were parameter sniffing the performance would have improved after he started recompiling the procedure every execution. I was confused why you were questioning me until I read what I wrote!

    Heh. Ok, no worries... 🙂

  • Thank you so much to all that have replied. I can't tell you how much I appreciate it.

    What are atypical parameters (sorry, I'm kind of a junior DBA)? From what I understand, the SP wasn't ever even updated. It just stopped working one day. When we point the SP at different boxes (without the recompile), the information pulls up fine on the site. However, when we point it at our production box, it does not work unless I add the recompile code.

    Does that change the direction of the answers given?

  • "Atypical parameters" refers to the fact that SQL Server may cache a plan based on the actual parameter values passed on the first call of a procedure. If those specific parameter values are not typical (of all possible parameter values) the cached plan may not be a good one for other parameter values. Something like that, anyway 🙂

    Statistics Used By The Optimizer

    Compilation and Re-compilation

  • I just wanted to thank you guys for all your input. I added "optimize for unknown" to the sproc and the site is currently responding well.

    I believe the parameter sniffing was the main issue.

    Thanks again,

    Matt

  • Thanks for the feedback, Matt.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply