How to Clean up SQL Server Memory
We do get a situation where SQL Server will not be in a position to allocate memory for the new resources.
At times, we have to clear up the memory to allocate space to new resources.
DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
GO
DBCC FREESYSTEMCACHE ('ALL')
GO
DBCC FREESESSIONCACHE
Above commands will clear the cache of system.
Note: Buffer cache size will not be reduced by running the above commands. That would be still the same as it was earlier before running the commands.
To find the cached size per database
SELECT count(*)*8/1024 AS 'Cached Size (MB)' ,
CASE database_id WHEN 32767 THEN 'ResourceDb'
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
To find the cached size of the plans
SELECT objtype AS 'Cached Object Type', count(*) AS 'Number of Plans',
sum(cast(size_in_bytes AS BIGINT))/1024/1024 AS 'Plan Cache Size (MB)', We do get a situation where SQL Server will not be in a position to allocate memory for the new resources.
At times, we have to clear up the memory to allocate space to new resources.
DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
GO
DBCC FREESYSTEMCACHE ('ALL')
GO
DBCC FREESESSIONCACHE
Above commands will clear the cache of system.
Note: Buffer cache size will not be reduced by running the above commands. That would be still the same as it was earlier before running the commands.
To find the cached size per database
SELECT count(*)*8/1024 AS 'Cached Size (MB)' ,
CASE database_id WHEN 32767 THEN 'ResourceDb'
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
To find the cached size of the plans
SELECT objtype AS 'Cached Object Type', count(*) AS 'Number of Plans',
avg(usecounts) AS 'Avg Use Count'
FROM sys.dm_exec_cached_plans
GROUP BY objtype
Hope the above information helps
Thanks
