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.
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.
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)
2 Comments. 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???
If we want to check the execution plan of a query again after making some changes, we need to clear the cache to get rid of the old plans that are cached in memory.