13

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!?)?

Memory Consumption Graph Memory Consumption Chart

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.

5

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.

  • 1
    Still I would suggest not to frequently clear SQLCP and go ahead and find out the root cause – Shanky Oct 24 '17 at 16:26
  • Entity Framework and a custom reporting feature that lets almost any user run almost any query, in a way, creates a lot of adhoc queries. If there was a way to just purge those, or even tell them to never be cached in the first place, it might help. So far, plan cache has hovered at about 5.8GB since the SP1 install. – Professional Sounding Name Oct 24 '17 at 17:32
  • Have you enabled Optimize for Ad-Hoc Workload. As compared to 38G if it is now taking 5 G I believe this would not be something to worry about considering the workload – Shanky Oct 24 '17 at 18:07
  • Yes, Optimize for Ad-Hoc Workload is set (as says above in question) – Professional Sounding Name Oct 24 '17 at 18:22
  • 1
    We saw this same issue on 13.0.4001.0. Had to bounce SQL to clear the proc cache. Nothing was being compiled, but the proc cache was 'stuck' and would not clear no matter what we tried. Haven't seen it happen again since bouncing SQL, but wonder if there is some kind of memory leak or bug. – GoodwinSQL Oct 25 '17 at 14:21
3

Requirements

In order to run DBCC FREEPROCCACHE or DBCC FREESYSTEMCACHE your account requires the permission ALTER SERVER STATE.

References

Resource Governor

Does AWS RDS allow for resource governor settings? If yes, then the syntax is:

DBCC FREEPROCCACHE ('<pool_name>'). 

Find out if you have pools with:

SELECT * FROM sys.dm_resource_governor_resource_pools

Verifying permissions

Run the following command to determine if you have these permissions:

SELECT * FROM sys.fn_my_permissions(NULL,NULL)

Limitations / Restrictions

There are some restrictions on the permissions granted to the administrators of AWS RDS instances as documented in the article Microsoft SQL Server on Amazon RDS in the chapter Microsoft SQL Server Security

Here an overview:

The following server-level roles are not currently available in Amazon RDS:

  • bulkadmin
  • dbcreator
  • diskadmin
  • securityadmin
  • serveradmin
  • sysadmin

The following server-level permissions are not available on SQL Server DB instances:

  • ADMINISTER BULK OPERATIONS
  • ALTER ANY CREDENTIAL
  • ALTER ANY EVENT NOTIFICATION
  • ALTER ANY EVENT SESSION
  • ALTER ANY SERVER AUDIT
  • ALTER RESOURCES
  • ALTER SETTINGS (You can use the DB Parameter Group APIs to modify parameters. For more information, see Working with DB Parameter Groups.
  • AUTHENTICATE SERVER
  • CONTROL_SERVER
  • CREATE DDL EVENT NOTIFICATION
  • CREATE ENDPOINT
  • CREATE TRACE EVENT NOTIFICATION
  • EXTERNAL ACCESS ASSEMBLY
  • SHUTDOWN (You can use the RDS reboot option instead)
  • UNSAFE ASSEMBLY
  • ALTER ANY AVAILABILITY GROUP (SQL Server 2012 only)
  • CREATE ANY AVAILABILITY GROUP (SQL Server 2012 only)

These restrictions/limitations may have changed since the document was published.

Other resources

Brent Ozar wrote an article "SQL Server RDS" where he noted that...

Many DBCC commands don’t work at all – DBCC DBINFO and DBCC LOGINFO were explicitly denied. I did, however, discover that I could run DBCC FREEPROCCACHE as often as I wanted.

Keep in mind that these limitations may change over time – new features are added on a regular basis.

Amazon Support

If your account has the required permissions and you are not able to free up cache, then you may want to open up a support ticket with Amazon.

  • Updated question with more details based in this. Thank you for your thoughtful and detailed response. I do run across permissions issues with other things in RDS, but this should be supported. I will contact Amazon Support later today if I don't get much activity here. It seemed more like a SQL Server problem than an RDS problem, and I don't know how knowledgeable or specific their support will be compared to this fine community! – Professional Sounding Name Oct 19 '17 at 14:16
  • 1
    I appreciate your feedback. I guess you might want to open up a ticket with either Amazon or Microsoft seeing as nothing seems to work. And then again, it might be a bug or a feature (limitation due to ....). – John aka hot2use Oct 20 '17 at 8:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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