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

24 June,2015 by Jack Vamvas

 

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


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to list all SQL Server Caches and use DBCC FREESYSTEMCACHE to clear


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer