ohack (9/19/2014)
Hi,Another SQL Server 2000 mystery, perhaps related to my last post (http://www.sqlservercentral.com/Forums/Topic1611514-5-1.aspx).
A stored procedure (the same one) disappears from syscacheobjects - usually before the usecounts counter reaches 350 (which takes about a minute). It then reappears in syscacheobjects anything between 1 and 15 seconds later. In the meantime we have compile locks listed in sysprocesses as other connections queue to get access to the stored procedure. It's kind of similar to what is described in http://support.microsoft.com/kb/263889 but with one major difference. No matter what I do Profiler never captures any SP:Recompile for the procedure, neither is there any SP:ExecContextHit - just a couple of Cache Misses and, for one lucky spid, a single Cache Insert. Can anyone explain how the procedure gets back into syscacheobjects without being recompiled (when I trace I capture loads of other recompiles). The Proc cache on our server is under pressure and so might explain why it disappears.
Big special thanks to anyone who even reads all of this let alone replies. I realise I'm mostly here with the tumbleweeds.
Are you monitoring the SP:CacheRemove event in your trace? It fires when a plan ages out of the cache due to memory pressure or is removed due to a cache flush (DBCC FREEPROCCACHE). The SP:CacheMiss and SP:CacheInsert indicate that the plan is indeed absent from the cache. Unless you have some process that is flushing the cache periodically, I'd say your plan is aging out of the cache due to memory pressure.
Jason Wolfkill