Study guide

8.1 Operational Baselines and Performance Metric Sources

DP-300: Administering Microsoft Azure SQL Solutions Exam

Track your progress as you read

Finished sections get a check mark.

1 Introduction & Exam Overview

1.1 Current DP-300 Exam Facts 1.2 Skills Outline and Domain Weights 1.3 Study Strategy and Official Resources

2 Planning and Deploying Azure SQL Solutions

2.1 Choosing and Deploying Azure SQL Database Offerings 2.2 Automated Deployment, Azure Arc, and SQL Database in Microsoft Fabric 2.3 Table Partitioning and Sharding Strategy 2.4 Hybrid SQL Server and IaaS Patching

3 Configuring Resources for Scale and Performance

3.1 Configuring Azure SQL Database for Scale and Performance 3.2 Configuring Azure SQL Managed Instance for Scale and Performance 3.3 Configuring SQL Server on Azure VMs for Scale and Performance 3.4 Configuring Table Partitioning and Data Compression

4 Planning and Implementing a Migration Strategy

4.1 Evaluating Migration Requirements and Strategies 4.2 Implementing Online, Offline, and Azure Migrations 4.3 Migrating Between Azure SQL Services and Troubleshooting

5 Configuring Database Authentication and Authorization

5.1 Microsoft Entra ID Authentication for Azure SQL 5.2 Authentication for SQL on Azure VMs and Managed Instance; Troubleshooting 5.3 Security Principals and Entra-Backed Users 5.4 Permissions, Least Privilege, and T-SQL Management

6 Implementing Security for Data at Rest and in Transit

6.1 Transparent Data Encryption and Object-Level Encryption 6.2 Always Encrypted and VBS Enclaves 6.3 Firewall Rules, Private Links, and Service Endpoints

7 Implementing Compliance Controls for Sensitive Data

7.1 Data Classification and Server/Database Auditing 7.2 Change Data Tracking and Dynamic Data Masking 7.3 Row-Level Security and Ledger in Azure SQL

8 Monitoring Resource Activity and Performance

8.1 Operational Baselines and Performance Metric Sources 8.2 Interpreting Metrics and Configuring Activity Monitoring 8.3 Monitoring with Database Watcher (Preview) 8.4 Monitoring with Extended Events

9 Monitoring and Optimizing Query Performance

9.1 Configuring and Monitoring with Query Store 9.2 Session Blocking and Dynamic Management Views 9.3 Index Changes and Execution Plan Review 9.4 Query Construct Modifications and Intelligent Insights

10 Configuring Database Solutions for Optimal Performance

10.1 Index, Statistics, and Integrity Maintenance 10.2 Automatic Tuning and Database-Scoped Configuration 10.3 Server Settings and Resource Governor 10.4 Compute/Storage Scaling and Intelligent Query Processing

11 Creating and Managing SQL Server Agent Jobs

11.1 Schedules for Regular Maintenance Jobs 11.2 Job Alerts, Notifications, and Agent Troubleshooting 11.3 Automating Deployment with ARM and Bicep Templates

12 Automating Deployments and Database Tasks in Azure

12.1 Azure PowerShell, Azure CLI, and Deployment Monitoring 12.2 Elastic Jobs for Cross-Database Automation 12.3 Azure Automation Runbooks, Alerts, and Troubleshooting Automated Tasks

13 Planning an HA/DR Strategy and Backup and Restore

13.1 Recommending HA/DR Strategy from RPO and RTO 13.2 Hybrid and Azure-Specific HA/DR Solutions and Testing 13.3 Backup and Restore Strategy with Native Tools 13.4 Point-in-Time Restore and Recovery Operations

14 Configuring HA/DR: Long-Term Retention, Geo-Replication, and Failover Groups

14.1 Long-Term Retention, T-SQL Backup/Restore, and Cloud Storage 14.2 Active Geo-Replication 14.3 Failover Groups

15 Advanced HA/DR: Availability Groups, FCIs, Log Shipping, and Troubleshooting

15.1 Always On Availability Groups on SQL Managed Instance and Azure VMs 15.2 Always On Failover Cluster Instances and Log Shipping 15.3 Monitoring and Troubleshooting HA/DR Solutions

8.1 Operational Baselines and Performance Metric Sources

Key Takeaways Explain This Topic AI

An operational performance baseline captures typical CPU, log IO, data IO, wait statistics, DTU/vCore usage, and tempdb activity under normal load so anomalous behavior can be distinguished from ordinary peaks

Azure SQL Database exposes resource usage through sys.resource_stats (15-minute aggregates, up to 92 days) and sys.dm_db_resource_stats (5-10 second granularity, ~1 hour); SQL MI uses sys.server_resource_stats; always query the finest source that fits the window you investigate

DTU% blends CPU, data IO, and log write into one number for DTU databases; vCore databases expose avg_cpu_percent and storage usage separately, so the right metric to alert on depends on the purchasing model

Query Store is a first-class baseline source: it tracks query text, plans, runtime stats, and resource consumption over time and survives restarts, making it the recommended long-term repository for query-level baselining

Baselining before tuning is mandatory because every later measurement (after an index change, scaling, or config change) is only meaningful when compared against the pre-change normal behavior you recorded

Last updated: August 2026

Why Baselining Comes Before Tuning

