SQL Server Performance

WITH RECOMPILE, parameter sniffing - HELP!!

Discussion in 'Performance Tuning for DBAs' started by sql_jr, Oct 3, 2006.

  1. sql_jr New Member

    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=':)' />]
  2. Roji. P. Thomas New Member

    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
  3. sql_jr New Member

    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

  4. joechang New Member

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

    look at the execution plan in the prod system
  5. Roji. P. Thomas New Member

    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

Share This Page