SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSMS 2008 Stored Proc WITH RECOMPILE


SSMS 2008 Stored Proc WITH RECOMPILE

Author
Message
MattW2010
MattW2010
Old Hand
Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)

Group: General Forum Members
Points: 322 Visits: 398
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
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35860 Visits: 11361
Almost certainly 'parameter sniffing'.

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

Statistics Used By The Optimizer
Compilation



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Toby White
Toby White
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1567 Visits: 639
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.
matt stockham
matt stockham
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2644 Visits: 3178
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.
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35860 Visits: 11361
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?



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35860 Visits: 11361
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.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Toby White
Toby White
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1567 Visits: 639
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.
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35860 Visits: 11361
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?



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Toby White
Toby White
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1567 Visits: 639

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!
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35860 Visits: 11361
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... :-)



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search