Neither DBCC FREEPROCCACHE nor DBCC FREESYSTEMCACHE('SQL Plans') do anything to free CACHESTORE_SQLCP memory

CACHESTORE_SQLCP Sql Plans takes up > 38 GB after a few days.

We are already running with "optimize for ad hoc workloads" option on. (Entity Framework and custom reporting creates a lot of ad hocs!)


SQL Server 2016 SE 3.00.2164.0.v1 on AWS RDS with multi-AZ mirroring

When I run:

DBCC FREESYSTEMCACHE('SQL Plans');

or

DBCC FREEPROCCACHE

or

DBCC FREESYSTEMCACHE ('SQL Plans') WITH MARK_IN_USE_FOR_REMOVAL

or

DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL;

It doesn't seem to clear it:

SELECT TOP 1 type, name, pages_kb FROM sys.dm_os_memory_clerks ORDER BY pages_kb  desc

type                name        pages_kb
CACHESTORE_SQLCP    SQL Plans   38321048

I was running with Query Store enabled, but I disabled it to see if that was interfering with anything, it didn't seem to help, but I left it off.

What's really weird also is

SELECT COUNT(*) FROM sys.dm_exec_cached_plans

is 1-3 or so (it seems to show only ever show currently-running queries), even though all that memory is reserved, even before I attempted to clear anything. What am I missing?

CACHESTORE_SQLCP is taking up more than 60% of all available memory, which is a concern because there are memory waits happening occasionally. In addition, we had to kill a routine DBCC CHECKDB over the weekend that was lasting 4 hours because insufficient memory was stacking up waits (it completed instantly with no errors with PHYSICAL_ONLY on).

Is there any way to reclaim this memory (other than nightly reboots!?)?

Update from comments/answers

When I run

SELECT * FROM sys.fn_my_permissions(NULL,NULL)

I get

```
entity_name subentity_name permission_name
server CONNECT SQL
server CREATE ANY DATABASE
server ALTER ANY LOGIN
server ALTER ANY LINKED SERVER
server ALTER ANY CONNECTION
server ALTER TRACE
server VIEW ANY DATABASE
server VIEW ANY DEFINITION
server VIEW SERVER STATE
server ALTER

I upgraded to 13.00.4422.0.v1 (SQL Server 2016 SP1) and restarted.

So far, I am able to clear SQL Plan memory using DBCC FREESYSTEMCACHE('SQL Plans'); command!

Time will tell to see if it uses the memory a little wiser now, but at least now I have a way to reset it if its gets too bloated again.

StackExchange Database Administrators Q#188805, answer score: 6