SQL SERVER – How to Clear Plan Cache with Database Scoped Configuration?

Question: How to Clear Plan Cache with Scoped Configuration?

Answer: Database Scoped Configuration is a relatively new feature introduced in SQL Server 2016 and will be continued for the future version of SQL Server.

SQL SERVER - How to Clear Plan Cache with Database Scoped Configuration? plancache1-800x173

In SQL Server Management Studio, we can easily find these settings when we go to property of the database, under options sub tab. You can see there are a total of four different scoped configuration settings for their primary server and secondary server.

Solarwinds

The available options are

  • Legacy Cardinality Estimation
  • Max DOP
  • Parameter Sniffing
  • Query Optimizer Fixes

However, there is one more database scoped configuration which is not there and only accessible via T-SQL only and it is for clearing the cache of the database plans. There are many different ways to remove the cache for the database plans and the most popular is DBCC command.

You can run following command to clear the plan cache from the database.

DBCC FREEPROCCACHE

However, the same result, you can now achieve with the Database Scoped Configuration as well.

You can first select the database context with the USE Database command and next run following command to clear the plan cache.

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE

The above query will remove the plan cache from the database.

Well, that’s it. Please note that running above command on production will remove all of your plan cache and you may see an instant performance degradation of your database. Please do not run on this production.

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

Solarwinds
, , , ,
Previous Post
SQL SERVER – Difference Between spid, kpid and ecid in sysprocesses
Next Post
SQL Server Management Studio – New Feature – Object Explorer Query Tracking

Related Posts

1 Comment. Leave new

  • Hi sir,i have one query regarding cache clean…when is really required to clean cache??as sql server manages available memory appropriately…means when query required memory than it will get assigned from available memory and if it is less than available memory than dirty pages in memory get return back to memory and clean pages get flush from memory..so what is the need to clean catche???

    Reply

Leave a Reply

Menu