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, 2:00 am, Juaninho <Juani...@.discussions.microsoft.com>
wrote:
> 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
If you've not read the BOL yet, here is what it says. After attending
Kalen Delaney's presentation last week I was reading about it more. I
have not seen any way or technique to keep the stored proc in the
cache. What I understand is even the execution plan is eligible for
deallocation sql server does not deallocate it till the resources is
needed. But make sure you have a lot of memory and that sql server
can use all of it.
After an execution plan is generated, it stays in the procedure cache.
SQL Server 2000 ages old, unused plans out of the cache only when
space is needed. Each query plan and execution context has an
associated cost factor that indicates how expensive the structure is
to compile. These data structures also have an age field. Each time
the object is referenced by a connection, the age field is incremented
by the compilation cost factor. For example, if a query plan has a
cost factor of 8 and is referenced twice, its age becomes 16. The
lazywriter process periodically scans the list of objects in the
procedure cache. The lazywriter decrements the age field of each
object by 1 on each scan. The age of our sample query plan is
decremented to 0 after 16 scans of the procedure cache, unless another
user references the plan. The lazywriter process deallocates an object
if these conditions are met:
The memory manager requires memory and all available memory is
currently in use.
The age field for the object is 0.
The object is not currently referenced by a connection.
Because the age field is incremented each time an object is
referenced, frequently referenced objects do not have their age fields
decremented to 0 and are not aged from the cache. Objects infrequently
referenced are soon eligible for deallocation, but are not actually
deallocated unless memory is required for other objects.
Good day,
Bulent|||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 i
s
> 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 ke
ep
> 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