Post reply

Please explain the difference between "Parameter Sniffing and With Recompile"

  • November 6, 2012 at 4:00 am

    #284526

    [font="Verdana"][/font]

  • November 6, 2012 at 4:23 am

    #1556390

    With recompile, you are telling SQL Server to throw away the existing query plan and build another one but only for this once.

    With parameter sniffing, the optimizer pretends that the values passed to the stored procedure (the first time?) are fixed and uses those for the query plan stored in the cache. This may be useful is the values passed are appropriate, but if the first time you run the SP, and the values passed are 'extra-ordinary' then you will be storing an inefficient query plan and your performance will suffer.

    See http://blogs.technet.com/b/mdegre/archive/2012/03/19/what-is-parameter-sniffing.aspx for more on parameter sniffing.

    HTH,

    B

  • November 6, 2012 at 7:40 am

    #1556457

    There's a very good article on Gail Shaw's blog: http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/

    Read that and see if it answers your questions. It's quite helpful to most people with questions about these things.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply