How to Clear SQL Server Cache Memory

Sometimes there are issues due to what SQL Server has stored in its cache. Here are some possible reasons which may create caching performance issues.

• Ad-hoc Query workload issues due to cache bloat
• Excessive use of dynamic T-SQL code
• Server has insufficient memory or not properly assigned to SQL instances
• Memory pressure generated due to heavy long running transactions
• Server has frequent recompilation events

To overcome these issues we may need to flush the plan cache or buffer cache.

Let's consider some tables to get the details information of Cache memory.

This query returns a row for each query plan that is cached by SQL Server for faster query execution.

SELECT * FROM sys.dm_exec_cached_plans;

It returns information about all the data pages that are currently in the SQL Server buffer pool.

SELECT * FROM sys.dm_os_buffer_descriptors;

It returns memory information from the operating system.

SELECT * FROM sys.dm_os_sys_memory
WHERE system_memory_state_desc = 'Available physical memory is low';

It returns a snapshot of the health of a cache in SQL Server and provides run-time information about the cache entries allocated, their use, and the source of memory for the cache entries.

SELECT * FROM sys.dm_os_memory_cache_counters

ORDER BY (pages_kb + pages_in_use_kb) DESC;

Check overall system memory status and query-execution memory reservations

SELECT * FROM sys.dm_exec_query_memory_grants;

Now we generate some queries: 

How many plans are in the cache

SELECT COUNT(*) FROM sys.dm_exec_cached_plans;

How much space is being used by the plan cache

SELECT (SUM(size_in_bytes)/1024)/1024 as 'MB' 

FROM sys.dm_exec_cached_plans;

Plans which have only been used once

SELECT * FROM sys.dm_exec_cached_plans

WHERE objtype = 'ADHOC' and usecounts < 2;

To find out what single-use plans have been stored for your instance issue this statement to get the text and size of the single-use plans in your plan cache:

SELECT K.size_in_bytes, K.usecounts, K.objtype, LEFT(sql.[text], 200) AS [text]

FROM   sys.dm_exec_cached_plans K 

        OUTER APPLY sys.dm_exec_sql_text(K.plan_handle) sql

--WHERE usecounts < 2 

ORDER BY size_in_bytes DESC;

How much space is being used by the single use plan cache

SELECT objtype AS 'Cached Object Type', count(*) AS 'Number of Programs',

sum(cast(size_in_bytes AS BIGINT))*8/1024 AS 'Plan Cache Size (MB)',

avg(usecounts) AS 'Avg Use Count' FROM sys.dm_exec_cached_plans

GROUP BY objtype;

Sample showing searching the cached size per database

SELECT (CASE WHEN ([is_modified] = 1) THEN 'Dirty' ELSE 'Clean' END) AS 'Page State',

              (CASE WHEN ([database_id] = 32767) THEN 'Resource Database' ELSE DB_NAME(database_id) END) AS 'Database Name',

              COUNT(*) AS 'Page Count',

              COUNT(*)*8/1024 AS 'Cached Size (MB)'

FROM   sys.dm_os_buffer_descriptors

GROUP BY [database_id], [is_modified]                             

ORDER BY [database_id], [is_modified];

Finally we run the scrip to clear SQL Server cache memory:

DBCC FREESYSTEMCACHE ('ALL');

DBCC FREESESSIONCACHE;

DBCC DROPCLEANBUFFERS;

DBCC FREEPROCCACHE; (AVOID TO RUN IN LIVE SERVER)

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

EXEC sys.sp_configure

RECONFIGURE WITH OVERRIDE;

Source: https://www.mssqltips.com/sqlservertip/4714/different-ways-to-flush-or-clear-sql-server-cache/
https://logicalread.com/sql-server-minimize-single-use-plans-tl01/#.XsF-NZ4zbIU

If there is any other better option,

Please share it here or mail to me arkaa4@gmail.com

Thank You,

Arka Gupta.

https://in.linkedin.com/in/arkagupta