cancel
Showing results for 
Search instead for 
Did you mean: 

Procedure cache and temp objects not clearing down

Former Member
0 Kudos

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.


Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

I found the command

     dbcc proc_cache(free_unused)

and all the space used by tempdb objects dropped...

DBName                         MemMB       c

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

tempdb                                 135        4740


and we were left with


DBName                         MemMB       c

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

tempdb                                   5          50


Does this imply unused objects aren't being cleared up ?

We don't close connection from our services very often - would this cause a problem.



dawn_kim
Contributor
0 Kudos

Hi Mike,

I recently worked a case where batch parameters from an application are not getting cleared out of procedure cache. So the lwp's are not getting cleaned up this is ASE CR 779389.

So cleaning up your procedure cache might be what you do need to do.

dbcc proc_cache(free_unused) will free up **all** memory used by currently unused procedure plans. It will free up ** all ** the unused plans regardless of the size.


However please note that after running dbcc proc_cache(free_unused) , there may be a performance hit when users start running their procedures again requiring their plans to be compiled again and stored in the procedure cache. dbcc proc_cache(free_unused) can be useful if there is a need to clear unused plans that may never be needed again. However if ASE is regularly hitting Error 701, that indicates that the procedure cache needs to be increased to a level that will be able to contain all the plans executed on this server.

Thanks,
Dawn Kim

Former Member
0 Kudos

Thanks - we've had this a couple of times and not been able to understand why it happens.

I'll be continuing to monitor it and see which objects are using the most space.

Is CR 779389 being worked on ? Is there an expected release for it ?

I'm not aiming to run

          dbcc proc_cache(free_unused)

in our production environment. I really don't want to reload all that data again.


is there a way (a dbcc command) to remove just the unused temporary objects ?

Former Member
0 Kudos

The procedure cache filled up again.

Luckily I was monitoring it at the time...

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

      from master..monCachedProcedures

      group by DBName, ObjectName

      having sum(MemUsageKB) / 1024 > 10

      order by 3

and it was only showing less than 2Gb usage

DBName                         MemMB      count

tempdb                                 145         4862

DB3                                     178           597

DB2                                     334         1403

DB1                                      846        1803

I was monitoring it in a loop and the total space in monCachedProcedures started to decrease.

until I got the message

    Msg 701, Level 17, State 3

    Server 'UAT_SQL', Line 1

    There is not enough procedure cache to run this procedure, trigger, or SQL batch.

    Retry later, or ask     your SA to reconfigure ASE with more procedure cache.

I ran

    dbcc procbuf

and this only shows 257 objects in the procedure cache.

I ran

    dbcc proc_cache(free_unused)

and this showed

    Procedures in cache before free unused:  258

    Procedures in cache after free unused:   212

We've had this a few times on 2 or our non-production servers but never on production.

All machines are the same expect Production runs in "process mode" and non-production runs in "thread mode". (We were re-testing thread mode as we had to turn it off as it was unstable).

It seems from this that monCachedProcedures doesn't show everything thats in the the procedure cache ?


Running select * from master..monProcedureCacheModuleUsage

shows

      InstanceID  ModuleID  Active      HWM         NumPagesReused     ModuleName

              0        5               831          6308111                  0                 Execution

              0        10             282868     1097270        1805401                 Procedural Objects

              0        1              14             236915                   0                  Parser


The HWM of the "Exectuion" module shows 6m pages - which is probably what fills up the procedure cache.

What populates the this ?

Will log this as an incident.

Former Member
0 Kudos

We've done some more analysis and have found the space in the procedure cache is used up by

MEMC_SCOMPILE_1.

                   AllocID ModuleID Active   HWM      ChunkHWM NumReuseCaused AllocatorName

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

2015/08/21 4:15PM     110        1   131052   281275        1              0 MEMC_PARSLEX_1

2015/08/21 4:15PM     114        5  5509024  6756243        8           2347 MEMC_SCOMPILE_1

It seems that when ASE is heavily loaded (100 processes) that MEMC_SCOMPILE_1 space grows and then the whole machine runs very slow. It feels as though stored procs aren't running but dynamic SQL is running - sp_sysmon "00:00:05" never completed.

I'm not sure what MEMC_SCOMPILE_1 is and why it grows to 10Gb - thats a lot of memory per process.

We only see this on thread based servers (not saying this is the problem but we're going to turn it back to process mode and re-test).