Keep sp in cache

hi, I wonder if anyone knows if it is possible to specify that a specific
stored procedure always should stay in cache or for example set that i
specific sp has "high cache priority"?
Regards
JuaninhoNo. You can be sneaky about it though and have a default parameter that is
used as a flag to return immediately. Then you can set up a sql agent job
to fire every so often (minutes/hours') with that flag set, which will keep
the plan in cache. This could lead to poor query plans if the OTHER
parameters for the sproc (if any) are not typical.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Juaninho" <Juaninho@.discussions.microsoft.com> wrote in message
news:29BC33AD-8DC2-462A-89F0-B984366895B7@.microsoft.com...
> hi, I wonder if anyone knows if it is possible to specify that a specific
> stored procedure always should stay in cache or for example set that i
> specific sp has "high cache priority"?
> Regards
> Juaninho|||On Apr 24, 5:41 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> No. You can be sneaky about it though and have a default parameter that is
> used as a flag to return immediately. Then you can set up a sql agent job
> to fire every so often (minutes/hours') with that flag set, which will keep
> the plan in cache. This could lead to poor query plans if the OTHER
> parameters for the sproc (if any) are not typical.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Juaninho" <Juani...@.discussions.microsoft.com> wrote in message
> news:29BC33AD-8DC2-462A-89F0-B984366895B7@.microsoft.com...
>
> > hi, I wonder if anyone knows if it is possible to specify that a specific
> > stored procedure always should stay in cache or for example set that i
> > specific sp has "high cache priority"?
> > Regards
> > Juaninho- Hide quoted text -
> - Show quoted text -
If you are using SQL Server 2005 Look at USE PLAN, KEEPFIXED PLAN in
BOL
Regards
Amish Shah
http://shahamishm.tripod.com