HWM & Active counters in monProcedureCacheMemory|ModuleUsage

[Former Member]

Since 15.0.x ASE comes with handy PC MDAs - monProcedureCacheMemoryUsage and monProcedureCacheModuleUsage.  Both are "essential" in sizing the PC correctly - and analyzing PC behavior for your application & ASE.  There counters are very nice but... over time they become user-un-friendly.

Both counters are not absolute point-in-time sample values but rather grow over time.  But if they grow - what do they show?

• Active (for ModuleUsage) must show "Number of memory pages (2K) currently allocated to this module".
• HWM show "The maximum number of memory pages allocated since the server was started."

There may be further broken down into sub-allocators.

You'd expect Active to indicate the sample value from the point-of-time of inspection and HWM to indicate the number of memory pages since the startup (i.e. relative to the DaysRunning from the monState).  Yet, both behave in the same cumulative manner and both yield useless values if divided into daily portions and compared to say monitorconfig.   The same applies to the reuse values packed into these tables.

Anyone tried to make sense of these values?   They are essential in configuring PC on the one hand but accumulate over time on the other (and exceed PC size in hundreds of % + are marked as indicator&1).   What do these in fact display?  How do they relate to monitorconfig?  When you restart ASE (or play with these on your private ASE) the numbers seem to make sense.  When you analyze them on the real production environment - they only mislead (as does the sysmon bit in the Indicator column related to them).  

Any insights?

Thanks,

Andrew

ps.  PERFORMANCE SCALABILITY ENHANCEMENTS IN SAP ADAPTIVE SERVER® ENTERPRISE has a neat section on PC sizing which, too, rely on monProcedureCacheModuleUsage.  However, the values are taken as simple aggregates - without any respect to their cumulative nature.  Was ASE bounced to take correct measurement?  How reliable are the HWM/Reuse numbers in that paper?

[simon_ogden]

Hi Andrew,

Those two tables can provide you with some useful data but as you've already noticed they can mislead (sometimes gratuitously!) These have been observerations/findings over time:

• If using a version of ASE prior to 15.7 SP130, forget trying to correlate the total used sizes with anything reported by sp_monitorconfig. You will regularly see massively higher sums for certain modules. This was due to a bug covered under CR #747948, I believe it related to duplicate allocator entries when memory was moved from one module to another, In short, this bug over time will lead to monProcedureCacheMemoryUsage reporting enormous 'Active' figures for certain modules (usually 5 and 10), way, way in excess of the actual configured 'procedure cache size'.
• This bug is fixed in SP130, however, there is still a distinct difference between the data in monProcedureCacheModuleUsage and monProcedureCacheMemoryUsage. So from SP130 the total 'Active' figure for each table should be roughly the same but when summing it per module you'll again see discrepancies and this is due to the proc memory manager and memory transfers.
  • Memory is moved from one module to another (this happens with reasonable regularity particularly from 5 to 10, likely lava execution contexts).
  • When the memory is moved the ModuleID will change but the AllocatorID does not, this leads to discrepancy over time. This isn't really a monitoring issue but more a proc mgr issue as all monitoring is doing is reading the detail from the control structure.
  • monProcedureCacheModuleUsage is 'current' usage by module.
  • monProcedureCacheMemoryUsage does report thecurrent usage (as a total) but broken down by the *original* allocator and as each allocator only has one parent module, the data is not representative of the current allocations by module.
• The HWM figures are of course useful but you can't start summing these else you'll end up with useless data, you need to treate each HWM figure as standalone and use it as a proportion of the total procedure cache used. I.e. You could say that ModuleID 5 has used a maximum of 20% of the configured proc cache for example.
• For comparison to sp_monitorconfig, this is reasonably reliable but there are a few points you need to be wary of.
  • Don't trust the total percentage used as reported by sp_monitorconfig. The monitor counter it is reporting on brings back the total number of pages used in the full procedure cache memory pool, this includes the cfg parameter 'statement cache size'.
    • When it calculates the percentage however it will only use the 'procedure cache size' configuration parameter.
    • As such, (not a realistic example) say you had a 50MB proc cache and 60MB statement cache and you fill 50MB of the statement cache. This will report 100% used by sp_monitorconfig and any further additions to the statement cache will end up with an active figure going above the size of cfg parameter 'procedure cache size'. You see no reuse for statement cache removals (despite each removal removing the entry from the 'true' statement cache and the associated lwp). You WILL see reuse if it is the statement cache lwp that has been directly flushed from the proc cache (rather than the the lwp being removed because the statement has been removed)
    • The reuse_cnt in sp_monitorconfig should match the sum of the NumReuseCaused in monProcedureCacheMemoryUsage. This figure represents the number of regular procbufs forcibly pushed out of the proc cache. Each procbuf might use of X number of proc_hdrs, this reuse_cnt is not a measure of pages it is a measure of how many trees/plans have been forced out.
  • monProcedureCacheModuleUsage.NumPagesReused is the actual number of 2KB pages that have been reused for a given module.
  • If you are using dbcc proc_cache ('free_unused') this will increase the reuse count by the total number of lwp and procs in the proc cache memory pool but it won't increment the counter in monProcedureCacheModuleUsage, so immediately you'll see discrepancies.

