DP-300 · Domain 3 of 5

DP-300 Domain 3: Monitor, configure, and optimize database resources

Finding and fixing performance problems in Azure SQL: building baselines, reading metrics, using database watcher, Extended Events, Query Store and DMVs, resolving blocking, maintaining indexes and statistics, and configuring automatic tuning and server settings.

Exam weight
20–25%
Official functional groups
3
Estimated questions estimate
~11
Suggested study time estimate
14–18 hrs

About 11 min read Reviewed October 2026

Overview

Domain 3 carries 20–25% of DP-300 and is the most hands-on area. Microsoft lists three functional groups. Monitoring resource activity covers operational baselines, sources and interpretation of performance metrics, configuring activity monitoring, database watcher and Extended Events.

Query performance covers configuring and using Query Store, resolving session blocking, using DMVs, index changes, query construct changes, execution plans and Intelligent Insights. The optimization group covers index and statistics maintenance, integrity checks, automatic tuning, server settings, Resource Governor, database-scoped configuration, compute and storage scaling, and use cases for intelligent query processing. The common thread is diagnosis: find whether the bottleneck is CPU, IO, memory, blocking or a plan regression, then apply the narrowest fix.

How it's tested

Expect symptoms such as rising CPU after a deployment, timeouts at month end, or sessions waiting on locks, along with metrics or DMV output. You decide which tool reveals the cause and which change fixes it with the least disruption, often choosing between Query Store, an index change, a setting or scaling.

  • Regression after change

    A query slowed after a deployment or statistics update, pointing to Query Store plan forcing or automatic tuning.

  • Who is blocking whom

    Lock waits and timeouts lead to DMVs such as sys.dm_exec_requests to find the head blocker and its transaction.

  • Fix or scale

    High resource use is judged against plan quality, indexes and waits before deciding that more vCores are justified.

Reasoning it through

CPU on an Azure SQL database jumped from 40% to 95% after a release, and one stored procedure dominates. Query Store shows two plans for it, and the new one scans a large index. Scaling up would mask the problem at extra cost. Forcing the previous good plan in Query Store, or letting automatic tuning do so, restores performance immediately while the team fixes the parameter-sensitive query properly.

Functional groups 3

Official functional groups from the Microsoft outline, in outline order.

3.1 Monitor resource activity and performanceBaselines, metric sources and interpretation, activity monitoring, database watcher and Extended Events.
What it means
This group covers preparing an operational performance baseline, determining sources for performance metrics, interpreting them, configuring and monitoring activity and performance, monitoring with database watcher, and monitoring with Extended Events.
What to understand
A baseline records normal CPU, data IO, log IO, memory, waits and workload so anomalies stand out. Sources include Azure Monitor metrics, diagnostic settings sent to Log Analytics, sys.dm_db_resource_stats for about an hour at 15-second granularity, sys.resource_stats in master for about 14 days, and wait statistics DMVs. Database watcher is a managed agentless service that collects detailed monitoring data from Azure SQL Database and Managed Instance into an Azure Data Explorer or Fabric Real-Time Intelligence store with dashboards. Extended Events sessions capture specific events with low overhead, writing to a ring buffer or event files in Azure Blob Storage.
How it shows up
Expect a question on where to look for a given metric, how long data is kept, or which tool captures specific query events.
Common confusion
Expecting sys.dm_db_resource_stats to show last week's history; it holds roughly one hour.
3.2 Monitor and optimize query performanceQuery Store, blocking, DMVs, indexes, query rewrites, execution plans and Intelligent Insights.
What it means
This group covers configuring and monitoring with Query Store, identifying and resolving session blocking, identifying issues through DMVs, identifying and implementing index changes, recommending query construct changes from resource usage, reviewing execution plans, and monitoring with Intelligent Insights.
What to understand
Query Store persists query text, plans and runtime statistics, with settings for operation mode, capture mode, size and cleanup. Its reports show regressed queries and allow plan forcing or query hints. Blocking is traced with sys.dm_exec_requests, sys.dm_tran_locks and blocking_session_id; Azure SQL Database uses read committed snapshot isolation by default, which reduces reader-writer blocking. Missing index DMVs suggest indexes but need judgment. Execution plans reveal scans, key lookups, implicit conversions and spills. Query rewrites such as making predicates SARGable cut resource use. Intelligent Insights analyzes telemetry and writes a diagnostic log describing detected performance issues.
How it shows up
Expect DMV output or a plan fragment and a choice of the change that removes the bottleneck.
Common confusion
Creating every index the missing index DMV suggests; overlapping indexes slow writes and waste storage.
3.3 Configure database solutions for optimal performanceIndex and statistics maintenance, integrity checks, automatic tuning, server settings, Resource Governor, scoped configuration, scaling and IQP.
What it means
This group covers implementing index maintenance, statistics maintenance and database integrity checks, configuring automatic tuning, server settings and Resource Governor, implementing database-scoped configuration, configuring compute and storage for scaling, and identifying use cases for intelligent query processing features.
What to understand
Reorganize lightly fragmented indexes and rebuild heavily fragmented ones, using resumable online rebuilds where supported. Update statistics after large data changes. Run DBCC CHECKDB on VMs and Managed Instance on a schedule. Automatic tuning offers FORCE_LAST_GOOD_PLAN, plus CREATE_INDEX and DROP_INDEX in Azure SQL Database. Server settings such as max server memory and cost threshold for parallelism apply on VMs. Resource Governor limits resource pools on VMs and Managed Instance. Database-scoped configuration sets MAXDOP and parameter sniffing per database. Intelligent query processing features, such as memory grant feedback and parameter sensitive plan optimization, depend on compatibility level.
How it shows up
Expect a need to cap a reporting workload, tune MAXDOP for one database, or enable a feature tied to compatibility level.
Common confusion
Trying to set instance-level options such as max server memory in Azure SQL Database, where they are managed.

