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.

SQL SERVER - Cleanup Plan Cache For a Single Database cleanupplancache

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)

, , ,
Previous Post
SQL SERVER – How to Optimize Your Server Performance by Reducing IO Waits?
Next Post
SQLBits 2019: Attention Pre-Con Attendees – One Free Consulting Hour

Related Posts

1 Comment. Leave new

  • Hi Pinal,

    In the last post received by email regarding to “SQL SERVER – Cleanup Plan Cache For a Single Database”, the commands were not displayed on the email.

    Thanks and regards
    Antonio

    Reply

Leave a Reply

Menu