SQL SERVER – Cleanup Plan Cache For a Single Database

During my recent Comprehensive Database Performance Health Check, we had implemented quite a few improvements in the database. After making the improvements in the database, we reached to the point where we needed to clear the cache of the server to see how our improvements fairs with the changes which we have made.

While we were discussing about the clearing the cache, the senior DBA at the organization, in rush immediately jumped to the Query Editor in the SQL Server Management Studio (SSMS) and wrote down following command.

DBCC FREEPROCCACHE

As soon as I saw him writing down the above command, I told him to stop immediately and explained that if he ran above command on the server it will drop the plan cache for the ALL the database on the server and that is something not recommended. If drop the plan cache for all the server, during the business hours, SQL Server will be under pressure to re-create all the plans and it may negatively impact the performance as well.

As we had made improvements in a single database and our need was to clear the cache for a single database and hence, here is the command to remove the cached plans for a single database only.

USE DatabaseToClearCache;
DECLARE @dbid INT = DB_ID();
DBCC FLUSHPROCINDB (@dbId);

If you are using SQL Server 2016 or later version, you can also run the following command:

USE DatabaseToClearCache;
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE

Well, that’s it. It is very simple to remove the cache for a single database, I strongly suggest that you only do it in the extreme conditions, as in most of the cases, you do not need it.

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