DP-300 Certification Practice Question #167

Question

You have an Azure SQL database.
You discover that the plan cache is full of compiled plans that were used only once.
You run the select * from sys.database_scoped_configurations Transact-SQL command and receive the results shown in the following table.

You need relieve the memory pressure.
What should you configure?

Accepted Answer

To relieve plan cache bloat and memory pressure caused by compiled plans that are executed only once, you should enable the `OPTIMIZE_FOR_AD_HOC_WORKLOADS` database-scoped configuration (Option C). When enabled, this option stores a small plan stub instead of the full compiled plan on the first compilation of an ad-hoc query. If the query is executed again, SQL Server compiles and stores the full query plan. This prevents single-use plans from consuming valuable memory in the plan cache.

More DP-300 practice questions