Question: How can I list all SQL Server Caches?
Rather than clearing down all the caches , I'd like to have control of every cache. How can I generate a DBCC FREESYSTEMCACHE statement for every cache?
Answer: The sys.dm_os_memory_clerks DMV returns the set of all memory clerks that are currently active in the instance of SQL Server. Read more on the difference between sys.dm_os_memory_clerks and Sys.dm_os_memory_objects on Memory objects allocated by SQL Server
Using the information derived from sys.dm_os_memory_clerks , you can generate DBCC FREESYSTEMCACHE statements for every cache.
Example 1 : Use FREESYSTEMCACHE to clear just the SQL Plans
DBCC FREESYSTEMCACHE(‘SQL Plans’)
Example 2 : List all SQL Server caches and generate DBCC FREESYSTEMCACHE statements
select 'DBCC freesystemcache ('+''''+name+''''+')' from sys.dm_os_memory_clerks group by name
Example 3 : Clear all caches . Don’t run in PROD
These are strong commands . Before executing in a busy Production environment consider the impact and whether it's acceptable to the users.
Theses commands could clear a problem but spend some time to Find the Root cause of a SQL Server Performance downgrade.