Domain 3 of 5 · Chapter 1 of 3

Monitor resource activity and performance

Scope, denominator, and time window of a performance signal

A support ticket says an application could not reach its Azure SQL database at 09:14, yet the Availability chart for that minute reads 100 percent. Both statements can be true, because the Availability metric counts a minute as available when at least one connection succeeds, and it also counts failures caused by user errors, such as a connection blocked by a firewall rule, as available[1]. The chart answered a narrower question than the ticket asks; Interpreting connection and availability metrics gives the complete rule.

Three properties of a signal limit what its value can tell you:

The tasks here are the ones the Microsoft DP-300 study guide[5] lists under Monitor resource activity and performance, from 'Prepare an operational performance baseline' through 'Monitor by using Extended Events'. Finding which queries create the pressure is a different task: the Microsoft DP-300 study guide[5] lists 'Monitor by using Query Store' and 'Identify performance issues using dynamic management views (DMVs)'; see Monitor and optimize query performance. Changing a setting once the evidence points at a cause is another: the study guide[5] lists 'Configure server settings for performance' and 'Configure compute and storage resources for scaling'; see Configure database solutions for optimal performance. Sizing a deployment is a third: the study guide[5] lists 'Configure SQL Server on Azure Virtual Machines for scale and performance'; see Configure resources for scale and performance.

Before reading any value, name its scope, its denominator, and its time window.

Building an operational performance baseline

A baseline answers the question every threshold and tuning decision depends on: compared with what? Measurements taken at regular intervals, even when no problems occur, are how you determine whether a SQL Server system is performing optimally[6].

The same guidance[6] is to compare each new set of measurements with earlier ones. At a minimum, the measurements should establish:

  • peak and off-peak hours of operation;
  • production query or batch command response times;
  • database backup and restore completion times.

Numbers far above or far below the baseline are candidates for further investigation[6]: if a set of queries starts taking longer, for example, examine whether the queries can be rewritten or whether column statistics or new indexes are needed. Compare like with like, so a Monday-morning peak is judged against earlier Monday-morning peaks rather than against a quiet weekend.

Response time and throughput

Two measurements describe different aspects of service. Response time is the time required for the first row of the result set to return to the user, and throughput is the total number of queries the server handles during a specified period[7]. The same page notes that as users increase, competition for resources increases response time and decreases overall throughput. Record both beside user concurrency: a change that completes more batches per hour has not thereby made an interactive user's request return sooner, so judge an interactive workload against its response-time requirement under comparable load.

A starting point from Azure SQL Database history

In Azure SQL Database, sys.resource_stats in the logical server's master database keeps 5-minute resource aggregates for about 14 days, and its average values usually give a good baseline to compare with a target compute size[4]. The listing below is Microsoft's query from that page; it reports one database's average and maximum resource use over the past seven days, grouped by sku, the database's service tier[8], so periods spent at different tiers stay separate.

Seven-day resource summary for one database (run while connected to master)

SELECT rs.database_name,
       rs.sku,
       MAX(rs.storage_in_megabytes) AS storage_mb,
       AVG(rs.avg_cpu_percent) AS 'Average CPU Utilization In %',
       MAX(rs.avg_cpu_percent) AS 'Maximum CPU Utilization In %',
       AVG(rs.avg_data_io_percent) AS 'Average Data IO In %',
       MAX(rs.avg_data_io_percent) AS 'Maximum Data IO In %',
       AVG(rs.avg_log_write_percent) AS 'Average Log Write Utilization In %',
       MAX(rs.avg_log_write_percent) AS 'Maximum Log Write Utilization In %',
       MAX(rs.max_worker_percent) AS 'Maximum Requests In %',
       MAX(rs.max_session_percent) AS 'Maximum Sessions In %'
FROM sys.resource_stats AS rs
WHERE rs.database_name = 'userdb1'
      AND rs.start_time > DATEADD(day, -7, GETDATE())
GROUP BY rs.database_name, rs.sku;

Fourteen days of history is a starting point, not a substitute: a baseline is recorded on purpose, across the workload's whole normal cycle, before anything goes wrong.

Built-in signal sources for Azure SQL Database

A user reports that an Azure SQL database was slow ten minutes ago, and a capacity planner wants last week's peak. The service already records evidence for both, in two built-in families of signals that differ in scope and time window: Azure Monitor metrics and the resource-statistics views.

Azure Monitor metrics

Azure SQL Database metrics are series of numeric values measured at regular intervals[1], such as CPU percentage, Data space used, Deadlocks, and Tempdb Percent Log Used. You choose an aggregation to summarize them over a period: Total, Count, Average, Minimum, or Maximum (the Sum aggregation is sometimes referred to as Total[9], and alert-rule settings list the choices as Sum, Count, Average, Min, or Max[10]), and some metrics can be split by dimensions, which add context such as the error behind a failed connection. Platform metrics are collected automatically, with no configuration[11]. The database's Overview page charts common metrics on its Monitoring tab, and metrics explorer[1] lets you change the time range, granularity, and aggregation, widen the scope to other Azure resources, and create alert rules.

The resource-statistics views

The resource-statistics views are Transact-SQL (T-SQL) views that report resource use as percentages of the limits of the database's or pool's compute size. They differ in where you run them, what they cover, and their time window (monitoring with DMVs[4]):

View Run it in Scope Time window
sys.dm_db_resource_stats the user database that database 15-second intervals, kept about one hour
sys.resource_stats master each database on the logical server 5-minute aggregates, kept about 14 days
sys.dm_elastic_pool_resource_stats any database in the pool the whole elastic pool recent, granular data
sys.elastic_pool_resource_stats master each elastic pool on the logical server history for the last 14 days

sys.dm_db_resource_stats records CPU, data I/O, log write, worker thread, and memory percentages for every 15-second interval[4], which makes it the first view for current-state troubleshooting. sys.resource_stats aggregates by 5-minute intervals, and new data can take several minutes to appear[4], so it suits historical analysis rather than near-real-time analysis.

Scope matters as much as the time window. sys.dm_elastic_pool_resource_stats reports percentages toward the elastic pool's limits, which might be higher than the limits for a single database in the pool[4], and when pool usage approaches 100 percent, the next step is to check how much each database contributes. sys.elastic_pool_resource_stats covers historical monitoring and trend analysis over the last 14 days[4].

Taken together: use metrics for charts and alerts, sys.dm_db_resource_stats for the last hour, and the master history views for the last two weeks, always at the scope of the boundary you are investigating.

Exporting telemetry with diagnostic settings

Portal charts read metrics where Azure Monitor stores them; a diagnostic setting is how SQL telemetry reaches a place where you can query it alongside other data, stream it, or archive it. Resource logs, which capture operations performed inside a resource, aren't collected by default and require a diagnostic setting[12].

What a diagnostic setting contains

A diagnostic setting is configured on one Azure SQL resource (a database, an elastic pool, a managed instance, or an instance database) and names two things: the telemetry categories to export and the destinations. For a single or pooled database, the event-based categories are SQLInsights, AutomaticTuning, QueryStoreRuntimeStatistics, QueryStoreWaitStatistics, Errors, DatabaseWaitStatistics, Timeouts, Blocks, and Deadlocks, and the Basic metrics option adds one-minute metrics[13] such as CPU percentage, log write percentage, and successful, failed, and firewall-blocked connections. An elastic pool exports Basic metrics, and the pool and its databases are configured separately[13]. For a managed instance, the instance-level category is ResourceUsageStats, and instance databases export SQLInsights, QueryStoreRuntimeStatistics, QueryStoreWaitStatistics, and Errors[13]. Nothing is exported until you select it.

Choosing a destination

Each destination serves a different consumer[13]:

Retention follows the destination: Event Hubs uses its retention policy; Azure Storage uses lifecycle management, and Log Analytics retention is set per table[14]. A resource supports at most five diagnostic settings, and one setting sends to at most one destination of each type[11], so sending to two workspaces takes two settings. The figure below traces one setting from its Azure SQL resource to the three destination types.

A look-alike: the Activity log

The Activity log records management (control plane) operations, such as creating a resource or a failed Resource Manager deployment[12], and it is collected automatically[11]. It can show who scaled or deleted a database, but not what the engine was doing, so it is no substitute for SQL resource logs.

A diagnostic setting is a deliberate export: select the categories, then pick each destination by what will consume the data.

Azure SQL resourcedatabase, pool, or instanceDiagnostic settingselected categoriesLog AnalyticsKQL, alertsEvent Hubsstream outAzure Storagearchive
One diagnostic setting sends the selected Azure SQL telemetry categories to Log Analytics, Event Hubs, and Azure Storage destinations.

Engine and host signals for SQL Server on Azure VMs

Picture SQL Server running on a Windows virtual machine in Azure. Two layers report on it: Azure Monitor collects host metrics for the VM and its disks, and the SQL Server engine publishes its own performance counters inside the guest operating system. Each layer shows what the other cannot, and this section covers where each comes from.

Windows Performance Monitor

On Windows, Performance Monitor displays SQL Server objects and performance counters together with the behavior of processors, memory, cache, threads, and processes[15]. Watching Windows and SQL Server counters at the same time reveals correlations; monitoring the Windows disk I/O counters and the SQL Server Buffer Manager counters together, for example, shows the behavior of the entire system[15]. The same page recommends concentrating first on disk activity, processor utilization, and memory usage. Performance Monitor adds some load itself, so log its data to another disk or computer, or run it remotely, and collect only the counters you need.

