SQl Database Cache Cleanup

Sometimes we have some issues because of SQL server database cache on measuring query performance, when we comparing the execution time of two different queries, it’s important to clear the cache of database to confirm the execution of the first query does not affect the performance of the second query

Here is the query to get the list of database with their cache size

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

        CASE database_id

          WHEN 32767 THEN 'ObjectDb'

          ELSE DB_NAME(database_id)

        END AS 'Database'

FROM    sys.dm_os_buffer_descriptors

GROUP BY DB_NAME(database_id) ,

        database_id

ORDER BY 'Cached Size (MB)' DESC;

Result will be like below

We can use below commands to check Resource pool and cached memory size

SELECT name AS 'Pool Name',

       cache_memory_kb / 1024.0 AS [cache_memory_MB],

       used_memory_kb / 1024.0 AS [used_memory_MB]

FROM sys.dm_resource_governor_resource_pools;

We can use below query to clear cache of all databases or any specific one

GO

DBCC FREEPROCCACHE /* Clear the entire plan cache for a SQL Server instance. */

GO

DBCC DROPCLEANBUFFERS  /* Removes all clean buffers from the buffer pool */

GO

DBCC FREESYSTEMCACHE ('All') /* Clear all unused cache entries from all caches, we can use specifc Resource Pool Name instead of 'All' */

GO

DBCC FREESESSIONCACHE /*Clear the distributed query connection cache */

If we wish to clear plan cache for any specific database, then we can use 

/* Clear the plan cache for a specific Database. */

DBCC FLUSHPROCINDB (DatabaseID) GO

We can get databse Id using 

SELECT db_ID()

For specific objects like stored procedure, trigger, table, view, function in the current database, we can use

EXEC sp_recompile N'[ procedure name, trigger, table, view]';

Below are some more use of FREESYSTEMCACHE

/* Clear the ad hoc and prepared plan cache for the entire instance. */

DBCC FREESYSTEMCACHE ('SQL Plans')

/* Clear all cached table variables and temp tables. */

DBCC FREESYSTEMCACHE ('Temporary Tables & Table Variables')

/* Clear for a specific user database. */

DBCC FREESYSTEMCACHE ('userdatabase')