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.
Best Answer
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.