Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SSMS 2008 Stored Proc WITH RECOMPILE Expand / Collapse
Author
Message
Posted Thursday, April 1, 2010 8:56 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 21, 2014 6:29 PM
Points: 58, 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
Post #894880
Posted Thursday, April 1, 2010 10:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Saturday, August 30, 2014 4:20 PM
Points: 11,194, Visits: 11,142
Almost certainly 'parameter sniffing'.

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

Statistics Used By The Optimizer
Compilation




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #895011
Posted Thursday, April 1, 2010 12:55 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 9, 2012 2:48 PM
Points: 493, Visits: 636
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.
Post #895212
Posted Thursday, April 1, 2010 1:06 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, July 7, 2014 1:15 PM
Points: 750, Visits: 3,157
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.
Post #895223
Posted Friday, April 2, 2010 12:39 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Saturday, August 30, 2014 4:20 PM
Points: 11,194, Visits: 11,142
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #895470
Posted Friday, April 2, 2010 12:41 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Saturday, August 30, 2014 4:20 PM
Points: 11,194, Visits: 11,142
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #895471
Posted Friday, April 2, 2010 8:28 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 9, 2012 2:48 PM
Points: 493, Visits: 636
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.
Post #895662
Posted Friday, April 2, 2010 8:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Saturday, August 30, 2014 4:20 PM
Points: 11,194, Visits: 11,142
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #895692
Posted Friday, April 2, 2010 9:19 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 9, 2012 2:48 PM
Points: 493, Visits: 636


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!
Post #895720
Posted Friday, April 2, 2010 9:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Saturday, August 30, 2014 4:20 PM
Points: 11,194, Visits: 11,142
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #895751
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse