"Britney" wrote:

> hi all,
>       Sometimes I noticed that when I use WITH RECOMPILE inside stored
> procedure, the performance is better (more consistent), why?
>
>
>

"Alejandro Mesa" <AlejandroM***@discussions.microsoft.com> wrote in message
news:F8E6C5AD-E564-4345-BEE6-85E6CD078E02@microsoft.com...
> Because it is forcing sql server to create a new execution plan every time
> the sp is executed, no cached one will be used. When you do not use "WITH
> RECOMPILE", the cached plan can be used and if the selectivity for the
value
> of the parameters is not appropiate to the execution plan in cache, then
> performance can be hurt.
>
> If you are subscribed to sql server magazine, then you should read the
> following article written by Kalen Delaney.
>
> Reusing Query Plans
> http://www.windowsitpro.com/Article/ArticleID/46233/46233.html?Ad=1
>
> Read also about parameter sniffing.
>
> A technique for ensuring plan stability in SQL Server 2000
> http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx
>
>

>
>
> AMB
>
> "Britney" wrote:
>
> > hi all,
> >       Sometimes I noticed that when I use WITH RECOMPILE inside stored
> > procedure, the performance is better (more consistent), why?
> >
> >
> >

"Britney" wrote:

> well... I can only read half page of the article "reusing query plans"..
> is it possible that you can copy and paste full article here?
>
> "Alejandro Mesa" <AlejandroM***@discussions.microsoft.com> wrote in message
> news:F8E6C5AD-E564-4345-BEE6-85E6CD078E02@microsoft.com...
> > Because it is forcing sql server to create a new execution plan every time
> > the sp is executed, no cached one will be used. When you do not use "WITH
> > RECOMPILE", the cached plan can be used and if the selectivity for the
> value
> > of the parameters is not appropiate to the execution plan in cache, then
> > performance can be hurt.
> >
> > If you are subscribed to sql server magazine, then you should read the
> > following article written by Kalen Delaney.
> >
> > Reusing Query Plans
> > http://www.windowsitpro.com/Article/ArticleID/46233/46233.html?Ad=1
> >
> > Read also about parameter sniffing.
> >
> > A technique for ensuring plan stability in SQL Server 2000
> > http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx
> >
> >
> http://groups-beta.google.com/group/microsoft.public.sqlserver.server/msg/ad37d8aec76e2b8f?hl=en&lr=&ie=UTF-8&oe=UTF-8
> >
> >
> > AMB
> >
> > "Britney" wrote:
> >
> > > hi all,
> > >       Sometimes I noticed that when I use WITH RECOMPILE inside stored
> > > procedure, the performance is better (more consistent), why?
> > >
> > >
> > >
>
>
>