Study guide
8.2 Interpreting Metrics and Configuring Activity Monitoring
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.2 Interpreting Metrics and Configuring Activity Monitoring
Key Takeaways Explain This Topic AI
Azure Monitor alert rules evaluate platform metrics (or Log Analytics queries) and fire through action groups that deliver email/SMS/webhook/Push/Azure Function notifications - the action group is the reusable delivery layer, the rule is the condition
On DTU databases, DTU% is a blended metric that hits 100 when any one of CPU, data IO, or log write reaches its limit, so a DTU spike does not tell you which dimension throttled - drill into avg_cpu_percent, avg_data_io_percent, and avg_log_write_percent
Diagnostic settings stream Azure SQL logs (SQLSecurityAuditEvents, Errors, TimeoutEvents, Blocks, Waits, QueryStore runtime stats) to up to three destinations: Log Analytics workspace, storage account, or Event Hub - configure them per database or per server
For active session investigation, sys.dm_exec_requests shows currently executing requests with their wait type and wait time; sys.dm_exec_sessions shows all open sessions; combining with sys.dm_exec_sql_text and sys.dm_exec_plan_attributes reveals what each session is doing
sys.dm_os_wait_stats accumulates wait time by wait type since the last engine restart or DBCC SQLPERF reset; analyze the ratio of waiting time to signal wait time and the top wait types to classify whether the bottleneck is IO, locking, or CPU
Last updated: August 2026
Interpreting the Core Metrics
Each Azure SQL metric has a specific meaning, and the exam tests the common misreadings.
Metric What it means Common misreading
cpu_percent CPU utilization as a percentage of the database's (or instance's) compute budget Treating 100% as a hardware limit; it is the limit of the configured service objective
dtu_consumption_percent Blended CPU + data IO + log write, as a percentage of the DTU limit (DTU model only) Assuming a DTU spike means CPU is high; any one dimension can drive it to 100%
physical_data_read_percent Data file reads (IOPS/throughput) as a percentage of the tier's data IO limit Confusing it with log writes; it is read I/O, not write I/O
log_write_percent Log file write throughput as a percentage of the tier's log write limit Interpreting 100% as disk full; it is throughput throttling, not space
deadlocks Cumulative count of deadlocks since the metric window started Treating it as an instantaneous value; it is a counter
connection_failed Count of failed connection attempts in the window Assuming it is a server outage; can be app-side retry storms, firewall, or auth
The DTU versus CPU distinction is the most heavily tested. On a DTU-database, dtu_consumption_percent is a blended measure: the DTU limit is the smaller of CPU, data IO, and log write headroom, so a database can hit 100% DTU without being CPU-bound. To find the actual bottleneck, break DTU% apart by reading avg_cpu_percent , avg_data_io_percent , and avg_log_write_percent from sys.resource_stats or sys.dm_db_resource_stats . On a vCore database there is no DTU metric; use avg_cpu_percent and the storage metrics directly.
Log_write_percent at 100% indicates the transaction log throughput limit for the service objective is saturated - the database is producing log faster than the tier allows. The fix is either to reduce the log-generating workload (batch, tune, reduce concurrent writes) or to scale up to a tier with a higher log throughput cap. It is never solved by adding data-file storage.
Alert Rules and Action Groups
An alert rule in Azure Monitor is the condition: it watches a metric (or a log query) and fires when the condition is met. An action group is the reusable delivery layer that defines who and how - email, SMS, voice, webhook, Azure Function, Logic App, secure webhook, or push notification. You create the action group once and attach it to many rules.
Two alert types matter for Azure SQL:
Metric alerts evaluate platform metrics with static thresholds (for example, cpu_percent > 80 for 5 minutes ) or dynamic thresholds (machine-learned baselines that adapt automatically). Metric alerts are near-real-time (1-minute evaluation) and do not require diagnostic settings.
Log alerts run a KQL query against a Log Analytics workspace on a schedule and fire when the query returns results. They require that diagnostic settings stream the relevant logs to that workspace first.
For example, a static metric alert on deadlocks > 0 over the last 5 minutes, attached to an action group that pages the on-call DBA, is the standard way to catch deadlocks as they happen. A log alert on a KQL query that returns blocked processes from the AzureDiagnostics table (where Category == 'Blocks' ) catches sustained blocking.
Diagnostic Settings
Azure SQL logs and metrics are not retained in depth until you configure diagnostic settings . Each diagnostic setting sends logs and/or metrics to up to three destinations in combination:
Log Analytics workspace - queryable with KQL, powers Azure SQL Analytics and custom workbooks.
Azure Storage account - long-term archival in JSON blobs with time-based naming; useful for compliance retention beyond the 93-day metric window.
Event Hub - streaming destination for SIEM tools (Microsoft Sentinel, Splunk) or custom real-time processors.
You can configure up to five diagnostic settings per resource, and each resource supports a diagnostic setting at the server/instance level (which captures instance-level events) and/or at the database level . The logs worth streaming for performance monitoring include SQLSecurityAuditEvents , Errors , TimeoutEvents , Blocks , Waits , QueryStoreRuntimeStatistics , QueryStoreWaitStatistics , and SQLInsights .
Fleet Dashboards: Legacy Solutions vs Database Watcher
Two older Azure Monitor solutions aggregated diagnostic logs across many Azure SQL resources, and the exam expects you to know their current status rather than to deploy them:
Azure SQL Analytics (preview) is a Log Analytics solution that ingests diagnostic logs and renders prebuilt workbooks (performance overviews, wait statistics, timeouts, blocks, query statistics, errors). Microsoft states this and similar monitoring solutions are no longer in active development .
SQL Insights (preview) was a separate Azure Monitor experience that collected from targets through a monitoring VM. It was retired on 31 December 2024 and is no longer supported.
Both depended on diagnostic settings or an agent being configured correctly per target; without that, the dashboards were empty. For new fleet-scale monitoring of Azure SQL Database and Azure SQL Managed Instance, Microsoft's recommendation is database watcher (Section 8.3).
One naming trap worth memorizing: the resource log category named SQLInsights is Intelligent Insights (Section 9.4) and has nothing to do with the retired SQL Insights solution.
Session and Activity Monitoring
When investigating a live incident, the request and session DMVs are the primary tools. They are in-memory, reset on engine restart, and reflect current activity only. -- What is running right now, with wait info and the SQL text
- SELECT r.session_id, r.status, r.wait_type, r.wait_time,
- r.wait_resource, r.cpu_time, r.logical_reads,
- t.text AS query_text,
- DB_NAME(r.database_id) AS db_name,
- s.host_name, s.program_name, s.login_name
- FROM sys.dm_exec_requests r
- JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id
- CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
- ORDER BY r.cpu_time DESC;
sys.dm_exec_requests returns one row per executing request, including its wait_type , wait_time , and wait_resource - enough to identify the blocker and the blocked. sys.dm_exec_sessions gives context: host, program, login, and when the session started. sys.dm_exec_sql_text resolves the sql_handle to readable text; sys.dm_exec_query_plan resolves the plan_handle to the execution plan XML.
The community stored procedure sp_whoisactive (by Adam Machanic) is widely used as a human-friendly wrapper around these DMVs - it joins requests, sessions, and text into a single output with blocking chains and wait descriptions. The exam mentions it by name as a legitimate activity-monitoring tool; it is not Microsoft-built, but it is a standard industry practice and does not require installation into system databases.
Wait Statistics Interpretation
sys.dm_os_wait_stats accumulates wait time by wait type since the SQL Server service last started (or since DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR) ). It has four key columns: waiting_tasks_count , wait_time_ms , max_wait_time_ms , and signal_wait_time_ms .
Interpretation rules:
signal_wait_time_ms is the time a waiter spent on the runnable queue waiting for a CPU - high signal waits indicate CPU pressure, not resource waits.
wait_time_ms - signal_wait_time_ms is the resource wait: time spent waiting for the resource (IO, lock, latch).
The ratio of signal_wait_time_ms to wait_time_ms is a CPU-pressure indicator; if signal wait is a large fraction of total wait, add CPU or reduce concurrent queries rather than chase IO.
Common wait types and their meaning:
- Wait type Indicates
- PAGEIOLATCH_* Waiting for a data page read from disk to memory - IO bottleneck
- WRITELOG Waiting for transaction log flush - log IO bottleneck
- LCK_* (LCK_M_X, LCK_M_S, etc.) Lock contention - blocking
- PAGELATCH_* In-memory page latch contention, often tempdb or hot-page inserts
- SOS_SCHEDULER_YIELD CPU yielded and waiting for scheduler - CPU pressure
- CXPACKET Parallelism wait - often too many threads; review MAXDOP
- Correlating Metrics to Incidents
The exam scenario pattern: a user reports slowness at 09:15. You pull sys.dm_db_resource_stats for 09:00-09:30, see a log_write_percent spike to 100%. You pull sys.dm_os_wait_stats and see WRITELOG dominate. You open Query Store and find a new query that started executing at 09:12 doing large bulk inserts. The chain: log throughput throttling (metric) -> log flush waits (wait stats) -> specific query (Query Store) -> root cause. That chain - metric, wait, query - is the structure the exam rewards.