When the SQL Server Cache Needs to Be Cleared

October 11, 2026

To check if the SQL Server cache needs to be cleared, you can monitor the performance and memory usage of your SQL Server instance. These checks will tell you whether clearing it would help:

Check Memory Usage:

SELECT
    total_physical_memory_kb / 1024 AS TotalMemoryMB,
    available_physical_memory_kb / 1024 AS AvailableMemoryMB,
    system_memory_state_desc AS MemoryState
FROM sys.dm_os_sys_memory;

Monitor Plan Cache Memory:

SELECT
    COUNT(*) AS CachedPages,
    SUM(virtual_memory_reserved_kb) / 1024 AS ReservedMemoryMB,
    SUM(virtual_memory_committed_kb) / 1024 AS CommittedMemoryMB
FROM sys.dm_os_memory_clerks
WHERE type = 'CACHESTORE_SQLCP' OR type = 'CACHESTORE_OBJCP';

Check Plan Cache Usage:

SELECT
    cache_type,
    objtype,
    AVG(usecounts) AS AvgUseCount,
    SUM(size_in_bytes) / 1024 / 1024 AS CacheSizeMB
FROM sys.dm_exec_cached_plans
GROUP BY cache_type, objtype;

Evaluate Performance Metrics:

SELECT
    total_worker_time / 1000 AS TotalCPUTimeMS,
    total_elapsed_time / 1000 AS TotalElapsedTimeMS,
    total_logical_reads AS TotalLogicalReads,
    total_logical_writes AS TotalLogicalWrites
FROM sys.dm_exec_query_stats;

If you notice high memory usage, excessive cache size, or performance degradation, it might be a good idea to clear the cache. You can use the following commands to clear different types of caches:

Caution: DROPCLEANBUFFERS and FREEPROCCACHE on a production server force every query to recompile and read its data from disk again, so performance drops sharply until the caches refill. Don’t run them without approval.

If the problem is plan cache bloat from single-use ad hoc plans, enable optimise for ad hoc workloads and clear only the ad hoc and prepared plans:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;
DBCC FREESYSTEMCACHE('SQL Plans');

 /* Checks if the cache needs  to be cleared now, or not
  DBCC FREESYSTEMCACHE('ALL');
    GO
    DBCC FREEPROCCACHE;
    GO
    DBCC DROPCLEANBUFFERS;
    GO

    DBCC FREESESSIONCACHE;
    */
DECLARE @MB decimal(19,3)
        , @Count bigint
        , @StrMB nvarchar(20)

SELECT @MB = sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(12,2)))/1024/1024
        , @Count = sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END)
        , @StrMB = convert(nvarchar(20), @MB)
FROM sys.dm_exec_cached_plans

IF @MB > 1000
    --DBCC FREEPROCCACHE;

    --DBCC DROPCLEANBUFFERS;
    --GO
    --DBCC FREESYSTEMCACHE('ALL');
    --GO
    --DBCC FREESESSIONCACHE;
       RAISERROR ('do it %s ', 10, 1, @StrMB);
ELSE
        RAISERROR ('Only %s MB is allocated to single-use plan cache – no need to clear cache now.', 10, 1, @StrMB)
go

From my SQL Server DBA handbook.

Performance

Clive Roberts