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…

The correct answer is C. OPTIMIZE_FOR_AD_HOC_WORKLOADS. The optimize for ad hoc workloads option is used to improve the efficiency of the plan cache for workloads that contain many single use ad hoc batches. When this option is set to 1, the Database Engine stores a small compiled plan stub in the plan cache when a batch is compiled…

Submitted by parkjh· Mar 6, 2026Optimize query performance

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?

Exhibits

Options

ALEGACY_CARDINALITY_ESTIMATION
BQUERY_OPTIMIZER_HOTFIXES
COPTIMIZE_FOR_AD_HOC_WORKLOADS
DACCELERATED_PLAN_FORCING

How the community answered

(20 responses)

A
5% (1)
B
15% (3)
C
75% (15)
D
5% (1)

Explanation

The optimize for ad hoc workloads option is used to improve the efficiency of the plan cache for workloads that contain many single use ad hoc batches. When this option is set to 1, the Database Engine stores a small compiled plan stub in the plan cache when a batch is compiled for the first time, instead of the full compiled plan. This helps to relieve memory pressure by not allowing the plan cache to become filled with compiled plans that are not reused. https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/optimize-for-ad-hoc- workloads-server-configuration-option?view=sql-server-ver15

Community Discussion

No community discussion yet for this question.

Full DP-300 Practice