Proc Cache Module Distribution ASE 15.0.x vs ASE 16.0.1.x
We are running a bunch of performance tests for our forthcoming migration. We’ve got a fairly advanced stage – things look good in fact. But there is something in Proc Cache memory distribution that does not make too much sense to me. So I’ve decided to throw a net to see if I may fish out some answers here.
I’ve noticed that throughout our testing we’ve got better procedure execution rate on ASE 16 and significantly lower physical IO footprint (and less blocking as a result of that). It is all very nice indeed (red – physical IO, orange – blocking, violet SP/s – all the rest irrelevant for now):
If you check how the procedure cache fared in terms of spinlock contention – all is pink too (we’ve got no statement cache enabled at this point).
However, the distribution of memory modules (monProcedureCacheModuleUsage – total active vs per-module active) displays a strange difference in ASE 16: all of the memory is used by procedural objects and almost none by optimizer/executor. ASE 15 has this distribution more reasonable (whatever that means).
Is there a problem with data this MDA displays in ASE16 or this reflects some change in the way memory is now used? I am inclined to think of the former, however if you inspect raw values they are non zero and do change over time for both versions. For ASE 16 there is (still) a problem with HWM which is zeroed (pretty annoying since one could rely on it to size procedure cache correctly) and Optimizer memory is mostly zero in Active – but not always. monProcedureCacheMemoryUsage too has HWM emptied in ASE16 – and most of the memory chunks go to MEMC_SCOMPILE_1. I’ve got a case open on zero HWM (I hope it was not closed automatically yet) for quite some time. Perhaps you folks might have opened similar cases too to have this resolved faster…
Can anyone check/comment on this?