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')
