Skip to content

Cleaning up Troublesome SQL Server Caches Using Up Memory

Problem

The current environment is rather unique, with a unique workload that requires writing things like this.  One of those being having the transactions cache on the server take up nearly 10 GBs of our memory.  Being that we would like that 10 GBs of memory go towards the Buffer Pool I wrote an automated process to clear the cache when it reaches a certain size.  Also being the automation junkie I made it where we could use it for other caches that could be using the same command and of course log to a table just in case we need to track down issues.  We eventually added SQL Plans and Lock Manager to the solution.

Step 1

First, we need to create a table to store our information on the caches we would like to clear on an automated basis and populate it with values.

For example, we clear SQL Plans, if we 10,000 plans are Adhoc or Prepared plans that take up 5GBs of memory or Single Used Plans is greater than 10,000 or the memory used for Adhoc or Prepared plans if more than 50% of memory.  We clear Transactions cache if is more than 2 GBs and Lock Manager : Node 0 if it is more than 2 GBs.

Step 2

Now we create the stored procedure to handle the logic and a table to store every time it clears one of the things we add it to the table.  The procedure in the end calls DBCC FREESYSTEMCACHE.  There is a @debug parameter if you pass in 1 that will tell what it cache it will clear on your system if you were to run it without the debug parameter.  Then we create a table to log when we clear any cache.

Step 3

Now you schedule a SQL Agent job to call the stored procedure CacheManagement_ClearCache.  We run ours every hour and this process has been in production for over 3 years without any issues.

Conclusion

I would be curious if anybody finds this useful and has any other use cases for it that we haven’t run across.  So please leave comments below.

3 thoughts on “Cleaning up Troublesome SQL Server Caches Using Up Memory

  1. Pingback: Automating Cache Cleanup – Curated SQL

  2. Malcolm says:

    Hi Tracey

    There is a couple of typo’s
    In Step1
    Line 2 – CacheManagementConfigD –should be CacheManagementConfigID

    In Step2 should that be single_pages_kb or multiple_pages_kb? (i used single_pages_kb)

    also removed “on Tables” to get mine working.

    regards,
    Malcolm

    Reply
  3. Tracy Boggiano says:

    I forget not everyone puts their tables outside of PRIMARY so I removed Tables. In Step 2, it should be ran as coded. This a process I wrote 3 years ago with no code modifications.

    Reply

Leave a Comment