“Tony Rogerson in particular has a very interesting post on the procedure cache and how you might improve performance with application design,”
The SQL Server procedure cache is one of the least known part of SQL Server, in 2000 it can grow to 800megs of ram and unless you use the system stored procedure sp_procoption it can remove your stored procedure from the cache based on lack of use. The reason is the least used algorithm that is used to manage it, if a stored procedure is not used for some time it removes it to make room for others that may need the space.