The same counters from T-SQL

The engine also exposes its counters through sys.dm_os_performance_counters[16], so a script or job can read them without Performance Monitor. The SQL Statistics object documentation[17] gives this query for that object:

Reading the SQL Statistics counters with T-SQL

SELECT *
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%SQL Statistics%';

The cntr_value column holds each counter's value, and it needs care: for per-second counters the value is cumulative, so the rate for an interval is the difference between two successive samples[16]. Batch Requests/sec, the number of T-SQL command batches received per second[17], is one such per-second counter.

Host metrics from Azure Monitor

The Azure Monitor metrics for virtual machines[18] describe the host, for example Percentage CPU, Available Memory Bytes, and per-disk metrics such as Data Disk IOPS Consumed Percentage (IOPS: input/output operations per second), most of them as one-minute values. None of them is a SQL Server engine counter: Page life expectancy or Batch Requests/sec comes from the SQL Server counter objects, not from the VM's platform metrics.

Read both layers for the same moment: comparing engine and host measurements helps identify correlations, not prove which one is the cause.

Interpreting Azure SQL Database compute metrics

Once you know where a number comes from, the next question is what 100 percent means for it. This section covers the compute metrics, including DTU (database transaction unit) percentage, whose denominators differ more than their similar names suggest.

Compute metrics and what 100 percent means

Metric 100 percent means Read it with
CPU percentage the user workload's CPU limit SQL instance CPU percent
SQL instance CPU percent the CPU limit for user and system workloads together CPU percentage
DTU percentage the highest of CPU, Data IO, and Log IO percentage at that moment its three component metrics
Workers percentage the user workload's worker-thread limit blocking, query duration, parallelism
Memory usage (avg_memory_usage_percent) the memory limit, mostly filled by the data cache out-of-memory events

The denominators in the first four rows come from the Azure SQL Database metrics reference[1]; the subsections below explain each pairing and cite the rest.

DTU percentage is the busiest of three dimensions

DTU percentage is derived from CPU percentage, Data IO percentage, and Log IO percentage, and at any point in time it matches the highest of the three[1]. A database at 95 percent DTU can therefore have low CPU and near-saturated log writes, and log-write pressure calls for a different investigation from CPU pressure, so open the components before choosing a remedy. Data IO percentage measures data file I/O, and Log IO percentage measures transaction log write throughput, each toward the user workload limit[1].

CPU percentage and SQL instance CPU percent

Azure SQL Database sets aside a limited portion of resources for internal processes such as high availability, backups, Query Store, and automatic tuning, and makes it available to user workloads when those processes are idle[19]. CPU percentage measures user consumption against the user workload limit, and SQL instance CPU percent measures total consumption against the limit for all workloads; because the two limits differ, the metrics are on different scales and aren't directly comparable. Performance might be noticeably affected if either metric is in the 70 to 100 percent range[19]: CPU percentage at 100 percent means the workload is using all the CPU available to it even if SQL instance CPU percent is lower, and SQL instance CPU percent in that range can flatten throughput and raise latency even while CPU percentage is well below 100 percent. SQL instance CPU percent and its memory counterpart, sql_instance_memory_percent, aren't available for the Basic, S0, S1, and S2 service objectives[19].

Memory that looks full

