By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,856 Members | 1,845 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,856 IT Pros & Developers. It's quick & easy.

When is procedure cache flushed or cleared?

P: n/a
Using SQL Server 2000. When does SQL flush or clear the procedure
cache? I am dynamically creating and dropping stored procedures (SP).
Does SQL clear the cache for the SP that has been dropped? If not,
when the SP is recreated (with the same name), does SQL use the
execution plan from cache?

Thank you in advance.

Jack

Jul 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
There are many good articles on Internet...

Here is an OK one :

http://www.databasejournal.com/featu...le.php/3067071

MarcM
<ja******@hotmail.com> wrote in message
news:11*********************@l41g2000cwc.googlegro ups.com...
Using SQL Server 2000. When does SQL flush or clear the procedure
cache? I am dynamically creating and dropping stored procedures (SP).
Does SQL clear the cache for the SP that has been dropped? If not,
when the SP is recreated (with the same name), does SQL use the
execution plan from cache?

Thank you in advance.

Jack

Jul 23 '05 #2

P: n/a
(ja******@hotmail.com) writes:
Using SQL Server 2000. When does SQL flush or clear the procedure
cache? I am dynamically creating and dropping stored procedures (SP).
Does SQL clear the cache for the SP that has been dropped? If not,
when the SP is recreated (with the same name), does SQL use the
execution plan from cache?


When the procedure is dropped, the plan is removed from the cache. This
also happens if you issue an ALTER PROCEDURE. It will also fall out of
the cache if a table it refers to is dropped or altered. You can also
use sp_recompile to force recompilation.

A procedure can also fall out of the procedure cache if it has not
been referenced for a while.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.