High-yield concepts

The terms, rules and formulas this domain turns into questions most often.

  • Performance baseline

    Normal ranges for key metrics over time.

    Remember
    Needed to recognize anomalies.
    Tested as
    Monitoring design questions.
    Trap
    Collecting data only after an incident.

  • sys.dm_db_resource_stats

    Per-database resource use every 15 seconds for about an hour.

    Remember
    Short-term diagnosis.
    Tested as
    Metric source questions.
    Trap
    Expecting long history.

  • Database watcher

    Managed monitoring that stores detailed SQL telemetry in a data store with dashboards.

    Remember
    No agents to install.
    Tested as
    Monitoring tool questions.
    Trap
    Assuming it supports SQL Server on VMs.

  • Extended Events

    Lightweight event capture sessions.

    Remember
    Use event_file targets in Blob Storage for Azure SQL Database.
    Tested as
    Tracing questions.
    Trap
    Choosing SQL Profiler-style tracing.

  • Query Store

    Stores plans and runtime stats per query.

    Remember
    Supports plan forcing and hints.
    Tested as
    Regression questions.
    Trap
    Leaving it read-only when capture is needed.

  • Blocking chain

    Sessions waiting on locks held by a head blocker.

    Remember
    Find the head blocker with blocking_session_id.
    Tested as
    Timeout scenarios.
    Trap
    Killing the waiting session, not the blocker.

  • Read committed snapshot

    Row versioning so readers do not block writers.

    Remember
    Default in Azure SQL Database.
    Tested as
    Blocking questions.
    Trap
    Assuming it is off by default.

  • Execution plan

    Operators and costs a query uses.

    Remember
    Look for scans, lookups, spills and conversions.
    Tested as
    Tuning questions.
    Trap
    Judging only by estimated cost.

  • Automatic tuning

    Plan correction and index management by the platform.

    Remember
    FORCE_LAST_GOOD_PLAN is on by default.
    Tested as
    Self-tuning questions.
    Trap
    Expecting index options on Managed Instance.

  • Index maintenance

    Reorganize or rebuild based on fragmentation.

    Remember
    Resumable rebuilds limit disruption.
    Tested as
    Maintenance questions.
    Trap
    Rebuilding everything nightly.

  • Resource Governor

    Limits CPU, memory and IO per workload group.

    Remember
    Available on VMs and Managed Instance.
    Tested as
    Workload isolation questions.
    Trap
    Expecting it in Azure SQL Database.

  • Database-scoped configuration

    Per-database settings like MAXDOP.

    Remember
    Overrides server defaults for one database.
    Tested as
    Configuration questions.
    Trap
    Changing server settings when one database needs it.

  • Intelligent query processing

    Optimizer features that adapt plans automatically.

    Remember
    Many require recent compatibility levels.
    Tested as
    Upgrade questions.
    Trap
    Expecting them at old compatibility levels.

Side by side

Concepts candidates mix up, compared on the point that decides the answer.

Monitoring sources

ConceptMeaningKey differenceCommon trap
Azure Monitor metrics Platform metrics such as CPU and storage Quick charts and alerts Expecting query-level detail
Query Store Per-query plans and runtime stats Persistent query history Using it for server-wide waits only
Extended Events Targeted event capture Fine-grained tracing Leaving broad sessions running
Database watcher Managed collection with dashboards Fleet-wide detailed telemetry Expecting coverage of SQL on VMs

Index maintenance actions

ConceptMeaningKey differenceCommon trap
Reorganize Compacts and reorders leaf pages Always online, lighter Using it on heavily fragmented large indexes
Rebuild Recreates the index and updates statistics Heavier, can be online and resumable Running it without considering log growth

Exam traps