So my overall take on this is:

• If running earlier than 15.7 SP130 don't use those two tables, they'll just confuse.
• Only use monProcedureCacheModuleUsage to break down by module, the other table is just a smidge too granular and the historical nature of it will confuse.

• Even if breaking it down by module it rarely gives you an insight into the behaviour of the proc cache, it's almost always the usual suspects using the bulk of it up.

• You can get some reuse even with a low percentage used, but your bad reuse should be avoided if you keep the proc cache at < 70-80%.
  • Let's not get into the engine local caches and TF 753, 758 and all that jazz, that's another discussion 🙂
• If running with a large statement cache be wary of the sp_monitorconfig problem and work out the actual percentage used by summing the Active from monProcedureCacheModuleUsage and dividing it by the 'procedure cache size' + 'statement cache size'.
  • You can also run a modified sp_monitorconfig which gives better data for procedure cache.

Hope this helps!

Cheers,

Simon

[Former Member]

Simon,

Thank you your detailed reply (sorry for not being able to reply earlier - busy with upgrade/downgrade project)...

What I understand from you is that sp_monitorconfig does provide you with the accurate measurement of PC utilization.  The two hefty MDAs mislead - at least until SP 130. 

Can I venture a few more questions?

I won't go into TF 753/7/8 - relevant but will skip for now.  I do want to check about ELC.

(1) PC is, to simplify, ELC + GC (global cache) + SC (statement cache).  ELC is, to simplify, the dynamically growing part of PC where engines operate (run their tasks).  I am confused, though, about the PC global cache.  ASE operates in tasks, ergo all its activity should take place in ELC. What takes place in GC?  Is it a sort of "repository" to grab free memory allocations for ELC/SC?

(2) ASE has two famous dbcc commands for PC:  flush_elc and free_unused.  Where each of these operate? 

(3) ASE starts up wit ELC preconfigured (may be changed in late versions).  sp_monitorconfig for the freshly started ASE will display 0% active.   Does it mean that first x% active reported by monitorconfig relate to engines operating in pure ELC space (no grab from GC), while from the moment the x% is crossed, GC->ELC memory movement begins (hence more spinlock contention on PC)?  At what point ASE requests engines to cleanup its ELC (do they shrink back to the preconfigured value at all)?

Andrew

[simon_ogden]

I'd probably suggest to read this document as it has some good detail on the ELC. Pay special attention to the parts about the ELC and its free nature and also the part about all ELCs being flushed once it starts to run out of resources (this last bit is very expensive directly and indirectly)

  http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/208c05a1-d739-3210-f0ae-f25f343c0...

The global part of the procedure cache will indeed hold the 2KB proc cache pages that are actively in-use by a given plan (PROCBUF), It will also hold additional free buffers and no doubt there will be some modules that perhaps do direct access to the global pool. I can't give you specifics as A) I have no way of actually checking and B) I'm not sure what SAP are really wanting to share about the very granular details in the area. You can't think of these memory pools as separate beasts, there are complexities and all kinds of synchronisations taking place internal to the memory management. For example, your 'true' sql statement cache is a defined fragment memory pool that uses pages pulled from the proc cache header pool. It doesn't actually have a explicit size but its maximum size is limited at a higher level.

The procbuf pool which sits at the server level contains trees and plans but the procbuf itself is a fairly small structure and hanging off those are the actual 2KB pages that form the plan (each procbuf may indirectly use X amount of 2KB pages).

So as a given procedure (or piece of ad-hoc sql) is optimized it'll grab the space it needs from the ELC. As the execution plan is finalized those buffers will get updated and marked as in-use from the perspective of the global pool. What I don't know for certain is whether at this point those previously free buffers in the ELC will be replaced with new free buffers from the global pool.

