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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Please explain the difference between...

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

Rate Topic

Display Mode

Topic Options

Author

Message

riddhi.mishra1

Posted Tuesday, November 06, 2012 4:00 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, March 11, 2013 1:17 AM
Points: 1, Visits: 22

[size="5"][/size]

Post #1381479

bleroy

Posted Tuesday, November 06, 2012 4:23 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:59 PM
Points: 137, Visits: 546

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

Post #1381485

GSquared

Posted Tuesday, November 06, 2012 7:40 AM

SSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569

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

Post #1381567

« Prev Topic | Next Topic »

Permissions