DP-300 · Question #14
DP-300 Question #14: Real Exam Question with Answer & Explanation
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 f
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?
Options
- ALEGACY_CARDINALITY_ESTIMATION
- BQUERY_OPTIMIZER_HOTFIXES
- COPTIMIZE_FOR_AD_HOC_WORKLOADS
- DACCELERATED_PLAN_FORCING
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.