SQL Server 2000
once the execution plan is generated for a Stored Procedure, it stays in the procedure cache. Lazy writer only keep looking and throwing out unused plans out of the cache "only when space is needed in cache".
Below are some documented and undocumented DBCC commands available in SQL Server 2000 to deal and find more information about SQL Server cache.
To Monitor the cahce:
DBCC SQLPERF (LRUSTATS)
DBCC CACHESTATS
DBCC MEMORYSTATUS
DBCC PROCCACHE
To clean the cache:
DBCC FLUSHPROCINDB
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
You can read more from SQL 2000 Topic under "Lazy Writer", 'Freeing and Writing Buffer Pages' at: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa_8unn.asp
