Monitor, configure, and optimize database resources →hardMultiple Choice

DP-300 Practice Question: Monitor, configure, and optimize database resources

Exhibit

Refer to the exhibit.

-- Azure SQL Database automatic tuning policy (JSON)
{
  "properties": {
    "state": "Enabled",
    "options": {
      "forcePlan": {
        "state": "Enabled"
      },
      "createIndex": {
        "state": "Enabled"
      },
      "dropIndex": {
        "state": "Enabled"
      }
    }
  }
}

Refer to the exhibit. You have configured the automatic tuning policy as shown. After a week, you notice that an index has been dropped automatically, causing a critical query to run slowly. What should you do to prevent this in the future while still benefiting from automatic tuning?

⚠ Common exam trap

DP-300 often tests the misconception that you must disable automatic tuning entirely to stop one unwanted action, when in fact each tuning option (createIndex, dropIndex, forceLastGoodPlan) can be toggled independently.

Answer choices

Why each option matters

Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.

Correct answer & explanation

✓

Set the dropIndex option state to Disabled in the tuning policy.

The dropIndex option in the automatic tuning policy controls whether SQL Server/Azure SQL can automatically drop indexes it deems unused. Setting dropIndex to Disabled preserves the createIndex and forceLastGoodPlan tuning actions while preventing the risky automatic drop that broke the critical query. This is the targeted fix that keeps automatic tuning benefits without the destructive behavior.

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

✗
Manually create the dropped index and mark it as a required index.
Why it's wrong here
Manually recreating the index and flagging it as required does not stop automatic tuning from dropping it again, because the policy still permits index drops. It is tempting as an immediate fix. Marking indexes as required belongs with index-creation guidance, not with overriding an active automatic-tuning drop policy.
✗
Enable Query Store to track index usage.
Why it's wrong here
Query Store records execution statistics and plan history; it does not protect an index from being dropped by automatic tuning. It is tempting because Query Store underpins tuning recommendations. Query Store would be the right choice when diagnosing regressed query plans, not when excluding a specific index from automatic tuning's drop actions.
✓
Set the dropIndex option state to Disabled in the tuning policy.
Why this is correct
Disabling dropIndex prevents automatic tuning from dropping indexes while leaving other tuning options active. This satisfies the requirement to stop the specific action causing slow queries while still benefiting from automatic tuning for index creation and plan regression correction.
✗
Disable automatic tuning entirely.
Why it's wrong here
Disabling automatic tuning entirely abandons the other tuning benefits the question requires retaining, such as plan regression correction and index creation. It is tempting as a guaranteed way to stop drops. Disabling tuning would be correct only where no automatic tuning is wanted at all.

Go deeper

DP-300 Practice Question: Monitor, configure, and optimize database resources

Exhibit

Why each option matters

Option-by-option breakdown

Related to this question