September 19, 2014 at 9:04 am
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.
September 19, 2014 at 10:42 am
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
September 22, 2014 at 5:23 am
Thanks wolfkillj that was a good suggestion which would have helped track the problem down. As it is the penny dropped about 40 minutes ago when we found a job that was running every minute and was calling DBCC FREEPROCCACHE.
Amazing, mystery solved and now the proc cache is growing normally again.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply