SQL SERVER – Remove Unused Cache MARK_IN_USE_FOR_REMOVAL

What we are going to discuss in today’s blog post is not something I do every day at the Comprehensive Database Performance Health Check. Today’s topic is very unique and one should not try it out without the proper consultation of an expert. Let us discuss how to Remove Unused Cache MARK_IN_USE_FOR_REMOVAL.

Remove Unused Cache

While I was working with a client, we realized that overall their system was working fine after we tuned their Disk IO and CPU. However, there was still some issue with their SQL Server and we figured out that it was related to the memory. We realized that there were lots of unused plans in the cache and by removing them we were able to make the memory available to the SQL Server.

Here is the command which we ran to release entries from all current caches once the entries become unused.

DBCC FREESYSTEMCACHE ('ALL') 
WITH MARK_IN_USE_FOR_REMOVAL;

Now, this process should be automatic and one should not be doing this manually. However, as I said in our case it was a special situation and we had to do this once to clear up unused cache, and right after that, we were able to get the necessary performance.

Well, once we ran the above script everything ran fine and we did not have to do any further management. If you ever face an SQL Server performance problems, you can always reach out to me via Twitter or linkedIn.

Reference: Pinal Dave (https://blog.sqlauthority.com)