How to list all SQL Server Caches and use DBCC FREESYSTEMCACHE to clear

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

DBCC FREESYSTEMCACHE(‘ALL’)

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.

Read More on PLE ,sys.dm_os_memory_clerks,Query Timeouts

SQL Server page life expectancy and memory bottleneck

Sys.dm_os_memory_clerks and AWE memory allocation

SQL Server – How to troubleshoot query timeouts