Microsoft DP-300 Exam - Topic 1 Question 115 Discussion

Actual exam question for Microsoft's DP-300 exam

Question #: 115
Topic #: 1

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?

ALEGACY_CARDINALITY_ESTIMATION

BQUERY_OPTIMIZER_HOTFIXES

COPTIMIZE_FOR_AD_HOC_WORKLOADS

DACCELERATED_PLAN_FORCING

Show Suggested Answer

Suggested Answer: C

OPTIMIZE_FOR_AD_HOC_WORKLOADS = { ON | OFF }

Enables or disables a compiled plan stub to be stored in cache when a batch is compiled for the first time. The default is OFF. Once the database scoped configuration OPTIMIZE_FOR_AD_HOC_WORKLOADS is enabled for a database, a compiled plan stub will be stored in cache when a batch is compiled for the first time. Plan stubs have a smaller memory footprint compared to the size of the full compiled plan.

Incorrect Answers:

A: LEGACY_CARDINALITY_ESTIMATION = { ON | OFF | PRIMARY }

Enables you to set the query optimizer cardinality estimation model to the SQL Server 2012 and earlier version independent of the compatibility level of the database. The default is OFF, which sets the query optimizer cardinality estimation model based on the compatibility level of the database.

B: QUERY_OPTIMIZER_HOTFIXES = { ON | OFF | PRIMARY }

Enables or disables query optimization hotfixes regardless of the compatibility level of the database. The default is OFF, which disables query optimization hotfixes that were released after the highest available compatibility level was introduced for a specific version (post-RTM).

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql

by Vashti at Jun 11, 2025, 05:59 AM

Limited Time Offer

25%

Off

Get Premium DP-300 Questions as Interactive Web-Based Practice Test or PDF

Contribute your Thoughts:

Submit Cancel

Delisa

5 months ago

Wait, are we sure C is the best option? Seems too simple.

upvoted 0 times

...

Marla

5 months ago

Totally agree, C is the way to optimize for ad hoc workloads!

upvoted 0 times

...

Cecil

5 months ago

B is also a good choice if you're dealing with hotfixes.

upvoted 0 times

...

Filiberto

6 months ago

I thought A was the go-to for cardinality issues, but C makes sense too.

upvoted 0 times

...

Lamar

6 months ago

You should go with C, it helps with memory pressure.

upvoted 0 times

...

Rosamond

6 months ago

I think we practiced a similar question, and if I recall correctly, optimizing for ad hoc workloads was the key to reducing memory usage.

upvoted 0 times

...

Deane

6 months ago

I vaguely recall that enabling certain configurations can help with memory pressure, but I can't remember the specifics.

upvoted 0 times

...

Yasuko

6 months ago

This question feels familiar; I think we discussed how the plan cache can get overloaded with single-use plans. Could it be option C?

upvoted 0 times

...

Afton

7 months ago

I remember something about optimizing for ad hoc workloads, but I'm not entirely sure if that's the right answer here.

upvoted 0 times

...

Billye

7 months ago

Based on the information provided, I believe the answer is OPTIMIZE_FOR_AD_HOC_WORKLOADS. This setting can help reduce the memory usage of the plan cache by discarding compiled plans that are used only once. I'm fairly confident this is the right approach.

upvoted 0 times

...

Amie

7 months ago

I'm a bit confused by the question. The database configurations seem relevant, but I'm not sure how they relate to the plan cache issue. I'll need to do some research on these settings to figure out the best solution.

upvoted 0 times

...

Jess

7 months ago

Okay, let's see. The plan cache is full of one-time compiled plans, and we need to relieve the memory pressure. I think OPTIMIZE_FOR_AD_HOC_WORKLOADS might be the way to go, but I'll double-check the other options just to be sure.

upvoted 0 times

...

Lang

7 months ago

Hmm, this looks like a tricky one. I'll need to carefully review the database configurations and understand the problem before deciding on the best approach.

upvoted 0 times

...

James

10 months ago

I'm going with C) OPTIMIZE_FOR_AD_HOC_WORKLOADS. It's the only one that actually makes sense in the context of the problem. The others just seem like random database knobs to twiddle.

upvoted 0 times

...

Chantell

10 months ago

Haha, I love how these options are like a mix of real and made-up sounding things. 'LEGACY_CARDINALITY_ESTIMATION' - sounds like something straight out of a Dilbert comic!

upvoted 0 times

...

Wava

10 months ago

Hmm, I'm not sure about that. What if we try D) ACCELERATED_PLAN_FORCING? It might help us get those plans out of the cache a bit faster.

upvoted 0 times

France

8 months ago

User1: Sounds like a plan. Let's configure it and monitor the results.

upvoted 0 times

...

Marguerita

9 months ago

User2: Let's give it a try and see if it helps with the memory pressure.

upvoted 0 times

...

Shoshana

9 months ago

User1: I think D) ACCELERATED_PLAN_FORCING could work.

upvoted 0 times

...

Willard

10 months ago

I'm not sure, but I think LEGACY_CARDINALITY_ESTIMATION could also help in this situation.

upvoted 0 times

...

Merrilee

10 months ago

I agree with Lizette. It seems like the best option to relieve memory pressure.

upvoted 0 times

...

Lizette

11 months ago

I think we should configure QUERY_OPTIMIZER_HOTFIXES.

upvoted 0 times

...

Vinnie

11 months ago

I think C) OPTIMIZE_FOR_AD_HOC_WORKLOADS is the way to go. It's the only option that directly addresses the issue of the plan cache being full of one-time plans.

upvoted 0 times