WITH RECOMPILE, parameter sniffing - HELP!!

hi, forum:<br /><br /> I really am getting stuck here. Not sure what my issue is, but it <i>seems</i> like it might be a "parameter sniffing" issue with my stored proc.<br /> Its a simple sp, that passes one parameter to a select statement.<br /> First, let it be known, it take 1-2 seconds in Query Analyzer, but depending on the options set, it takes 1-25+ secs.<br /> Anytime that I create the proc with "WITH RECOMPILE" it works, all the time consistently thru the web app calling this proc in 1-5 secs. Take off the Recompile option, it crawls.<br /> So, sounds like a bad plan, caching issue or parm sniffing, I guess.<br /> Now, this only occurs in our prod environment, not dev/test.<br /> I tried to set a default parm (ie @Param1 int=1) but doesn't seem to help. With or w/o it. Also, just for the record, I tried to reboot the server, rebuild indexes, update stats, to no avail.<br /> Please provide some worthy guidance. Thnx in Advance![<img src='/community/emoticons/emotion-1.gif' alt='' />]

If parameter sniffing is the actual problem, then usually adding a default value for the parameter helps. You can also try declaring a local variable and copying the parameter value to the local variable and using it in the query.

Roji. P. Thomas
http://toponewithties.blogspot.com

Indeed, that appears to be the problem. I did your suggestion (2nd one) and it works w/o Recompile. I tried to add a default value, that didn't work.
The questions that I must answer for the users, a little vague is:

1) Why it ran in QA regardless of Recompile option under 5 secs
2) Why this does not occur in the same environment as our dev/test (what could be different?)
Any ideas/explanations would be appreciated Thx again!

Regards

quote:Originally posted by Roji. P. Thomas

If parameter sniffing is the actual problem, then usually adding a default value for the parameter helps. You can also try declaring a local variable and copying the parameter value to the local variable and using it in the query.

Roji. P. Thomas
http://toponewithties.blogspot.com

is there a difference in the data in the prod db and the dev system?

look at the execution plan in the prod system

quote:Originally posted by sql_jr

Indeed, that appears to be the problem. I did your suggestion (2nd one) and it works w/o Recompile. I tried to add a default value, that didn't work.
The questions that I must answer for the users, a little vague is:

1) Why it ran in QA regardless of Recompile option under 5 secs
2) Why this does not occur in the same environment as our dev/test (what could be different?)
Any ideas/explanations would be appreciated Thx again!

You only have to explain what parameter sniffing is. Note that it is not a bug, but a feature!.

Also, try updating the statistics and see whether that makes any difference.

Roji. P. Thomas
http://toponewithties.blogspot.com