Skip to Content

15.7 SP110 -> CPU, proccache and memory consumption increase after IMDB

Hello,

We finally switched our tempdbs databases to IMDB databases last weekend. We experienced the day after a blocking of dataserver , that we solved by emptying proccache. We already had only once this behaviour, and I asked prod DBAS for opening support ticket to perhaps activate TF 753 and/or 757.

Nevertheless , I can see that memory usage increase , and for some batch processes Proccache usage has drastically increased, for globally the same amount of work. Moreover exec time has not decreased , but increased by about 20%.

On the opposite, response time for screen (which also calls stored procs , but lighter) has decreased as expected.

Has someone experienced this kind of behaviour ?

Regards

Marc

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • Posted on Nov 26, 2014 at 03:29 PM

    Hi ,

    Some news since yesterday :

    -- Our cpu overconsumption is linked to a well-known Sybase 15.x bug when Engine Local Cache is filled. We applied TF 757 , but we got same issue and finally we have scheduled every hour a job to free unused procs in cache. Issue is still pending and it seems Sybase does plan nothing for this issue.

    -- Execution time degradation seems linked , not to IMDB as we thought, but to dml_logging set to minimal for tempdbs. In fact, when analyzing figures from MDA tables, same workload , either allocates 80 temp tables and does 200K transactions and commits , either , with dml_logging = minimal , allocates 8000 tempdb tables and does 4 times number of commits and transactions.

    Feeling that is increased with increase of nesting.

    Sounds weird, configuring minus logging increases commits and transactions.

    Is someone aware of something to solve procedure cache issue , or at lesat be sure to avoid it ?

    Regards

    Marc

    Add a comment
    10|10000 characters needed characters exceeded

    • Again, Thanks a lot Jeff, for your time.

      For temp tables creation, you were right, I finally found a "new" hashtable creation within a stored proc which is called several million times a day. So , IMDB and RDDB fetaures , are currently perfectly working for this application.

      For another one, a very heavy usage of tempdb led us to huge figures in spinlocks for IMDB caches we have created. (20% contention) . I will add cache partitions and monitor.

      For high CPU usages , I got figures from sysmon but I see nothing high when pikes occured.

      Sampling Started at : Nov 24 2014 1:55PM

      Sampling Ended at : Nov 24 2014 2:12PM

      Sample Interval : 990 s

      Nb transactions : 17198

      Procedure Cache Management per sec per xact count % of total

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

      Procedure Requests 11.1 0.6 11025 n/a Procedure Reads from Disk 0.2 0.0 220 2.0 % Procedure Writes to Disk 0.2 0.0 226 2.0 % Procedure Removals 2.2 0.1 2132 n/a Procedure Recompilations 0.1 0.0 118 n/a

      Recompilations Requests:

      Execution Phase 0.1 0.0 64 54.2 % Compilation Phase 0.1 0.0 54 45.8 % Execute Cursor Execution 0.0 0.0 0 0.0 % Redefinition Phase 0.0 0.0 0 0.0 %

      Recompilation Reasons:

      Table Missing 0.1 0.0 104 n/a Temporary Table Missing 0.1 0.0 104 n/a Schema Change 0.0 0.0 14 n/a Index Change 0.0 0.0 0 n/a Isolation Level Change 0.0 0.0 0 n/a Permissions Change 0.0 0.0 0 n/a Cursor Permissions Change 0.0 0.0 0 n/a

      SQL Statement Cache:

      Statements Cached 0.0 0.0 16 n/a Statements Found in Cache 1.2 0.1 1188 n/a Statements Not Found 0.0 0.0 16 n/a Statements Dropped 0.0 0.0 35 n/a Statements Restored 0.0 0.0 6 n/a Statements Not Cached 0.0 0.0 0 n/a

      BTW , we experienced no other issue since we free ProcCache regularly.

      Again , thanks a lot for your help.

      Regards

      Marc

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.