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 DBCC ALTER DATABASE SCOPED CONFIGURATION commands with Database Console Commands (DBCC) for SQL Server.

Note:

• You must have VIEWER SERVER STATE and ALTER SERVER STATE permissions to run the following commands from the primary user account.
• To avoid potential issues, 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. All plans are evicted from the cache and any new queries then 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: Because the DBCC FREEPROCCACHE removes the entire plan and evicts the plans from the cache, use this command cautiously. 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 DBCC 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

Identify the top five caches and the memory that's used

Run the following command to identify the top five caches and the memory that's used:

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 (parameter sniffing issue)

Run the following command to identify the bad plan:

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);

Run one of the following commands to remove the bad plan:

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 a version that's earlier than SQL Server 2016, then run the following command:

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

Clear all caches

Run one of the following commands to clear all caches:

DBCC FREESYSTEMCACHE('ALL')

-or-

DBCC FREEPROCCACHE WITH NO_INFOMSGS;

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

Related information

How can I troubleshoot storage consumption in my RDS for SQL Server DB instance?