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');
DBCC FREESYSTEMCACHE ('SQL Plans') WITH MARK_IN_USE_FOR_REMOVAL
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)
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
pool_id name cache_memory_kb compile_memory_kb 1 internal 38368408 1168080
DBCC FREEPROCCACHE ('internal') doesn't do anything differently
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.
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.