How do I clear the buffer cache or other caches without rebooting my RDS for SQL Server instance?

Short description

To clear the cache, use the DBCC FREEPROCCACHE, DBCC FREESYSTEMCACHE, and ALTER DATABASE SCOPED CONFIGURATION commands for SQL Server.

Note: You must have VIEW SERVER STATE and ALTER SERVER STATE permissions to run the following commands from the primary user account. It's a best practice to test these commands before you run them in your production environment.

The DBCC FREEPROCCACHE command removes the entire plan cache. The command evicts all plans from the cache, and any new queries compile new plans. This command can also remove specific plans or remove cache entries tied to a resource pool. For more information, see DBCC FREEPROCCACHE (Transact-SQL) on the Microsoft website.

Important: Use the DBCC FREEPROCCACHE command cautiously in production environments. For more information, see Remarks on the Microsoft website.

The DBCC FREESYSTEMCACHE command removes elements from the plan cache and can also clear other memory caches. For more information, see DBCC FREESYSTEMCACHE (Transact-SQL) on the Microsoft website.

The ALTER DATABASE SCOPED CONFIGURATION command clears the procedure plan cache. For more information, see ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL) on the Microsoft website.

Resolution

Check the top five caches and their memory usage

Run the following command:

SELECT TOP(5) name AS [Name],SUM(pages_kb) AS [SizeKb]
FROM sys.dm_os_memory_cache_counters WITH (NOLOCK)
GROUP BY name
ORDER BY SizeKb DESC

Remove one plan from the cache

To identify the bad plan, run the following command:

SELECT cp.plan_handle, cp.objtype, cp.usecounts,DB_NAME(st.dbid)
AS [DatabaseName], st.text
FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE st.text LIKE N'%[search string]%' OPTION (RECOMPILE);

To remove the bad plan, run one of the following commands:

DBCC FREEPROCCACHE (plan_handle);

-or-

USE DatabaseName;
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE [plan_handle];

Remove optional and prepared plans from the cache

If you have too many optional and prepared plans in the cache, then run the following command to remove them:

DBCC FREESYSTEMCACHE ('SQL Plans')

Clear the plan cache for the current database

If you use SQL Server 2016 or later, then run one of the following commands:

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

-or-

DBCC FREESYSTEMCACHE('DatabaseName')

If you use an earlier version of SQL Server, then run the following command:

declare @dbid int = (select DB_ID())
DBCC FLUSHPROCINDB (@dbid);

Clear all caches

Run one of the following commands:

DBCC FREESYSTEMCACHE('ALL')

-or-

DBCC FREEPROCCACHE WITH NO_INFOMSGS;

Note: The DBCC DROPCLEANBUFFERS command clears all caches and the buffer pool. Amazon RDS for Microsoft SQL Server doesn't support this command because it requires membership in the sysadmin fixed server role.

Related information

How do I optimize storage consumption in my Amazon RDS for SQL Server DB instance?