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

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.

Requirements

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

References

• DBCC FREESYSTEMCACHE (Transact-SQL) (Microsoft Docs)

  Requires ALTER SERVER STATE permission on the server.

• DBCC FREEPROCCACHE (Transact-SQL) (Microsoft Docs)

  Applies to: SQL Server, Parallel Data Warehouse

  • Requires ALTER SERVER STATE permission on the server.

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.