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
Juaninho
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" <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...
>
>
> - 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