Monitor, configure, and optimize database resources →mediumMultiple Choice

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

Exhibit

Azure SQL Database Intelligent Insights log:

{
  "id": "/subscriptions/.../resourceGroups/.../providers/Microsoft.Sql/servers/.../databases/.../metrics",
  "properties": {
    "startTime": "2024-05-01T10:00:00Z",
    "endTime": "2024-05-01T11:00:00Z",
    "metrics": [
      {
        "name": "cpu_percent",
        "average": 85,
        "max": 99,
        "min": 20
      },
      {
        "name": "physical_data_read_percent",
        "average": 95,
        "max": 100,
        "min": 50
      },
      {
        "name": "log_write_percent",
        "average": 10,
        "max": 15
      }
    ],
    "observations": [
      {
        "type": "Degradation",
        "subType": "ResourceLimit",
        "details": "DTU consumption reached 100% for extended period. Consider scaling up or optimizing queries."
      }
    ]
  }
}

Current database tier: Standard S2 (50 DTU)

Refer to the exhibit. An Azure SQL Database is receiving Intelligent Insights degradation alerts. Which action should be taken first?

⚠ Common exam trap

DP-300 often tests the misconception that any performance alert should be resolved by scaling up resources, when in fact Intelligent Insights alerts are designed to be addressed first through automatic tuning recommendations that target the root cause.

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

✓

Implement automatic tuning recommendations

Intelligent Insights is an Azure SQL Database feature that uses built-in intelligence to detect and diagnose performance degradation, and it surfaces root cause analysis along with recommended actions. The first and most appropriate response is to apply the automatic tuning recommendations it provides, because Intelligent Insights alerts are diagnostic in nature and typically point to issues like missing indexes, parameter sniffing, or plan regressions that automatic tuning can resolve without changing the service tier or storage. Scaling resources or changing tiers does not address the underlying query-level cause and may be unnecessary or costly.

Answer analysis

Option-by-option breakdown

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

✗
Increase the maximum storage size
Why it's wrong here
Intelligent Insights alerts flag query-plan regressions, blocking, or resource contention, not capacity exhaustion, so storage size is irrelevant. Increasing maximum storage is the right first action when the database approaches its size limit; here the diagnosis should target the identified performance regression.
✗
Change the service tier to BusinessCritical
Why it's wrong here
Intelligent Insights alerts already identify the specific query or workload causing degradation; changing tier to Business Critical only alters underlying hardware and replica count, leaving the diagnosed root cause unaddressed. It is tempting because Business Critical provides local SSD and in-memory OLTP, which suits latency-sensitive workloads, but remediation must follow the alert's findings first.
✗
Scale up to Standard S3 (100 DTU)
Why it's wrong here
Scaling to Standard S3 adds DTUs but does not resolve the specific regression Intelligent Insights has detected, such as a plan change or blocking chain. It is tempting because DTU exhaustion commonly causes performance degradation, making a tier bump the correct response when the alert attributes the issue to resource saturation rather than a query-level fault.
✓
Implement automatic tuning recommendations
Why this is correct
Intelligent Insights identifies regressions caused by query plan changes, and automatic tuning can apply the corrective plan or index fix without manual intervention. Implementing recommendations first restores performance directly, satisfying the requirement to resolve the degradation before deeper investigation.

Visual reference

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