As a procedure is executed it may (will?) then need lava execution contexts which are also taken from the ELC.

A slight aside but 'streamlined dynamic sql' included a feature which meant these lava exec contexts were grabbed when the statement was compiled rather than when executed, this was partly to help reduce the hit on procedure cache whilst stuff is merely executing. This feature also allowed the dynamic SQL cache to in essence move to the regular statement cache. Prior to this, your dynamic sql was reusable for the lifetime of a connection but once disconnected it was lost. There have been some miscommunications about this feature in terms of it being designed to share dynamic sql statements across user ids, but this never was the case, it is there to make the dynamic sql cache persistant.

(2) ASE has two famous dbcc commands for PC:  flush_elc and free_unused.  Where each of these operate? 

flush_elc, this does what it says on the tin, it flushes the ELC for each engine. Exactly how and what bearing this has on the next request from a given engine is a question someone else would need to answer.

free_unused, this operates on a procbuf level, it will remove all procbufs (stored procedure plans/trees, lwps, default, views, triggers, partition conditions, basically all compiled objects)  that are not in use and their associated 2KB pages (I think by definition all these pages must be in in-use in the global pool?). It does not touch the sql text component of the statement cache. Any ad-hoc SQL that subsequently finds a match will compile a new lwp. dbcc purgesqlcache removes the sql text part of the statement cache along with all statement cache lwps.

FWIW, on a slightly separate note all our 15.7 servers run with TF 753 and I wouldn't run them any other way.

The fragmentation of the proc cache and not being able to find larger chunks in the global list over time is only lessened by having large allocations in the ELC (758), it delays it significantly, but it can (and demostrably will) still come back in some circumstances. With 2KB allocations only you can achieve workload profile consistency over time.

In my opinion (and this is only my opinion) the large allocation feature back in 15.0.1 (or was it 15.0.2?) was a little rushed and reactionary based on the 15 optimizer and execution engine at the time. If you can keep compilation to a minimum and taking into account the enhancements that have been added since to partially reduce the memory footprints and to shorten the compilation process, 2KB allocations are OK.

[Former Member]

Can I venture another question?

I have noticed that the distribution of memory among different PC modules in my environment usually has the following top modules;

Proc Objects

Sort

Execution

Optimizer

Backup

Proc Objects and Sorts are more or less straightforward.

What type of usage is the other three:  statistics for the query, e.g., does this go into Optimizer or Execution?  Backup module?  Some of my servers have a huge percentage of memory in that area.  What is it used for?

Thank you,

Andrew

[former_member182259]

Optimizer will contain:

     1) Stats used for your query compilation (note that there is no shared global stats cache - so if 10 people are optimizing the same query - you get 10x the proc cache usage)

     2) Work plans - all those possible plans (not discarded due to being too expensive) generated during optimization.   Depends on query complexity - each plan might be only 10KB - but could easily be ~150KB (probably a good average) or much larger (for DSS queries)

Execution

     1) The final query execution plan - figure 150KB on average

     2) Execution meta data

Both have other things as well....big thing is the stats & plans.

Backup Server - not sure myself - suspect it needs to track which pages it has read from shared memory - and if so, suspect it uses a hash table - which if you are familiar with cache overhead, likely is fairly big.

[Former Member]

Does it mean that backup (either full or tran) may potentially consume fairly large amount of proc cache memory (with the bunch of servers I inspect it may range anywhere between 10 to 90 % of it)? 

The one server that has had this huge percentage of PC consumed by backups also reports 20+% proc_cache spinlock contention - from time to time.  Is this related to the pressure on the PC memory caused by concurrent backup (actually, the only concurrent dump at that time was the tran log dump which takes place every 5 minutes)? 

Which brings me back to inability to use this MDA conclusively - at least in 15.0.3.  The counters in it increase all the time - so if at some point of time some activity pushed one of the modules into using 90% out of all available PC memory it will stay there until the server is bounced (for both Active and HWM counters - I would expect the HWM value being pushed up while the Active value being brought back to real non-cumulative value).  All the subsequent attempts to see which module is most active and potentially has greater influence on the overall behavior of ASE at the time of inspection will be unsuccessful.  Measuring delta values will also make no sense.    Is there no way to reset the counters without bouncing the server?  How can I make sense of the values the table lists?