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

Short description

There are multiple ways to clear the SQL Server cache, from very granular to full cache clearing. You can use the following tools to clear the cache. All of these tools are available with Database Console Commands (DBCC) for SQL Server:

• DBCC FREEPROCCACHE: Clears the cache by removing the entire plan cache. This command can also remove specific plans or remove cache entries tied to a resource pool.
• DBCC FREESYSTEMCACHE: In addition to removing elements from the plan cache, DBCC FREESYSTEMCACHE can clear other memory caches.
• DBCC ALTER DATABASE SCOPED CONFIGURATION: This command is available starting with SQL Server 2016.

Resolution

Note: It's a best practice to test the following commands thoroughly before running them in your production environment. Testing helps identify potential issues that might occur during implementation.

Identify the top five caches and the memory used

Run the following command to identify the top five caches and the memory 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

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE [plan_handle];

Remove as-needed and prepared plans from the cache

If you have too many as-needed 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're running SQL Server 2016 or higher, then run the following command:

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

or

DBCC FREESYSTEMCACHE('DatabaseName')

If you're using a SQL Server version prior to SQL 2016, then run the following command:

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

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. This command isn't supported in Amazon RDS for SQL Server because it requires membership in the sysadmin fixed server role.

---