Dont want stored procs to recompile..

How can I not have my stored procedures not to recompile whenever ?
The reason being is that when they recompile, they at times choose sub
optimal plans and causes a lot of pain to our applications. Is there any way
i can prevent sprocs not to recompile at a database or server level ? Using
SQL 2000
ThanksUnless you put WITH RECOMPILE option when you create the stored
procedure. The auto-recompile only happens under two conditions:
1) The first time a stored procedure is run after Microsoft=AE SQL
Server=99 2000 is restarted.
2) It also occurs if an underlying table used by the stored procedure
changes.
And it is mainly for optimization purpose (to keep the executation plan
efficient and up to date).
I don't think you can stop the auto-recompile behaviour. But you can
influence the executation plan by adding HINTS in your stored
procedure.
Mel|||Another option is to upgrade to 2005 where you can associate a plan with it
or to use a hint that allows you to optimize for a specific value.
Andrew J. Kelly SQL MVP
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23rtKRwLYGHA.5004@.TK2MSFTNGP02.phx.gbl...
> How can I not have my stored procedures not to recompile whenever ?
> The reason being is that when they recompile, they at times choose sub
> optimal plans and causes a lot of pain to our applications. Is there any
> way i can prevent sprocs not to recompile at a database or server level ?
> Using SQL 2000
> Thanks
>|||Hi Hassan
Although you can force a procedure to recompile, you can't stop it from reco
mpiling when it thinks it needs to. All you can do is try to reduce the numb
er of situations where it wants to recompile.
There are many situations that will cause a procedure to recompile.
Take a look at these two KB articles:
How to identify the cause of recompilation in an SP:Recompile event
http://support.microsoft.com/kb/308737
Troubleshooting stored procedure recompilation
http://support.microsoft.com/kb/243586/
And look at this Whitepaper, which is directed to SQL Server 2005, but much
of it is also useful for SQL 2000:
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
http://www.microsoft.com/technet/pr...005/recomp.mspx
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Hassan" <Hassan@.hotmail.com> wrote in message news:%23rtKRwLYGHA.5004@.TK2MSFTNGP02.phx.gbl.
.
> How can I not have my stored procedures not to recompile whenever ?
>
> The reason being is that when they recompile, they at times choose sub
> optimal plans and causes a lot of pain to our applications. Is there any w
ay
> i can prevent sprocs not to recompile at a database or server level ? Usin
g
> SQL 2000
>
> Thanks
>
>