2

In our production server (SQL server 2012) when I checked the memory component CACHESTORE_SQLCP is consuming the much more than the total size of the cached plans. Is there any reason for it and how we can clear it from memory.

Memory Clerk    Usage (MB)
MEMORYCLERK_SQLBUFFERPOOL   86678.00
CACHESTORE_SQLCP    6689.00
OBJECTSTORE_LOCK_MANAGER    822.00
CACHESTORE_OBJCP    703.00
MEMORYCLERK_SOSNODE 374.00

Below is the statistics from cached plans

Cached Object Type  Number of Plans Plan Cache Size (MB)    Avg Use Count
Proc     1047   715 9845
Prepared 359    32  25
View     169    25  156
Adhoc    239    23  1
Trigger  39     2   158
Check    24     0   3

Thank you in advance.

Peet

1
  • is optimize for adhoc on in sp_configure options ?
    – Kin Shah
    Commented Apr 14, 2023 at 20:11

1 Answer 1

2

For reference, the "CACHESTORE_OBJCP" clerk is for compiled plans:

This cache store is used for caching objects with compiled plans (CP): stored procedures, functions, triggers. To illustrate, after a query plan for a stored procedure is created, its plan is stored in this cache.

I assume your second query is hitting sys.dm_exec_cached_plans, in which case those numbers seem to line up: 703 MB reported by the memory clerk, 715 MB reported by the plan cache DMV (close enough for government work) for "Proc" type entries.


To the main point of your question though...

The "CACHESTORE_SQLCP" clerk has this description:

This cache store is used for caching ad hoc queries, prepared statements, and server-side cursors in plan cache.

This part seems way out of line. You have 32 + 23 = 55 MB of Adhoc and Prepared entries in the plan cache DMV. That leaves a whopping 6689 - 55 = 6634 MB of memory to account for.

It's possible, then, that the 3rd item from that list (server-side cursors, also known as API cursors) are to blame for the additional memory usage. You could check these perfmon counters to try to confirm that theory:

SELECT dopc.* 
FROM sys.dm_os_performance_counters dopc
WHERE 
    dopc.counter_name = 'Cursor memory usage';

If the problem is with API cursors, then you'll likely need application changes to correct whatever is causing them to consume so much memory (possibly by changing the cursor settings, how much data is being read into them, and / or how long they are staying open - all of that would be out of scope for this Q&A though).

It's also possible that this is a bug in software that's a decade old, and reached end of support in July of 2022. So even if it IS a bug, it's not going to get fixed.

3
  • Thank you for your clarification. Trying to figure out whether the CURSOR are the culprit for the missing memory component. I run DBCC FREESYSTEMCACHE('SQL Plans') to see if that can clear up some space but nothing happened. Is it wise to reduce the max memory to lower value and then increase to original value to release the memory lost like this?
    – Peter
    Commented Apr 17, 2023 at 7:17
  • @Peter I suppose it's viable as a temporary option. But I wonder how much time would pass before the cache is back in the same state. You also run the risk of just pushing things out of the buffer pool ("MEMORYCLERK_SQLBUFFERPOOL"), which would negatively impact performance, increase disk activity, etc. What do you hope to gain from reclaiming those ~6.5 GB of RAM? It doesn't sound worth it to me, but it's not my server 😁 Commented Apr 17, 2023 at 13:03
  • Josh, Can understand what you are suggesting and they make sense to. But this server what Im facing is number plans in the CACHESTORE_OBJCP is day by day reducing . It was around 50K after the last server restart and its came down to below thousands now. And I'm expecting performance degradation due to need of high number of recompiling and trying to avoid that situation.
    – Peter
    Commented Apr 17, 2023 at 17:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.