A performance baseline is a recorded snapshot of how your database behaves under normal conditions: how much CPU it uses, how fast it reads and writes, what the dominant wait statistics are, and how much of its provisioned resource budget it consumes. The reason it must come before tuning is simple: every optimization decision is comparative. If you change an index and CPU drops from 70% to 50%, you can only call that an improvement if you know that 70% was the normal before-state. Without a baseline you cannot distinguish a fix from normal variation, and you cannot tell whether a complaint represents a genuine anomaly or simply a busy Monday morning.

On the DP-300 exam the baselining objective is almost always phrased as a scenario: a user reports slowness, and the right first answer is capture a baseline before changing anything rather than scale up vCores immediately . Scaling up before measuring is a trap - it masks the root cause and the spend increase becomes permanent.

What a Baseline Captures

A complete baseline records several dimensions simultaneously, because resource symptoms interact:

  • Dimension What to record Typical source
  • CPU avg_cpu_percent (Azure SQL DB), processor_time (SQL VM) sys.dm_db_resource_stats, Azure Monitor
  • Log IO Log write throughput (MB/s) and log write % sys.dm_db_resource_stats, sys.resource_stats
  • Data IO Data read/write IOPS and MB/s, data IO % sys.dm_db_resource_stats, Azure Monitor
  • Waits Top wait types and wait time per wait sys.dm_os_wait_stats, Query Store waits
  • Resource budget DTU% (DTU model) or vCore % / storage % (vCore model) sys.resource_stats
  • tempdb Tempdb file size, growth events, contention sys.dm_db_file_space_usage, Azure Monitor

A baseline should be captured over a representative window - typically at least one full business cycle, including peak and off-peak hours. A 15-minute grab is a spot measurement, not a baseline.

Resource DMVs by Deployment Model

Azure SQL exposes resource usage through DMVs whose names and granularity depend on the deployment target. Knowing which to query for which target is a heavily tested mapping.

Azure SQL Database : sys.resource_stats lives in the master database of the logical server and returns one row every 15 minutes per database, retained up to 92 days . It reports avg_cpu_percent, avg_data_io_percent, avg_log_write_percent, and (for DTU databases) avg_instance_cpu_percent and dtu_limit. Use it for longer-range trend analysis. sys.dm_db_resource_stats lives in the user database and returns one row every 5-10 seconds , retained for approximately one hour; use it for near-real-time investigation. Coarser data over longer windows, finer data over short windows.

Azure SQL Managed Instance : the analogous view is sys.server_resource_stats , queried from the master database, returning 15-minute aggregates of instance-level CPU, IO, and storage across all databases on the instance.

SQL Server on Azure VMs : the engine reports nothing to Azure automatically; you collect from the in-engine DMVs ( sys.dm_os_performance_counters , sys.dm_os_wait_stats , sys.dm_db_resource_stats is not available) and forward to Azure Monitor via the SQL IaaS Agent extension, diagnostic settings, or Log Analytics agents. -- Recent 15-minute aggregates for one Azure SQL Database

  • SELECT start_time, end_time, avg_cpu_percent, avg_data_io_percent,
  • avg_log_write_percent, dtu_limit, storage_used_mb
  • FROM sys.resource_stats
  • WHERE database_name = 'SalesDB'
  • ORDER BY start_time DESC; -- Fine-grained (5-10s) recent resource usage inside the user database
  • SELECT end_time, avg_cpu_percent, avg_data_io_percent,
  • avg_log_write_percent, avg_memory_usage_percent
  • FROM sys.dm_db_resource_stats
  • ORDER BY end_time DESC;
  • Query Store as a Baseline Repository

Query Store , enabled by default on new Azure SQL databases, is the engine's built-in flight recorder. It captures query text, execution plans, compile-time and runtime statistics, and resource consumption (CPU, logical reads, physical reads, memory, row counts) per plan over time, persisting them in the database itself so they survive restarts and failovers. For baselining purposes Query Store is the recommended long-term source for query-level behavior because it ties resource use back to the specific query and plan, something the resource DMVs do not do. Configure the data retention to match your baseline window (for example, 30, 60, or 90 days), and set the capture mode to AUTO so infrequent queries are not dropped.

The contrast the exam tests: sys.dm_db_resource_stats tells you the database is using 80% CPU; Query Store tells you which queries consumed that CPU and when. For root-cause analysis you almost always need both.

Azure Monitor Metrics and Retention

Azure Monitor platform metrics are collected automatically for every Azure SQL resource at no cost and without configuration. They include cpu_percent , storage_percent , physical_data_read_percent , log_write_percent , dtu_consumption_percent , deadlocks , connection_successful , and connection_failed . Metrics are retained for 93 days at 1-minute granularity by default (longer with diagnostics configured). Azure Monitor metrics are what underlie the portal charts and what alert rules evaluate. Because they are pre-aggregated, they are ideal for dashboards and alerts but less ideal for root-cause investigation, where you drop to DMVs or Query Store.

Distinguishing Normal from Anomalous

An anomaly is a deviation from the baseline large enough to matter. Operational rules for classifying:

A value within the baseline's 95th percentile during the same hour of the same weekday is normal - do not alert on it.

A value above the baseline's max for that weekday/hour, or sustained above the 95th percentile for more than a few minutes, is anomalous and worth investigating.

A wait type that is new (not present in the baseline at all) is always significant, because it implies a new workload or a new contention pattern.

Correlate across sources before concluding: a CPU spike that aligns with a known batch job in Query Store is expected; a CPU spike with a matching spike in connection_failed and a new PAGEIOLATCH_* wait is a genuine problem. The exam pattern is: report slowness -> capture baseline if you do not have one -> compare current metrics against it -> correlate waits and resource DMVs with Query Store -> only then act.