SQL SERVER – 2017 – Script to Clear Procedure Cache at Database Level

When my customer ask they want to clear the cache of the database, I always so NO. It is not necessary. However, I have come to the realization that customers are always right and sometimes even though I say no, they still want to do it. One way or another way, they will eventually do what they want. Let us learn about Script to Clear Procedure Cache at Database Level.

In earlier version when we have to clear cache for any database we had access to DBCC command which we could run to clear the cache for a particular plan or for the server.

Here is the script which you can run on SQL Server 2016 and earlier versions:

DBCC FREEPROCCACHE

In SQL Server 2017 we now have a new script which we can run at the database level to clear the cache for that particular database. This is indeed a blessing for many when you just want to clear the cache for the database and not for the entire server, you can run the following script.

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

Well, I am going to use this new script for all of my future engagements. Let me know if you have started to use the new script when you are busy with SQL Server performance tuning.

In any case, do not clear the cache of the production server, without consulting your manager who must know how CACHE works.

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