Each trap is an answer that looks right. Learn the clue that should change your mind.

  1. Scale first

    What candidates pick
    Increase vCores when CPU jumps after a deployment.
    Why it looks right
    It works quickly.
    Why it's wrong
    A plan regression continues to waste resources; forcing a good plan fixes the cause.

    Clue to watch for Problem started right after a release or statistics change.

  2. Kill the waiting session

    What candidates pick
    End the session showing the longest wait.
    Why it looks right
    It looks like the stuck one.
    Why it's wrong
    The head blocker is the session holding locks; ending waiters does not clear the chain.

    Clue to watch for Output shows blocking_session_id values.

  3. Profiler in Azure SQL

    What candidates pick
    Use SQL Server Profiler traces in Azure SQL Database.
    Why it looks right
    It is a familiar tool.
    Why it's wrong
    Extended Events is the supported lightweight tracing mechanism.

    Clue to watch for A low overhead capture requirement.

  4. Resource Governor in SQL Database

    What candidates pick
    Configure Resource Governor on a single Azure SQL database.
    Why it looks right
    It caps workloads elsewhere.
    Why it's wrong
    Customers cannot configure Resource Governor in Azure SQL Database; isolate with separate databases or pools.

    Clue to watch for The target is Azure SQL Database.

  5. Automatic index for Managed Instance

    What candidates pick
    Enable CREATE_INDEX automatic tuning on Managed Instance.
    Why it looks right
    Automatic tuning exists there.
    Why it's wrong
    Managed Instance supports plan correction only; index options are Azure SQL Database features.

    Clue to watch for Managed Instance named as the platform.

Answer framework

Work through these steps when a DP-300 scenario describes a performance problem in Azure SQL.

  1. Compare current metrics with the baseline to classify the bottleneck as CPU, data IO, log IO, memory or waits, using a time window that matches the reported slowdown.
  2. Find the top consumers in Query Store or DMVs and check whether a plan changed recently, for example after a release, statistics update or compatibility level change.
  3. For blocking, locate the head blocker, its transaction and isolation level before taking action.
  4. Apply the narrowest fix: force a plan, change an index, rewrite a predicate, update statistics or adjust a scoped setting.
  5. Scale compute or storage only when the workload is efficient and still constrained, then record a new baseline so the next investigation starts from current normal values.

Practice question

After a weekly statistics update, a frequently run query on Azure SQL Database became much slower. Query Store shows the query now uses a new plan with higher duration, while the previous plan performed well. You need the fastest fix with no code change. What should you do?

Want more like this? The DP-300 practice test has questions for every domain.

Checklist

0 of 13

Tick each one you can do without notes. Your ticks stay in this browser.

Study strategy

Treat this domain as a lab course. Load a sample database such as AdventureWorksLT into Azure SQL Database and generate load with a simple script. Watch CPU and IO in Azure Monitor, then query sys.dm_db_resource_stats and sys.resource_stats so you remember their granularity and retention.

Next, open the Query Store reports, find the top resource consumers and force a plan, then unforce it. Create two sessions that block each other and resolve the chain with DMVs. Capture a deadlock or long query with an Extended Events session that writes to Blob Storage. Read a few execution plans and practice spotting key lookups and implicit conversions.

Finally, map configuration features to platforms: which work in Azure SQL Database, which need Managed Instance, and which are VM only. Resource Governor, server settings, automatic index tuning and DBCC scheduling all appear as platform traps. Review database watcher and intelligent query processing documentation last, since they are newer names candidates often skip.

FAQ

How much of DP-300 is Domain 3, Monitor, configure, and optimize database resources?

Microsoft weights it at 20–25% of the exam, across three functional groups: monitoring resource activity, monitoring and optimizing query performance, and configuring databases for optimal performance.

Is Query Store enabled by default?

Yes in Azure SQL Database and Managed Instance. On SQL Server 2022 it is on by default for new databases; older versions require enabling it.

What is database watcher?

A managed monitoring service for Azure SQL that collects detailed performance data without agents and stores it in Azure Data Explorer or Fabric Real-Time Intelligence.

Which automatic tuning options exist?

FORCE_LAST_GOOD_PLAN on Azure SQL Database and Managed Instance, plus CREATE_INDEX and DROP_INDEX on Azure SQL Database only.

How do I find what is blocking a query?

Query sys.dm_exec_requests for blocking_session_id, follow the chain to the head blocker, and inspect its open transaction and locks.

Do I need to know execution plan operators?

Yes, at a practical level: scans versus seeks, key lookups, sorts, spills, parallelism and warnings such as implicit conversions.

Other Data & Analytics certifications

Where the same subject shows up in other exams. Cards marked "Same subject" open the matching domain.

Test your Domain 3 knowledge

You have covered baselines, monitoring tools, Query Store, blocking, maintenance and tuning settings. Test whether you can find the bottleneck and choose the narrowest fix.

Take the DP-300 practice test Check your DP-300 readiness

Keep going