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.
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)