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 SERVER STATE
server      CREATE SERVER ROLE
server      ALTER ANY SERVER ROLE


Which includes the required ALTER SERVER STATE permission.

The output of DBCC FREEPROCCACHE is


  DBCC execution completed. If DBCC printed error messages, contact your
  system administrator.


Which is the standard message.  Other DBCC functions that RDS does not support give error messages about permissions.

(A day later and after running that again, SQL Plans is still 38,321,280 kb)

Update from comments/answers

SELECT pool_id, name, cache_memory_kb, compile_memory_kb FROM sys.dm_resource_governor_resource_pools


outputs:

pool_id name    cache_memory_kb  compile_memory_kb
1   internal    38368408         1168080


DBCC FREEPROCCACHE ('internal') doesn't do anything differently

Update

SQL Error Log logs the following each time:

2017-10-19 14:26:47.22 spid85 SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations.

2017-10-19 14:26:47.22 spid85 SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations.

2017-10-19 14:26:47.22 spid85 SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations.


Update

There's an engine version upgrade on RDS from 13.00.2164.0.v1 to 13.00.4422.0.v1 available.  Though it is set to auto-minor-version upgrade, it hasn't seemed to keep us updated to the latest.  Will reboot and install that this weekend to see if it helps.
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.