Skip to Content
Former Member
Aug 19, 2015 at 11:12 AM

Procedure cache and temp objects not clearing down


We ran out of procedure cache - we have this configured at 6,000,000

Unfortunately it was rebooted before I could check the master..monCachedProcedures table.

I'm monitoting it now and see the tempdb objects slowly increasing in size..

This shows...

select DBName, sum(MemUsageKB) / 1024 MemMB, count(*) c

from master..monCachedProcedures

group by DBName

having sum(MemUsageKB) / 1024 > 1

order by 2

DBName MemMB c

------------------------------ ----------- -----------

tempdb 134 4703

The mem usage doesn't seem to drop.

Is there someway of seeing which spid creates them.

This sugests the spid is held in the ObjectName *ss1376190261_2136957293ss*

from the 4th char - but this doesn't match master..sysprocesses.

Objects exist with no spid.

Its also says

The LWPs won't go away until the parent process either

a) deallocates the LWP or

b) logs out (at which point all LWPs are deallocated ... just like what happens with any #temp tables owned by said process).

We use connection pools - so what would cause the deallocation of the LWP ?

Is there someway to manually clear down the procedure cache ?

I'll log this as an incident.