How long does a query plan stay in the procedure cache?

How long does a a query plan stay in the procedure cache? Is there someway
we can reload a specific sp in SQL Server is restarted? TIADepends on the cost associated with creating the plan as well as how often
the sproc is run. Yes you can configure the sproc to run each time the SQL
Server is started by using the sp_procoption system stored procedure (see
BOL for details).
HTH
J
"ENathan" <njoysgolfing@.yahoo.com> wrote in message
news:OnKmNjuZFHA.2768@.tk2msftngp13.phx.gbl...
> How long does a a query plan stay in the procedure cache? Is there someway
> we can reload a specific sp in SQL Server is restarted? TIA
>|||Hi,
The most common reasons why a query execution plan is flushed from procedure
cache are:
1. There is not enough internal memory, or the query is not used frequently.
This causes the memory manager to assign the memory to other
procedure to be moved out from cache.You can try to run the query from time
to time.
2. Statistics are updated. When column statistics or index statistics are
updated, all query plans that use those tables will be discarded.
You can turn off auto-update statistics, and schedule UPDATE STATISTICS
yourself. After that you would need to run the query again in order to
can a compiled query plan
3. Schema changes. If the data structure is changed, indexes are added or
deleted, etc., then the related queries are marked for recompile.
Is there some way we can reload a specific sp in SQL Server is restarted?
We can put the procedure in startup . See sp_procoption system procedure in
books online.
Thanks
Hari
SQL Server MVP
"ENathan" <njoysgolfing@.yahoo.com> wrote in message
news:OnKmNjuZFHA.2768@.tk2msftngp13.phx.gbl...
> How long does a a query plan stay in the procedure cache? Is there someway
> we can reload a specific sp in SQL Server is restarted? TIA
>