How do I clear the buffer cache or other caches without rebooting my RDS for SQL Server instance?
I want to clear the Microsoft SQL Server cache on my Amazon Relational Database Service (Amazon RDS) for Microsoft SQL Server instance. How can I do this without restarting my DB instance?
"I want to clear the Microsoft SQL Server cache on my Amazon Relational Database Service (Amazon RDS) for Microsoft SQL Server instance. How can I do this without restarting my DB instance?Short descriptionThere 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.ResolutionNote: 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 usedRun 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 nameORDER BY SizeKb DESCRemove 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.textFROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS stWHERE st.text LIKE N'%[search string]%' OPTION (RECOMPILE);Run one of the following commands to remove the bad plan:DBCC FREEPROCCACHE (plan_handle);orALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE [plan_handle];Remove as-needed and prepared plans from the cacheIf 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 databaseIf you're running SQL Server 2016 or higher, then run the following command:ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;orDBCC 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 cachesDBCC FREESYSTEMCACHE('ALL')orDBCC 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.Follow"