8.1 Operational Baselines and Performance Metric Sources

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:

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.