8.2 Interpreting Metrics and Configuring Activity Monitoring

Interpreting the Core Metrics

Each Azure SQL metric has a specific meaning, and the exam tests the common misreadings.

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:

1. Log Analytics workspace - queryable with KQL, powers Azure SQL Analytics and custom workbooks.
2. Azure Storage account - long-term archival in JSON blobs with time-based naming; useful for compliance retention beyond the 93-day metric window.
3. 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:

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.