Unlike CPU, workers, or storage, reaching the memory limit doesn't negatively affect query performance or cause errors[19]: the engine uses available memory by design, mainly to cache data and avoid slower storage reads, so memory usage close to 100 percent is common and expected for a busy database that doesn't fit in memory. A shortage shows up as different evidence: out-of-memory errors, which can occur at any utilization between 0 and 100 percent and are detailed in sys.dm_os_out_of_memory_events[19]. Requests waiting for query memory grants are evidence of a different shortage, of query execution memory rather than of the data cache. A blocked request reports the type of its current wait, its wait type[20], and when RESOURCE_SEMAPHORE is the top wait type (the one with the most total wait time across current user requests in Microsoft's diagnostic query), queries can't start executing until they get a sufficiently large memory grant[4]. Lowering the chart by clearing the cache works against this design, because the cached pages are what spare queries the slower storage reads.

Sessions, workers, and requests

Three terms describe concurrent work[19]: a session is a process connected to the database engine; a request is the logical representation of a query or batch, issued by a client on a session; and a worker (worker thread) is a logical representation of an operating system thread. A request uses one worker with a serial plan and can use many with a parallel plan, and workers also serve other activities such as processing a login. New requests are rejected with an error when the session or worker limit is reached. With MAXDOP (max degree of parallelism) at 0 or above 1, the number of workers can be much higher than the number of requests[19], and workers pile up behind long-running queries and large blocking chains, so Workers percentage can climb while the connection count stays modest. Error 10928 still says "The request limit for the database is N and has been reached" for backward compatibility, but the limit actually reached is the number of workers[19]. A larger client connection pool admits more requests rather than reducing the workers existing requests hold; the documented mitigations are increasing the service tier or compute size, optimizing queries, reducing blocking, and reducing the MAXDOP setting when appropriate[19].

Read each compute metric against its own limit, and break composite or total metrics into their parts before choosing a fix.

Interpreting Azure SQL Database storage metrics

Storage metrics come in pairs that sound alike but measure different boundaries. This section covers data space and tempdb log space, and which one an alert should watch.

Data space used is the storage used in the data files of a database (for a pool, of all its databases), while Data space allocated is the space the data files themselves occupy[1]. Data files might contain empty space, so allocated is often higher than used for the same database or pool. Data space used percent compares used space with the data size limit of the database or pool[1], and that limit might be configured lower than the maximum the service objective allows. A large gap between allocated and used is free space inside the files; only the used percent tracks how close the data is to its limit.

tempdb has its own signal. Tempdb Percent Log Used (tempdb_log_used_percent) measures transaction log space consumed in tempdb toward its maximum log size[1]. It says nothing about user-database data files, so an alert on Data space used percent cannot warn about tempdb log exhaustion. Microsoft publishes recommended alert rules[1] as a starting point to adapt, not as universal thresholds; two of them cover storage. Each rule evaluates the metric aggregated over a lookback period and compares it with a threshold[1]; Configuring metric alerts for Azure SQL explains these settings.

Alert Metric Aggregation and threshold Check every / lookback period
Low data space Data space used percent Minimum, greater than 95 15 minutes / 15 minutes
Low tempdb log space Tempdb Percent Log Used Minimum, greater than 60 1 minute / 5 minutes

The Minimum aggregation is the smallest value captured over the aggregation interval[9], so a Minimum above the threshold means every value in the lookback period exceeded it: these rules detect sustained use rather than a single high sample.

Match the storage metric to the boundary in the incident: occupied data pages, the data files, the database or pool limit, or the tempdb log.

Interpreting connection and availability metrics

Connection metrics answer whether clients could reach a database, and the first section showed that the Availability metric answers a narrower question. This section gives its complete rule and the metrics that find the failing clients.

Availability is calculated for each one-minute interval as follows[1]:

  • if there is at least one successful connection, availability is 100 percent;
  • if all connections fail because of user errors, availability is 100 percent;
  • if there are no connection attempts, availability is 100 percent;
  • if all connections fail because of system errors, availability is 0 percent.

Two more limits apply. A minute counts as downtime, the 0 percent case above, when all continuous connection attempts within it fail because of a service issue, and intermittent unavailability counts as downtime only when the continuous unavailability crosses the minute boundary[1]. Availability data isn't supported for the serverless compute tier, where it displays 100 percent[1], so the value says nothing about a serverless database's connection health.

The connection metrics underneath separate the causes (metric definitions[1]):

Metric Counts Examples
Successful Connections connections established split by SslProtocol or ValidatedDriverNameAndVersion
Failed Connections : User Errors attempts that failed because of user-correctable errors incorrect password, connection blocked by firewall
Failed Connections : System Errors attempts that failed because of internal service errors most commonly transient

Both failed-connection metrics can be split by the Error dimension and by ValidatedDriverNameAndVersion, which identifies the specific error or client driver involved. In this classification, user errors also include failures caused by the user workload, such as resource governance errors, and by user management actions, such as scaling or a failover[1].

To investigate a firewall-related complaint, examine Failed Connections : User Errors split by Error, not the Availability chart; to be notified when the service itself fails connections, alert on Failed Connections : System Errors.

Interpreting SQL Server engine counters on a VM

Engine counters read through Performance Monitor or sys.dm_os_performance_counters, introduced in Engine and host signals for SQL Server on Azure VMs, need the same care with denominators and time windows. This section covers the Buffer Manager memory counters and the SQL Statistics compilation counters.

Buffer Manager counters

The SQL Server Buffer Manager object[21] provides counters for how SQL Server uses memory to store data pages (the buffer pool, which the counter descriptions also call the buffer cache) and for the physical I/O it performs as it reads and writes those pages:

Counter Measures
Page life expectancy the number of seconds a page stays in the buffer pool without references
Page reads/sec physical database page reads issued per second, across all databases
Free list stalls/sec requests per second that had to wait for a free page
Lazy writes/sec buffers per second the lazy writer flushes so they can be reused
Buffer cache hit ratio the percentage of pages found in the buffer cache without reading from disk

No single value proves memory pressure. Page life expectancy has no proper value; the 300 seconds cited in older documentation dates from servers with far less memory, so monitor it over time and evaluate sudden drops[22]. A falling Page life expectancy together with rising Page reads/sec and Free list stalls/sec suggests pages cycling through the cache, whereas Buffer cache hit ratio moves very little after a long period[21], so it can stay high while turnover rises. Judge all of these against your own baseline rather than a universal threshold. Query execution memory (memory grants) has its own counter in a different object: Memory Grants Pending, in the Memory Manager object, reports the total number of processes waiting for a workspace memory grant[23].

SQL Statistics counters

The SQL Statistics object[17] separates incoming work from optimization work:

  • Batch Requests/sec is the number of T-SQL command batches received per second.
  • SQL Compilations/sec is the number of times the compile code path is entered, and it already includes compiles caused by statement-level recompilations.
  • SQL Re-Compilations/sec is the number of statement recompiles.

Compare compilations with batch requests: compilations rising in step with batches means more work is arriving, while compilations or recompilations rising with flat batch volume points to the documented recompile causes, schema changes (including statistics changes after many rows are inserted or deleted) and session SET changes such as ANSI_PADDING or ANSI_NULLS[17]. Adding SQL Re-Compilations/sec to SQL Compilations/sec counts recompiles twice, because SQL Compilations/sec already contains them.

From T-SQL these are per-second counters, so cntr_value is cumulative and the rate is the difference between two samples over the interval between them[16]. The listing below takes two samples with a requested ten-second delay between them.

Turning two cumulative samples into a Batch Requests/sec rate

DECLARE @first bigint, @second bigint;

SELECT @first = cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%SQL Statistics%'
      AND counter_name = 'Batch Requests/sec';

WAITFOR DELAY '00:00:10';   -- request a 10-second delay; the actual interval can be longer

SELECT @second = cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%SQL Statistics%'
      AND counter_name = 'Batch Requests/sec';

SELECT (@second - @first) / 10.0 AS batch_requests_per_sec;   -- approximate rate assuming a 10-second interval, not measured elapsed time

Engine counters reward the same discipline as Azure metrics: know what each one counts, compare related counters with each other, and turn cumulative values into rates before comparing them with a baseline.

Interpreting VM disk and burst-credit metrics

When storage on a SQL Server VM is slow, the first question is which allowance is exhausted: a single disk's, or the VM's. This section covers the utilization metrics that answer it and the burst-credit metrics that explain a slowdown after a burst.

Disk limit or VM limit

Azure publishes storage I/O utilization metrics for VM series that support premium storage[24]. Data Disk IOPS Consumed Percentage divides the data disk IOPS completed by the disk's provisioned IOPS, and Data Disk Bandwidth Consumed Percentage does the same for throughput; at 100 percent, the application is I/O capped by that disk's IOPS or bandwidth limit. VM Cached and VM Uncached IOPS Consumed Percentage, and the matching Bandwidth Consumed Percentage metrics, compare the VM's total cached or uncached I/O with the VM's own limits, so 100 percent there means the VM is capping I/O whatever headroom individual disks have. OS Disk IOPS Consumed Percentage and OS Disk Bandwidth Consumed Percentage apply the same calculation to the OS disk[24], which a check of the data disks and the VM does not cover.

The data disk metrics carry a LUN dimension[18], the logical unit number that identifies each attached data disk. Split by LUN: an average across LUNs can hide one saturated disk among idle ones. Data Disk Latency (the average time to complete each I/O, in milliseconds) and Data Disk Queue Depth (outstanding I/O requests)[24] describe the impact, the delay and the backlog, rather than which limit was reached. The figure below orders the checks.

Burst credits

Some resources can run above their non-burst limit (a provisioned allowance, not the performance baseline described in Building an operational performance baseline) for a while by spending credits, and credit metrics show consumption or remaining credit, depending on the metric:

The burst metrics measure credit used so far: a high Used Burst BPS Credits Percentage means throughput credit is nearly spent, not that it is available. A workload that ran fast during a burst and slowed when credits ran out has returned to its non-burst limit, so size sustained demand against the non-burst limits, not against the burst peak.

Find the capped allowance first, per disk and then per VM, then use the credit metrics to separate a temporary burst from sustained capacity.

Data Disk IOPS orBandwidth Consumed100% on a LUN?YesDisk limitthat LUNNoVM Cached orUncached Consumedat 100%?YesVM limitall disksNoNo data disk or VM capcheck the OS disk
Locating the I/O cap on a SQL Server VM: per-LUN data disk consumption, then VM cached and uncached consumption, then the OS disk.

Configuring metric alerts for Azure SQL

An alert can send an email or call a webhook when a metric such as CPU usage reaches a threshold[10]. This section covers how an Azure Monitor alert rule turns a metric into a notification, and which settings decide whether the alert is useful or noise.

The parts of a metric alert rule

The Azure SQL Database alert setup offers alerts based on metric values, activity log events, or resource health events[10]; database watcher uses log search alert rules[26] instead (Troubleshooting database watcher alerts). This section covers metric alerts. The following four core groups organize the rule settings:

The figure below traces one evaluation from signal to action group.

Choosing the aggregation and the lookback period

The Maximum aggregation is the largest value captured over the aggregation interval, and larger time granularities smooth out spikes[9]; in an alert rule, that interval is the lookback period. So Maximum exposes brief saturation that Average hides, Minimum shows sustained use (the storage-alert reading in Interpreting Azure SQL Database storage metrics), and Total counts events over the lookback period. Microsoft's recommended alert rules[1] follow this pattern: High worker usage uses Minimum greater than 60 over a 5-minute lookback, and Failed connections (system errors) uses Total greater than 10 over 5 minutes, both checked every minute.

Static or dynamic threshold

A static threshold is a value you set. Dynamic thresholds use machine learning to learn the metric's behavior patterns and calculate thresholds, with a sensitivity of High, Medium (the default), or Low[10]. They need history: rules with dynamic thresholds don't trigger before collecting three days and at least 30 samples of data, and they need three weeks of history to detect weekly seasonality[27]. A new database with a known hard limit therefore needs a static threshold from the first day.

Stateful by default

Metric alerts are stateful by default: once the rule triggers, the alert fires once and stays in the fired state until it is resolved, when a resolved notification is sent, and a new alert can fire only after that[1]. An ongoing condition therefore produces one fired and one resolved notification, not a message at every evaluation.

A useful alert names the right scope and signal, aggregates it over a lookback period that matches the condition, and routes it through an action group to someone, or something, that can act.

1. Metric signalsuch as Workers percentage2. Aggregate over lookback periodsuch as Minimum or Total3. Compare with thresholdstatic or dynamic4. By default, fires oncestays fired until resolved5. Action groupnotify or automate
How one Azure Monitor metric alert rule evaluates a signal and reaches its action group.

How database watcher collects and stores data

Database watcher is a preview monitoring service for Azure SQL that is being retired, so the practical skill is working with a watcher that already exists. This section covers its retirement timeline, its components, and how it collects data.

Status and retirement

Database watcher retires on April 30, 2027 and won't reach general availability[28]. Starting November 9, 2026, you can't create new watcher resources or edit existing ones; existing watchers keep operating and collecting data until retirement, when they enter a stopped state and can't be started again. Retirement doesn't stop or deprovision the Azure Data Explorer or Microsoft Fabric resources holding the data, retention settings still apply, and retained resources can continue to incur charges. Existing alert rules aren't deleted automatically, so Microsoft's guidance is to recreate and test the alerts you need before retirement, then disable or delete the old rules, and to transition to Database Hub in Microsoft Fabric before that date.

Components

A watcher is an Azure resource configured with a data store and a set of SQL targets[28]: the databases, elastic pools, and SQL managed instances it monitors, which can be in any subscription in the same Microsoft Entra ID tenant. The data store is a database in Azure Data Explorer or in Real-Time Analytics in Microsoft Fabric. You read the data by querying the store with KQL or T-SQL, or through dashboards in the Azure portal: estate dashboards show data for multiple monitored resources in a common view, with heatmaps and top queries across the whole set of monitored databases, elastic pools, and managed instances (the Azure SQL estate), and resource dashboards show detail for one resource[28]. The figure below follows the data from SQL targets to its consumers; its log search alert rule branch, including which data stores support alerts, is explained in Troubleshooting database watcher alerts.

How collection works

The watcher collects monitoring data at periodic intervals using T-SQL queries, and each execution of a query produces a sample[29]. Frequency varies by dataset: frequently changing data such as SQL performance counters might be collected every 10 seconds, and mostly static data such as database configuration every five minutes. Streaming ingestion typically makes collected data available for analysis in less than 10 seconds, and the Ingestion statistics link on the dashboards shows the latency. A high availability replica is monitored independently of its parent and counts as a separate target[28].

For an existing estate, the watcher remains a central, near-real-time view across databases, pools, and instances until April 30, 2027, and its replacement needs planning before then.

DatabaseElastic poolManagedinstanceWatcherT-SQL samplesData storeAzure Data Explorer orReal-Time AnalyticsDashboards,KQL or T-SQLLog searchalert rules:Azure Data Explorer,not a free cluster
Database watcher data flow from SQL targets through the watcher and its data store to dashboards, queries, and log search alert rules.

Reading database watcher data correctly

Watcher data is a time series of samples from many targets and replicas, which invites several predictable misreads. This section covers timestamps and replicas, pool and database targets, and collection gaps.

Observation time and replica identity

Every dataset has common columns[29]. sample_time_utc is the time the values in a row were observed; collection_time_utc, present only in datasets where the two can differ, is the time the watcher collected the row. The sqldb_database_resource_utilization dataset shows why: it is derived from sys.dm_db_resource_stats, whose rows each cover a 15-second interval, so one collection returns several rows with different sample_time_utc values and the same collection_time_utc. The replica_type column (for a database: Primary, HA secondary, Geo-replication forwarder, or Named secondary) keeps primary and secondary measurements apart, so filter or group by target and replica before charting. Wait statistics and storage I/O datasets hold cumulative statistics[29], so the activity in an interval is the difference between successive samples of the same series, never their sum.

Pool targets and database targets

To monitor an elastic pool, you designate one database in the pool as the anchor database, which the watcher connects to[29]. Data from the anchor contains pool-level metrics plus certain per-database metrics, such as resource utilization and request rates. Pool-level CPU, memory, storage utilization, and wait statistics are collected only at the pool level because they cannot be attributed to one database, while query runtime statistics, database properties, and table and index metadata are available only if you add individual databases as targets. When you add pooled databases as targets, add the pool as well. In a dense elastic pool, monitor at the pool level, because monitoring queries running in many of its databases can affect the workload.

A gap is not an outage

Monitoring queries use short lock timeouts and a low deadlock priority, and when resource utilization is high or they conflict with application queries, they are deprioritized[29]. Gaps also appear if automation terminates long-running sessions; exclude sessions whose program_name is SQLExternalMonitoring or x_ms_reserved_sql_external_monitoring. A blank stretch of chart can therefore mean a busy target, not an idle or failed one; check the ingestion statistics and the target's own metrics before concluding either.

Group samples by target and replica, and treat a gap as a question about the target, not proof of an outage.

Troubleshooting database watcher alerts

Collection and alerting are separate: watcher alerts are log search alert rules that query the watcher's data store on a schedule[26]. They are created from templates such as High CPU utilization, High worker utilization, or Deadlock spike, or written yourself.

Each log search alert rule needs a managed identity to access the watcher's data store, and to follow the principle of least privilege Microsoft recommends a separate identity for alert rules rather than reusing the watcher identity[26]; for an Azure Data Explorer store, that identity needs the Reader role on the cluster and the Viewer role on the database. Alerts aren't available when the data store is Real-Time Analytics in Fabric or a free Azure Data Explorer cluster[28]. Notifications arrive per SQL target, so high CPU on two managed instances produces two emails[26].

A watcher that is still collecting proves nothing about its alerts: when expected alerts are missing, check the rule's query, evaluation frequency, identity, and action group separately from the watcher.

Designing an Extended Events session

Counters and metrics can show that a boundary is busy without showing which executions made it so; for that you need a record of individual events. Extended Events (XEvents) is the lightweight event-collection system of SQL Server, Azure SQL Database, and Azure SQL Managed Instance[30]. This section covers why it replaces older tracing, what a session is made of, and how its scope depends on the platform.

Why Extended Events, not SQL Trace

SQL Trace and SQL Server Profiler are deprecated; use Extended Events instead[31]. SQL Server Profiler can't trace Azure SQL Database: connecting it produces a misleading message asking for sysadmin membership or the ALTER TRACE permission, when the real problem is that Azure SQL Database isn't supported, so granting permissions changes nothing. The XEvent Profiler in SQL Server Management Studio (SSMS)[32] is a different tool despite its name: a live viewer of events, built on Extended Events, for SQL Server and Azure SQL Managed Instance.

The parts of a session

A session combines events, actions, predicates, and targets[33]:

  • An event is a specific occurrence to watch for, such as sql_batch_completed, which fires when a batch completes.
  • An action collects extra context when the event fires, such as sqlserver.session_id, the ID of the connected session that ran the batch, not of the event session capturing it.
  • A predicate is a WHERE condition; when it is false, the event is dropped before any action or target processing[34].
  • A target receives the events that are kept, such as event_file or ring_buffer; unlike a database watcher SQL target, which is a monitored resource, an Extended Events target is a destination for captured events.

Events and targets are independent: any target can receive any event[30], but actions intended for specific events cannot bind to other events[35]. A target consumes and processes events; an action can append contextual fields to them. To see what a platform exposes, Microsoft gives this query (Extended Events in Azure SQL[36]):

Listing the events, actions, and targets available on a platform

SELECT o.object_type,
       p.name AS package_name,
       o.name AS db_object_name,
       o.description AS db_obj_description
FROM sys.dm_xe_objects AS o
INNER JOIN sys.dm_xe_packages AS p
    ON p.guid = o.package_guid
WHERE o.object_type IN ('action', 'event', 'target')
ORDER BY o.object_type, p.name, o.name;

To correlate related events that occur on different server connections, set TRACK_CAUSALITY = ON[34] in the session options.

Scope follows the platform

In Azure SQL Database, event sessions are always database-scoped[36]: a session in one database can't collect events from another database, and the statements use ON DATABASE. SQL Server uses ON SERVER. Azure SQL Managed Instance supports both server-scoped and database-scoped sessions, and Microsoft recommends server-scoped sessions for most scenarios. An Azure SQL Database logical server is not an instance you can trace as a whole: to watch three databases, create a session in each. On SQL Server and Azure SQL Managed Instance, also check the built-in system_health session[37] before creating a new one: it starts automatically with the Database Engine and collects data such as detected deadlocks with their deadlock graphs. Azure SQL Database has no built-in system_health session.

The listing below defines a database-scoped session for an Azure SQL Database investigation, using the predicate style from Microsoft's quick start. Its event_file target needs the storage credential described in Authorizing an event_file target in Azure Storage.

A database-scoped session that captures completed batches whose SQL text mentions Orders (Azure SQL Database)

CREATE EVENT SESSION [batch_capture] ON DATABASE
ADD EVENT sqlserver.sql_batch_completed
    (
    ACTION (sqlserver.session_id, sqlserver.sql_text)   -- context added to each kept event
    WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text], N'%Orders%'))   -- keep only batches that mention Orders
    )
