You have a server named Serverl that is hosted in an Azure virtual machine. Server1 con tains the following:
- One instance of SQL Server 2016 Enterprise
- 10 databases
- 500 stored procedures
You have a database named Databasel that is hosted on Server1.
Database1 contains 100 queries that are executed dynamically from web applications.
You plan to remove data from the procedure cache on Database1.
You have the following requirements:
- Changes to Database1 must not affect other databases that are hosted on Server1
- Changes to Database1 must not affect the performance of queries that are stored in other databases.
- The solution must minimize administrative effort.
You need to remove the data from the procedure cache as quickly as possible.
What should you do?
A. Run DBCC FREEPROCCACHE.
B. Run ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE
CACHE in the context of Database 1.
C. Run DBCC DROPCLEANBUFFERS.
D. Write a script that iterates through each stored procedure definition and add
WITH RECOMPILE to the definition.
Explanation:
You should run ALTER DATABASE SCOPED
CONFIGURATION CLEAR PROCEDURE CACHE in the context of Database! This statement lets you change the settings of a database without affecting other databases that are installed on the instance of SQL Server 2016.
Incorrect Answers:
A: You should not run DBCC FREEPROCCACHE. DBCC FREEPROCCACHE would clean the entire plan cache and would affect all databases. It is possible to remove a single plan from the cache by using the plan_handle argument to DBCC FREEPROCCACHE, but you would have to identify all plans that are related to Databasel, which requires a lot more administrative effort.
C: You should not run DBCC DROPCLEANBUFFERS. DBCC DROPCLEANBUFFERS will remove the clean pages from the buffer cache. Columnstore pages are removed from the columnstore cache.
D: You should not write a script that will iterate through each stored procedure definition and add WITH RECOMPILE to the definition. Each time the procedure is called, it will be recompiled, and this might degrade the servers performance.
This approach would require additional administrative effort to produce the script. In addition, some calls are made from the web application and the script would not have any control over these calls. In earlier versions of SQL Server, prior to the availability of the
ALTER DATABASE statement, this option would have been the way to avoid affecting other databases.
References: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql