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 Friday, April 2, 2010 9:41 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 7:40 AM
Points: 58, Visits: 396
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?
Post #895762
Posted Friday, April 2, 2010 9:53 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:21 PM
Points: 11,192, Visits: 11,090
"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




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #895782
Posted Monday, April 5, 2010 9:25 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 7:40 AM
Points: 58, Visits: 396
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
Post #896780
Posted Monday, April 5, 2010 9:34 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:21 PM
Points: 11,192, Visits: 11,090
Thanks for the feedback, Matt.



Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #896787
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse