I85PREP
⌘ K
Azure DP-300 study materials
Azure DP-300 study resources

Azure DP-300: Monitor, Configure, and Optimize Database Resources

Azure DP-300 · Work through your exam, one topic at a time.

All guide sections

Monitor, Configure, and Optimize Database Resources

Where the numbers come from

  • Query Store records query text, plans, runtime statistics, and waits. It is on by default for Azure SQL and is a prerequisite for Automatic Tuning (CREATE INDEX, DROP INDEX, FORCE LAST GOOD PLAN) and automatic plan correction.
  • DMVs: sys.dm_db_resource_stats (roughly 15-second samples for the last hour), sys.dm_user_db_resource_governance (your tier's caps), sys.dm_exec_requests and blocking chains, sys.dm_db_wait_stats.
  • Azure Monitor: platform metrics (CPU percent, DTU/vCore percent, log write percent, storage), diagnostic settings to Log Analytics, and metric alerts for proactive paging.

Reading the signal

Identify the dominant wait type first. High CPU with SOS_SCHEDULER_YIELD or PAGEIOCP points at compute; PAGEIOLATCH_* points at storage; LCK_* points at blocking; RESOURCE_SEMAPHORE points at memory grants. On Azure SQL, throttling waits such as log-rate limits usually mean you have hit the service tier ceiling, not that the query is wrong.

Traps

  • DMV counters reset on service restart and on failover; capture a baseline before diagnosing.
  • Query Store set to READ_ONLY silently stops collecting, so Automatic Tuning quietly does nothing.
  • Index maintenance thresholds: reorganize between 5 and 30 percent fragmentation, rebuild above 30 percent. Rebuilding tiny tables nightly is wasted work.

Rule of thumb

Pinned CPU plus scheduler waits → scale up or fix the top query. Storage waits with a modest data file → check the tier's IOPS and log-throughput limits before rewriting code.