ADD TARGET package0.event_file
    (SET filename = N'https://<storage-account-name>.blob.core.windows.net/<container-name>/batch_capture.xel')
WITH (STARTUP_STATE = OFF);   -- a short-lived investigation: do not restart automatically

The figure below traces one event from this session through the predicate, the actions, the session buffers (sized by the session's MAX_MEMORY option[34]), and dispatch to the target.

A good session is narrow by design: the event picks the execution point, actions add only the context you need, predicates discard the rest early, and the platform decides whether the scope is one database or the instance.

Event firessql_batch_completedPredicate true?WHERE conditionNoEventdroppedYesActions add contextsqlserver.session_idSession buffersMAX_MEMORYdispatchTargetevent_file, ring_buffer
Path of one Extended Events event through predicate, actions, session buffers, and dispatch to a target.

Choosing between event_file and ring_buffer targets

The target decides how long captured evidence lasts and how you read it back. This section compares the two general-purpose targets and the reading method for each.

Property event_file ring_buffer
Where events go .xel files: local disk or Azure blobs on SQL Server, Azure Storage blobs only on Azure SQL Database and Managed Instance[36] memory
Retention files remain after the session stops; max_file_size limits individual file size; max_rollover_files limits retained files where supported, with Azure-blob support currently in preview for newly created sessions in Azure SQL Database and SQL database in Fabric only[38] older events are discarded as new ones fill the memory, and all output is discarded when the session stops[38]
Read it with the SSMS event viewer or sys.fn_xe_file_target_read_file target_data from the session-targets DMV, cast to XML
Use it for evidence that must persist, or analysis in the SSMS event viewer[36] a quick look at recent events during ad hoc troubleshooting[36]

A local path that works on a SQL Server VM is therefore not a valid event_file location on the managed services, and a ring buffer is never an incident archive.

Reading an event file

You can view event data in the SSMS event viewer, or read it with sys.fn_xe_file_target_read_file()[39], which represents the contents of an .xel file as a relational rowset[38]; for Azure SQL the files are addressed by their blob URL. SSMS 19.2 and later reads the .xel blobs for each session directly from the storage container[39].

Reading a ring buffer

A ring buffer is read from the runtime DMVs: sys.dm_xe_session_targets for server-scoped sessions and sys.dm_xe_database_session_targets for database-scoped ones, joined to the matching sessions view to pick the session. The first step of Microsoft's ring buffer walkthrough[40] for Azure SQL Database is:

Reading a database-scoped ring buffer as XML (Azure SQL Database)

SELECT CAST(xst.target_data AS xml) AS TargetData
FROM sys.dm_xe_database_session_targets AS xst
INNER JOIN sys.dm_xe_database_sessions AS xs
    ON xst.event_session_address = xs.address
WHERE xs.name = N'example-session';

target_data holds the data the target maintains, and event_session_address links each target row to its session's address[41]. The XML can be incomplete even while the session runs: the ring buffer's contents are omitted from the XML when the truncated attribute is 1[38], and Microsoft recommends setting the ring_buffer target's MAX_MEMORY parameter, which is distinct from the session's MAX_MEMORY, to 1,024 KB or less to help avoid truncation of the XML output[34]. XML truncation is a reading problem, separate from events lost in session buffers, which What happens after an event session starts covers.

Choose event_file when the evidence must outlive the session or be shared, and ring_buffer only for a quick, disposable look.

Authorizing an event_file target in Azure Storage

On Azure SQL Database and Managed Instance, an event_file target writes to a blob container, so the Database Engine itself, not the person creating the session, needs access to the container. This section covers the two ways to grant that access and the requirements for the storage account.

Two ways to authorize the engine

The Database Engine running the event session must have specific access to the blob container, granted in one of two ways[36]:

  1. Managed identity: assign the Storage Blob Data Contributor role on the container to the managed identity of the Azure SQL logical server or managed instance (or the equivalent container read and blob read, write, and delete actions), then create a credential that tells the engine to use managed identity.
  2. SAS token: create a shared access signature (SAS) token for the container with the rwdl (Read, Write, Delete, List) permissions, start and expiry times that cover the whole lifetime of the session, and no IP address restrictions, then store it in a credential.

A credential is a record containing the authentication information needed to connect to a resource outside SQL Server[42]. In Azure SQL Database it must be a database-scoped credential; in SQL Server and Azure SQL Managed Instance, use a server-scoped credential[36]. The credential's name must match the URL of the blob container, without a trailing slash[39]. Microsoft's walkthrough creates the managed-identity version in the user database (the batch requires the CONTROL database permission):

Database-scoped credential for managed-identity access (Azure SQL Database)

CREATE DATABASE SCOPED CREDENTIAL
    [https://<storage-account-name>.blob.core.windows.net/<container-name>]
WITH IDENTITY = 'MANAGED IDENTITY';

An operator's own access to the storage account in the portal authorizes nothing for the engine. Starting a session without a matching credential fails with operating system error 86, and a managed identity without the required role assignment fails with error 5, 'Access is denied'[39].

Storage account requirements

Microsoft's best practices for event_file storage[36] are:

  • use a storage account in the same Azure region as the database or managed instance;
  • align redundancy with the SQL resource: LRS, GRS, or RA-GRS (locally redundant, geo-redundant, or read-access geo-redundant storage) for locally redundant resources, and ZRS, GZRS, or RA-GZRS (the zone-redundant equivalents) for zone-redundant resources;
  • use only the Hot blob access tier;
  • don't enable the hierarchical namespace on the storage account;
  • review role assignments and access control lists on the account and container, including inherited access, because the event files might contain sensitive data.

An immutability policy on the container or an enabled hierarchical namespace makes the session fail with error 13, 'The data is invalid'[39].

The engine writes the files, so the engine needs the credential and the storage permission; the people who read the files need their own, separately reviewed, access.

What happens after an event session starts

Creating a session and collecting events are separate steps, and a running session still holds events in memory before they reach the target. This section covers session state, buffering and dispatch, event retention modes, and the Azure SQL Database limits on running sessions.

Defined is not running

Extended Events catalog views describe session definitions, while Extended Events DMVs return information about started sessions[36]. A new session isn't started when it is created[39]; start it with ALTER EVENT SESSION ... STATE = START[34] or from SSMS, and confirm it is running in the runtime views, sys.dm_xe_sessions for server-scoped sessions and sys.dm_xe_database_sessions for database-scoped ones[40]. Use STARTUP_STATE = ON for a continuously running session that should start again after each Database Engine restart, such as after a failover or a maintenance event, and STARTUP_STATE = OFF for short-term sessions used in ad hoc troubleshooting[36].

Buffering and dispatch

Events pass through session memory buffers before a target receives them, the buffer stage in the figure in Designing an Extended Events session. MAX_MEMORY sets the memory for event buffering, with a default of 4 MB[34]. MAX_DISPATCH_LATENCY sets how long events stay buffered before dispatch to targets, 30 seconds by default; a value in seconds is the wait before buffers start flushing, and INFINITE flushes only when the buffers are full or the session closes[34]. A low-volume session set to INFINITE can show an empty target for a long time while it is still collecting, so delayed output alone does not mean collection stopped.

Event retention mode

EVENT_RETENTION_MODE decides what happens when the buffers are full[34]:

Mode When buffers are full
ALLOW_SINGLE_EVENT_LOSS a single event can be dropped, minimizing both performance impact and data loss
ALLOW_MULTIPLE_EVENT_LOSS full buffers containing many events can be lost; this generally avoids performance impact when buffers fill quickly
NO_EVENT_LOSS every task that fires an event waits for buffer space, so user sessions and queries might stall

NO_EVENT_LOSS buys completeness with workload responsiveness, and on Azure it often doesn't buy it at all: for event file targets in Azure SQL Database and in Azure SQL Managed Instance with the SQL Server 2025 or Always-up-to-date update policy, NO_EVENT_LOSS has behaved like ALLOW_SINGLE_EVENT_LOSS since June 2024; specifying it returns warning 25665 and creates the session with ALLOW_SINGLE_EVENT_LOSS[34]. The session's MAX_MEMORY is distinct from the ring_buffer target's MAX_MEMORY[34], so enlarging the target does not enlarge the session buffers that fill up.

Limits in Azure SQL Database

In Azure SQL Database, total event session memory is limited to 128 MB in a single database and 512 MB across an elastic pool, and the number of started sessions is limited to 100 per database and 100 database-scoped sessions per pool[36]. In dense elastic pools, a session can fail to start because of memory even below 100 sessions. The corrective actions are to run fewer concurrent sessions or to reduce the session MAX_MEMORY.

For current event collection, confirm the session is running and delivering: expect dispatch latency, and choose a retention mode knowing what each one costs.

Delegating Extended Events with granular permissions

Managing sessions and reading their evidence are separate rights, so a monitoring reader doesn't need the power to change sessions. This section covers which permission covers which task on each platform.

Task SQL Server and Managed Instance (server-scoped sessions) Azure SQL Database (database-scoped sessions)
Create a session CREATE ANY EVENT SESSION (SQL Server 2022 and later, and Managed Instance) or ALTER ANY EVENT SESSION CREATE ANY DATABASE EVENT SESSION
Administer existing sessions ALTER ANY EVENT SESSION ALTER ANY DATABASE EVENT SESSION
Read runtime target data VIEW SERVER STATE on sys.dm_xe_session_targets; VIEW SERVER PERFORMANCE STATE in SQL Server 2022 and later VIEW DATABASE STATE on sys.dm_xe_database_session_targets

The creation permissions come from CREATE EVENT SESSION[34], and the viewing permissions from the reference pages for sys.dm_xe_session_targets[43] and sys.dm_xe_database_session_targets[41]. The Extended Events permission model is granular on SQL Server 2022 and later, Managed Instance, and Azure SQL Database[30], with narrower forms such as ALTER ANY EVENT SESSION ADD EVENT or ALTER ANY DATABASE EVENT SESSION OPTION; Managed Instance also accepts the database-scoped permissions for its database-scoped sessions.

The viewing permissions let a reader see target data without being able to change any session definition, so granting ALTER ANY EVENT SESSION to someone who only reads results delegates far more than the task needs. None of these SQL permissions authorizes the engine to write event files to Azure Storage; that is the credential and role assignment from Authorizing an event_file target in Azure Storage.

Grant creation, administration, and viewing separately, at the narrowest scope that fits the platform.

Exam patterns for monitoring resource activity

For scenario-based practice on this objective, match each symptom to a tool or reading whose scope, denominator, and time window fit the scenario.

  • A spike ten minutes ago in one Azure SQL database: sys.dm_db_resource_stats in that database. A request snapshot shows only work still running, and sys.resource_stats lags by several minutes (Built-in signal sources for Azure SQL Database).
  • Last week's trend: sys.resource_stats, or sys.elastic_pool_resource_stats for a pool, in master; the 15-second view keeps only about an hour.
  • Availability at 100 percent while users report login failures: examine Failed Connections : User Errors split by Error. The Availability rule treats user-error failures as available (Interpreting connection and availability metrics).
  • DTU percentage near 100: inspect CPU, Data IO, and Log IO percentage before scaling, because DTU percentage is only the highest of the three.
  • Memory usage near 100 percent with no out-of-memory errors: usually healthy caching; clearing the cache only adds storage reads.
  • Error 10928 with few connections: workers held by parallel, blocked, or long-running requests, not a connection-count problem, so a larger connection pool is the wrong fix.
  • A SQL Server VM with slow storage: split Data Disk IOPS and Bandwidth Consumed Percentage by LUN, then check VM Cached and Uncached consumption; an average across LUNs can hide the saturated disk.
  • A slowdown after a burst: Used Burst Credits Percentage metrics count credit already spent, and an exhausted B-series VM returns to base CPU performance.
  • Buffer Manager or SQL Statistics counters on a SQL VM: Performance Monitor or sys.dm_os_performance_counters, not the VM's platform metrics, which hold no engine counters.
  • Streaming SQL telemetry to a SIEM: a diagnostic setting with an event hub destination. The Activity log records management operations, not engine telemetry.
  • Alerting on a brand-new database: a static threshold, because a dynamic threshold needs three days and 30 samples before it can fire.
  • Notifying an operator: the action group linked to the alert rule is what notifies or runs automation.
  • A new database watcher: not possible starting November 9, 2026; existing watchers run until April 30, 2027. Missing watcher alerts call for checking the identity the alert rule uses and its query, not the watcher's SQL access.
  • SQL Server Profiler against Azure SQL Database: unsupported whatever the permissions; create a database-scoped Extended Events session (ON DATABASE).
  • Evidence that must survive the session: event_file with a blob credential the engine can use; a ring_buffer loses its output when the session stops.
  • A reader who only needs ring buffer output in Azure SQL Database: VIEW DATABASE STATE, not ALTER ANY DATABASE EVENT SESSION.

In each case, ask which boundary the question is about, what 100 percent means for the signal offered, and whether the signal's time window still holds the evidence.

Performance signal sources by scope, record type, and time window

PropertyAzure Monitor metricsResource-statistics viewsDiagnostic settingsPerformance MonitorDatabase watcherExtended Events
ScopeAzure SQL databases and elastic pools; host metrics for a SQL Server VMOne database (sys.dm_db_resource_stats) or every database on the logical server (sys.resource_stats)Each database, elastic pool, or managed instance it is configured onSQL Server and Windows on the VMSelected databases, elastic pools, and managed instancesOne database (Azure SQL Database) or the instance (SQL Server, Managed Instance)
What it recordsNumeric time series such as CPU percentage and AvailabilityCPU, data IO, log write, worker, and memory percentagesSelected resource-log categories and metrics, exportedSQL Server counter objects and operating-system countersSamples from SQL system viewsIndividual events with the actions you choose
Time windowTypically one-minute values, with metric-specific exceptions, aggregated to the chart granularity or the alert rule's lookback period15-second samples for about an hour, or 5-minute aggregates for about 14 daysSet by the destination's retention policy or workspace tierUpdated at a frequency you defineDataset-specific, such as 10 seconds for performance countersUntil the session stops (ring_buffer) or while the blobs or files are kept (event_file)
Reach for it whenCharting and alerting on resource consumptionDiagnosing a recent spike or a two-week trendQuerying with KQL, streaming to third-party tools, or archivingCorrelating engine and host activity on a SQL VMComparing an already monitored estate before April 30, 2027Capturing specific executions and their context

Decision tree

Individual eventswith context?YesExtended EventssessionNoSQL Server counterson a Windows VM?YesPerformanceMonitorNoExport logs to query,stream, or archive?YesDiagnosticsettingNoChart or alerton a metric?YesAzure MonitormetricsNoMonitored by anexisting watcher?YesDatabasewatcherNoAzure SQL Databasespike, last hour?Yessys.dm_db_resource_statsNosys.resource_statsmaster, about 14 days

Sharp facts the exam loves — give these one last read before exam day.

Cheat sheet

Sharp facts the exam loves — scan these before test day.

A useful baseline includes normal peak and off-peak operation

Collect performance measurements at regular intervals while the database is healthy, including peak and off-peak periods, production query response times, and backup and restore completion times. Preserve these observations so later measurements can be compared with the corresponding workload period. A deviation from normal behavior identifies a tuning candidate; a single busy-period snapshot does not establish what normal operation looks like.

3 questions test this
Response time and throughput measure different aspects of service

Response time measures how long a user waits for the first returned row, whereas throughput measures the number of queries handled in a period. Record both alongside user concurrency and resource usage: more completed requests do not establish that individual requests became faster. Use the application's latency and completion requirements to judge whether a change improved service under comparable load.

Trap Using batch throughput alone as the acceptance criterion for an interactive workload whose requirement concerns individual response time.

3 questions test this
Performance Monitor collects SQL Server engine counters on Windows

For SQL Server on a Windows Azure VM, use Windows Performance Monitor to collect SQL Server counter objects such as Buffer Manager and SQL Statistics together with operating-system CPU, memory, and disk counters. This correlates engine activity with host resource consumption over time. Use an event-oriented collector such as Extended Events when individual executions and their context, rather than counter rates, are needed.

Trap Using only Microsoft.Compute virtual-machine platform metrics for buffer-cache and batch-request counters, which are SQL engine instrumentation rather than host platform metrics.

4 questions test this
Azure SQL resource views trade recent detail for historical retention

In Azure SQL Database, query sys.dm_db_resource_stats in the user database for resource percentages sampled over 15-second intervals and retained for approximately one hour. Query sys.resource_stats in the logical server's master database for five-minute aggregates retained for approximately 14 days. Choose the first for a current short-lived spike and the second for historical capacity trends, allowing for its reporting delay. For pool-wide measurements, query sys.dm_elastic_pool_resource_stats from any database in the pool for recent detail, or sys.elastic_pool_resource_stats in master for historical trends over the last 14 days. Pool resource percentages use the elastic pool's limits rather than an individual database's limits, so match the view's scope to the resource boundary being investigated.

Trap Using a current request snapshot to reconstruct a resource-utilization incident from the previous week, after those requests have finished.

4 questions test this
Diagnostic settings route Azure SQL logs to purpose-specific destinations

Configure diagnostic settings on Azure SQL resources to export selected resource-log categories and metrics. Send telemetry to Log Analytics for queryable analysis with other monitoring data, Event Hubs for streaming integrations, or Azure Storage for archival. Choose the required categories explicitly; viewing platform metrics in the portal is a different operation from exporting resource logs for downstream consumption.

Trap Using the Azure Activity log as a substitute for SQL diagnostic resource logs, because it records management-plane operations rather than the selected engine telemetry.

4 questions test this
DTU percentage follows the busiest of three resource dimensions

Azure SQL Database DTU percentage is the highest of CPU percentage, Data IO percentage, and Log IO percentage at a point in time. Inspect those underlying dimensions before choosing a remedy, because a log-write-limited workload needs a different investigation from a CPU-heavy one. A high composite value can therefore occur while two component metrics remain low.

4 questions test this
User CPU and SQL instance CPU percentages have different denominators

Azure SQL Database CPU percentage measures consumption toward the user workload CPU limit, while SQL instance CPU percent includes user and system workloads on an instance scale. These percentages are not directly comparable or interchangeable. Use the user metric to assess the workload's entitlement and the instance metric to investigate total engine pressure, including internal activity.

4 questions test this
High cache memory use does not by itself establish memory pressure

The database engine intentionally caches data in available memory to avoid slower storage reads, so a high Azure SQL memory-utilization percentage can be healthy. Investigate evidence such as waiting memory grants, out-of-memory errors, or excessive physical reads before diagnosing a shortage. For SQL Server Buffer Manager, Page life expectancy measures seconds a page stays without references, Free list stalls/sec counts requests waiting for a free page, and Lazy writes/sec counts dirty buffers written to make buffers reusable. Falling page life expectancy together with rising physical Page reads/sec and free-list stalls suggests cache turnover, while Buffer cache hit ratio measures pages found in cache and can change slowly. Evaluate these counters against the workload and its healthy baseline rather than an isolated universal threshold, and distinguish cache turnover from query execution memory contention.

Trap Clearing the buffer cache to lower the memory-utilization chart, which discards useful cached pages and creates additional storage reads.

3 questions test this
A parallel request can consume several workers within one session

A session is a connection, a request is a submitted query or batch, and a parallel request can require multiple worker threads. In Azure SQL Database, hitting worker or session limits rejects new work, and blocked or long-running parallel requests can exhaust workers even without a large connection count. Correlate worker utilization with blocking, query duration, and parallelism before changing connection limits or compute size.

Trap Increasing the client connection-pool limit to address worker exhaustion, which can admit additional work without reducing workers consumed by existing requests.

3 questions test this
Storage utilization metrics must match the affected data or log boundary

Azure SQL Database Data space used measures occupied data pages, whereas Data space allocated measures the data files, including free space inside them. Deleting rows can make space reusable without reducing allocated file size. Compare both metrics with the database and elastic-pool storage boundaries when deciding whether growth, reclamation, or a capacity change is actually required. For tempdb transaction-log capacity, use Tempdb Percent Log Used (tempdb_log_used_percent), which measures log-space consumption toward tempdb's maximum log size rather than user-database data space or PAGELATCH contention. A low-tempdb-log-space alert can use this signal with Minimum greater than 60 percent over five minutes, evaluated every minute; this documented example detects sustained high utilization rather than defining a universal threshold.

Trap Using user-database Data space used percent to alert on tempdb log exhaustion, which measures occupied data-file space against a different capacity boundary.

3 questions test this
Storage utilization metrics identify the saturated disk or VM allowance

For a SQL Server VM storage incident, split Data Disk IOPS Consumed Percentage and Data Disk Bandwidth Consumed Percentage by LUN to distinguish operation-rate saturation from bytes-per-second saturation on each disk. Compare these with VM Cached and VM Uncached IOPS Consumed Percentage and Bandwidth Consumed Percentage, where supported, to identify which storage allowance is being consumed. A saturated VM metric with unsaturated individual-disk metrics directs investigation to the VM allowance rather than a single disk. Correlate Data Disk Latency in milliseconds and Data Disk Queue Depth with the utilization pattern, because outstanding I/O and completion delay describe the impact rather than identifying the capped dimension by themselves.

Trap Using Data Disk IOPS Consumed Percentage averaged across LUNs to rule out a disk bottleneck, which can conceal one saturated disk among idle disks.

3 questions test this
Compilation rates must be interpreted alongside incoming batch volume

Compare SQL Compilations/sec and SQL Re-Compilations/sec with Batch Requests/sec to distinguish optimization work from the volume of incoming T-SQL batches; SQL Compilations/sec already includes statement recompilations. Rising recompilations call for checking schema or statistics changes and session SET changes such as ANSI_NULLS, rather than assuming that more batches arrived. Query sys.dm_os_performance_counters with object_name LIKE '%SQL Statistics%' for a T-SQL source of these engine counters. For per-second counters, derive interval rates from successive cumulative cntr_value samples and elapsed time instead of treating one raw value as a per-second rate.

Trap Using the sum of SQL Re-Compilations/sec and SQL Compilations/sec as total compilation work, which counts statement recompilations twice.

3 questions test this
Burst-credit exhaustion returns a SQL VM resource to its baseline allowance

For a SQL VM slowdown after a burst, inspect credit telemetry before treating the burst maximum as sustained capacity. On B-series VMs, correlate CPU Credits Remaining with CPU Credits Consumed: running above base CPU performance spends credits, and exhaustion throttles CPU back to the base level until credits accumulate. For credit-based I/O bursting where supported, examine Data Disk Used Burst IO Credits Percentage and Data Disk Used Burst BPS Credits Percentage, plus the corresponding VM Cached or VM Uncached credit metrics, to distinguish depleted operation-count credits from depleted throughput credits at disk or VM scope. These I/O metrics report the percentage already used, not credits remaining, so compare the post-burst workload with baseline IOPS or bandwidth rather than sizing sustained demand from a short burst.

Trap Assuming a high Used Burst BPS Credits Percentage represents spare throughput credit, which reverses a metric that measures credits already consumed.

3 questions test this
A 100 percent Availability value does not establish that every SQL client can connect

Azure SQL Database Availability describes one-minute connection outcomes, not success for every client. For a supported database, a minute reports 100 percent if at least one connection succeeds, all attempts fail from user errors, or there are no attempts; it reports 0 percent when all attempts fail from system errors. Use Failed Connections : User Errors for user-correctable problems such as passwords or firewall rules, and Failed Connections : System Errors for internal service failures, splitting by Error to direct the investigation and choosing the corresponding signal for alerts. The Availability metric is unsupported for serverless compute and displays 100 percent, so that value cannot establish serverless connection health.

Trap Using a 100 percent Availability chart to close a firewall-related client incident, which the metric can classify as available despite failed connections.

3 questions test this
An actionable alert pairs the right metric with a meaningful evaluation window

Configure an Azure Monitor metric alert with the SQL resource scope, signal, aggregation, threshold, evaluation window, and action group appropriate to the condition. A maximum can expose brief saturation that an average conceals, while a total is useful for counting events such as failures over a period. Route notifications through the alert's action group to reach the responsible operator or automation. Metric alerts are stateful by default: a fired alert remains active until resolved rather than sending a fresh firing notification on every evaluation of the same continuing condition.

Trap Enabling automatic tuning as the notification mechanism for connection failures, because tuning recommendations do not define the required connection-failure alert and recipient.

3 questions test this
Dynamic thresholds need historical data before they can detect deviations

Dynamic-threshold metric alerts learn normal patterns and cannot trigger before collecting three days and at least 30 samples of data. Use a suitable static threshold when a known hard boundary must be monitored before that history exists.

Trap Using a newly enabled dynamic-threshold rule for immediate protection of a new resource before sufficient historical samples exist.

4 questions test this
Watcher analysis must distinguish observation time, collection time, and replica identity

Use sample_time_utc for when a watcher observation occurred; collection_time_utc, where present, records when the watcher retrieved it and can be shared by several older samples. Filter or group by the target and replica identity so primary and secondary measurements are not mixed. For cumulative counters such as waits, compare successive samples from the same series and exclude resets before interpreting interval activity.

Trap Summing lifetime cumulative wait counters across samples as interval wait activity, which repeatedly counts earlier waits.

4 questions test this
Pool-level watcher data does not contain every database-level detail

An existing elastic-pool watcher target connects through an anchor database and provides pool-wide metrics plus selected per-database resource and request metrics. Query runtime statistics, database properties, and table or index metadata require individual database targets, while some pool resource and wait information cannot be attributed to a single database. Check which targets were already monitored before concluding that missing query detail or shared-resource pressure is absent.

Trap Diagnosing shared pool contention from only an individual database's query statistics, which does not supply the complete pool-level resource picture.

4 questions test this
Existing watchers continue temporarily but their alerts and stores need a transition plan

Database watcher is a preview retiring on April 30, 2027; since November 9, 2026, new watchers cannot be created and existing watchers cannot be edited. Existing watchers continue collecting until retirement, when they stop and cannot be restarted. Their Azure Monitor log search alerts query the retained data store and are not automatically deleted, and retirement does not deprovision that store, so recreate and test needed monitoring before retirement and retire obsolete rules and retained resources deliberately.

5 questions test this
An existing database watcher centralizes detailed Azure SQL telemetry

Database watcher collects performance, configuration, and health data from SQL system views into a central Azure Data Explorer or Fabric Real-Time Analytics data store. For an already monitored Azure SQL estate, use its dashboards to compare databases, elastic pools, and managed instances, then drill into the affected resource. Query the retained store with KQL or T-SQL when a custom cross-resource investigation needs more detail than the dashboard provides.

Trap Using Query Performance Insight as a complete managed-instance estate monitor, although that portal feature targets query analysis for Azure SQL Database rather than the watcher's supported estate scope.

4 questions test this
Watcher sampling intervals and collection gaps affect incident interpretation

Check ingestion statistics and collection continuity before interpreting an empty watcher chart as proof that the target was idle. Database watcher runs periodic T-SQL queries at dataset-specific frequencies, such as ten seconds for rapidly changing counters and five minutes for relatively static configuration. Application workloads take priority, so contention, short monitoring lock timeouts, or termination of monitoring sessions can create gaps; collected data is typically available for analysis in less than ten seconds.

Trap Treating every missing monitoring sample as a database outage, although monitoring queries can lose priority while the application continues working.

4 questions test this
A collecting watcher does not prove its log-search alert can evaluate or notify

When an existing watcher keeps collecting but expected alerts are absent, inspect the scheduled log-search rule's KQL query, evaluation window, target filtering, and managed identity because evaluation queries the retained data store rather than the SQL target directly. The alert-rule identity needs its own data-store access, separate from the collecting watcher; for an Azure Data Explorer store, verify Reader on the cluster and Viewer on the database. Check the per-target dimensions and resulting alert instances to identify the affected database, elastic pool, or managed instance rather than treating the estate as one undifferentiated condition. If an alert instance fired but delivery failed, follow the action-group delivery requirement in dp-300-f-30011 rather than changing the evaluation query.

Trap Reauthorizing only the collecting watcher's SQL identity to fix alert evaluation, which leaves the alert rule's separate data-store identity unchanged.

4 questions test this
Extended Events session scope depends on the SQL platform

Azure SQL Database event sessions use ON DATABASE and collect events only within that database's scope. SQL Server uses ON SERVER, while Azure SQL Managed Instance supports both scopes and generally favors server-scoped sessions. Select the scope that contains the incident rather than expecting one Azure SQL Database session to observe neighboring databases on its logical server.

Trap Reusing an instance-wide ON SERVER session definition on an Azure SQL Database logical server, which is not a customer-managed SQL Server instance.

5 questions test this
Events, actions, and predicates serve different capture purposes

Choose an Extended Events event for the execution point to observe, such as sql_batch_completed for completed batches. Add an action such as sqlserver.session_id to collect contextual information, and use a predicate to retain only events meeting the investigation's conditions. This combination captures useful evidence while reducing unnecessary event volume and monitoring overhead. Discover supported events, actions, and targets by joining sys.dm_xe_objects.package_guid to sys.dm_xe_packages.guid and filtering object_type to event, action, or target, rather than assuming every platform exposes the same objects. For correlating related events across different server connections, use TRACK_CAUSALITY = ON rather than relying on the session_id action or timestamps alone.

Trap Adding a target to supply session_id context, although a target consumes captured events rather than adding an action's contextual field.

4 questions test this
An event session definition and a running session are different states

Extended Events catalog views describe session definitions, whereas Extended Events DMVs describe started sessions and their runtime targets. Start a defined session to begin collection, and use STARTUP_STATE = ON for a session that should resume after an engine restart. Use STARTUP_STATE = OFF for a short-lived investigation and inspect the appropriate server- or database-scoped runtime views to confirm it is actually running. Before defining redundant collection, inspect the automatically started system_health session on SQL Server or Managed Instance for evidence such as deadlocks and serious errors; Azure SQL Database has no built-in system_health session. A running session can still buffer events before target delivery: MAX_DISPATCH_LATENCY defaults to 30 seconds, a shorter interval starts flushing sooner, and INFINITE flushes only when buffers fill or the session closes, so delayed target output alone does not establish that collection stopped.

Trap Using MAX_DISPATCH_LATENCY = INFINITE for prompt target output from a low-volume session, which can leave events buffered until buffers fill or the session closes.

3 questions test this
An event_file target preserves evidence that an in-memory ring buffer cannot

Use an event_file target when captured Extended Events data must survive the session for later analysis; use ring_buffer for recent in-memory events during an ad hoc investigation. Azure SQL Database and Managed Instance event_file targets write to Azure Storage blobs, whereas SQL Server can use local files or blobs, so a VM's local storage path is not automatically suitable for a managed Azure SQL service. Read event files with the SSMS event viewer or sys.fn_xe_file_target_read_file, using a blob URL for Azure SQL event files. For a ring buffer, cast target_data to XML from sys.dm_xe_session_targets for a server-scoped session or sys.dm_xe_database_session_targets for a database-scoped session, joining the corresponding runtime sessions view to select the intended session. Even while the session runs, ring-buffer XML output can be truncated; set the ring_buffer target MAX_MEMORY to 1,024 KB or less to reduce this output-truncation risk, which is distinct from event loss in session buffers.

Trap Choosing an in-memory ring buffer as the durable incident archive, which does not provide event-file persistence.

4 questions test this
An Azure event-file container must meet the engine's access and storage requirements

For an Azure SQL event_file target, grant the server or managed-instance identity Storage Blob Data Contributor on the container and create the corresponding credential, or store an appropriate container SAS in a credential. A SAS requires Read, Write, Delete, and List permissions and a validity period covering the session; use a database-scoped credential in Azure SQL Database and a server-scoped credential in Managed Instance, because portal access held by the operator is not the engine's storage authorization. Use Hot-tier blobs in a same-region storage account without hierarchical namespace, aligning redundancy with the SQL resource: LRS, GRS, or RA-GRS for locally redundant resources, and ZRS, GZRS, or RA-GZRS for zone-redundant resources. Restrict reader access through storage RBAC, including inherited access, because captured events can contain sensitive query data.

Trap Granting the interactive administrator Reader on the storage resource to authorize event writes, which neither gives the engine a credential nor grants blob data-write access.

4 questions test this
New Database Engine traces should use Extended Events rather than SQL Trace

Choose Extended Events for new Database Engine tracing because SQL Trace and the SQL Trace-based SQL Server Profiler are deprecated. SQL Server Profiler does not support Azure SQL Database, so granting ALTER TRACE or sysadmin does not repair that unsupported connection. SSMS XEvent Profiler is a different tool built on Extended Events, providing a live event viewer on SQL Server and Managed Instance rather than a SQL Trace capture.

Trap Using an ALTER TRACE grant to resolve SQL Server Profiler's Azure SQL Database connection error, which reports a permissions-style message for an unsupported target.

4 questions test this
Extended Events management and evidence viewing have separate SQL permission boundaries

Delegate session administration with ALTER ANY EVENT SESSION for server-scoped SQL Server or Managed Instance sessions, or ALTER ANY DATABASE EVENT SESSION for Azure SQL Database sessions, separately from the engine's blob-write authorization. For creation alone, SQL Server 2022 and later and Managed Instance support CREATE ANY EVENT SESSION, while Azure SQL Database supports CREATE ANY DATABASE EVENT SESSION. Reading server-scoped runtime target data through sys.dm_xe_session_targets requires VIEW SERVER STATE, with SQL Server 2022 and later requiring VIEW SERVER PERFORMANCE STATE. Reading sys.dm_xe_database_session_targets in Azure SQL Database requires VIEW DATABASE STATE; these viewing permissions do not themselves authorize changing a session definition.

Trap Using ALTER ANY EVENT SESSION for a monitoring reader who only needs target data, which delegates session changes instead of the narrower viewing capability.

4 questions test this
Event retention policy trades capture completeness against workload stalls

Where supported, NO_EVENT_LOSS makes event-firing tasks wait for buffer space and can stall user queries, trading workload responsiveness for capture completeness. ALLOW_SINGLE_EVENT_LOSS can drop an event when session buffers are full, whereas ALLOW_MULTIPLE_EVENT_LOSS can discard full buffers containing multiple events. Session MAX_MEMORY, which defaults to 4 MB, controls event buffering separately from the ring_buffer target's MAX_MEMORY. In Azure SQL Database, session-memory budgets are 128 MB per database and 512 MB across an elastic pool, with at most 100 started sessions per database and 100 across a pool; a memory-limit start failure can occur below that count, so run fewer concurrent sessions or reduce session MAX_MEMORY. For event_file targets in Azure SQL Database and in Managed Instance with the SQL Server 2025 or Always-up-to-date update policy, specifying NO_EVENT_LOSS produces warning 25665 and uses ALLOW_SINGLE_EVENT_LOSS instead, so it does not promise lossless blob capture.

Trap Using a larger ring_buffer target MAX_MEMORY to cure full session event buffers, which changes target storage rather than the session buffering allocation.

4 questions test this

Also tested in

References

  1. https://learn.microsoft.com/en-us/azure/azure-sql/database/monitoring-metrics-alerts
  2. https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-pool-overview
  3. https://learn.microsoft.com/en-us/azure/azure-sql/database/logical-servers
  4. https://learn.microsoft.com/en-us/azure/azure-sql/database/monitoring-with-dmvs
  5. https://learn.microsoft.com/en-us/credentials/certifications/resources/study-guides/dp-300
  6. https://learn.microsoft.com/en-us/sql/relational-databases/performance/establish-a-performance-baseline
  7. https://learn.microsoft.com/en-us/sql/relational-databases/performance/monitor-and-tune-for-performance
  8. https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-resource-stats-azure-sql-database
  9. https://learn.microsoft.com/en-us/azure/azure-monitor/metrics/metrics-aggregation-explained
  10. https://learn.microsoft.com/en-us/azure/azure-sql/database/alerts-create
  11. https://learn.microsoft.com/en-us/azure/azure-monitor/platform/diagnostic-settings
  12. https://learn.microsoft.com/en-us/azure/azure-monitor/fundamentals/activity-log
  13. https://learn.microsoft.com/en-us/azure/azure-sql/database/metrics-diagnostic-telemetry-logging-streaming-export-configure
  14. https://learn.microsoft.com/en-us/azure/azure-monitor/platform/migrate-to-azure-storage-lifecycle-policy
  15. https://learn.microsoft.com/en-us/sql/relational-databases/performance-monitor/monitor-resource-usage-system-monitor
  16. https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-performance-counters-transact-sql
  17. https://learn.microsoft.com/en-us/sql/relational-databases/performance-monitor/sql-server-sql-statistics-object
  18. https://learn.microsoft.com/en-us/azure/azure-monitor/reference/supported-metrics/microsoft-compute-virtualmachines-metrics
  19. https://learn.microsoft.com/en-us/azure/azure-sql/database/resource-limits-logical-server
  20. https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-requests-transact-sql
  21. https://learn.microsoft.com/en-us/sql/relational-databases/performance-monitor/sql-server-buffer-manager-object
  22. https://learn.microsoft.com/en-us/training/modules/describe-performance-monitoring/4-establish-baseline-metrics
  23. https://learn.microsoft.com/en-us/sql/relational-databases/performance-monitor/sql-server-memory-manager-object
  24. https://learn.microsoft.com/en-us/azure/virtual-machines/disks-metrics
  25. https://learn.microsoft.com/en-us/azure/virtual-machines/b-series-cpu-credit-model/b-series-cpu-credit-model
  26. https://learn.microsoft.com/en-us/azure/azure-sql/database-watcher-alerts
  27. https://learn.microsoft.com/en-us/azure/azure-monitor/alerts/alerts-dynamic-thresholds
  28. https://learn.microsoft.com/en-us/azure/azure-sql/database-watcher-overview
  29. https://learn.microsoft.com/en-us/azure/azure-sql/database-watcher-data
  30. https://learn.microsoft.com/en-us/sql/relational-databases/extended-events/extended-events
  31. https://learn.microsoft.com/en-us/sql/tools/sql-server-profiler/sql-server-profiler
  32. https://learn.microsoft.com/en-us/sql/relational-databases/extended-events/use-the-ssms-xe-profiler
  33. https://learn.microsoft.com/en-us/sql/relational-databases/extended-events/quick-start-extended-events-in-sql-server
  34. https://learn.microsoft.com/en-us/sql/t-sql/statements/create-event-session-transact-sql
  35. https://learn.microsoft.com/en-us/sql/relational-databases/extended-events/sql-server-extended-events-packages
  36. https://learn.microsoft.com/en-us/azure/azure-sql/database/xevent-db-diff-from-svr
  37. https://learn.microsoft.com/en-us/sql/relational-databases/extended-events/use-the-system-health-session
  38. https://learn.microsoft.com/en-us/sql/relational-databases/extended-events/targets-for-extended-events-in-sql-server
  39. https://learn.microsoft.com/en-us/azure/azure-sql/database/xevent-code-event-file
  40. https://learn.microsoft.com/en-us/azure/azure-sql/database/xevent-code-ring-buffer
  41. https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-xe-database-session-targets-azure-sql-database
  42. https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/credentials-database-engine
  43. https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-xe-session-targets-transact-sql