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 scope is the boundary it measures: one database, an elastic pool (databases on a single server that share a set number of resources)[2], every database on a logical server (the central administrative point for a collection of databases)[3], a VM, or a single disk.
- The denominator is what 100 percent means. CPU percentage is measured toward the user workload limit, while SQL instance CPU percent covers user and system workloads on a different scale[1], so a reading of one says little about the other (Interpreting Azure SQL Database compute metrics explains the two limits).
- The time window is how finely the signal samples and how long it keeps data.
sys.dm_db_resource_statsrecords 15-second intervals and keeps them for about an hour[4], so it cannot show what happened yesterday.
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]:
- A Log Analytics workspace lets you analyze SQL telemetry with other monitoring data and use Azure Monitor features such as alerts and visualizations; logs there are queried with Kusto Query Language (KQL)[11].
- Azure Event Hubs streams metrics and resource logs to third-party security information and event management (SIEM) or log analytics tools, or into a custom telemetry platform; the event hub must be in the same region as the database and server[13].
- Azure Storage archives large volumes of telemetry for a fraction of the cost of the other two options.
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.
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:
- On B-series VMs, credits accumulate while the workload runs below the base CPU performance and are spent above it; once all credits are consumed, the VM is throttled back to its base CPU performance until it accumulates credits again[25]. CPU Credits Remaining and CPU Credits Consumed track this, and they exist only on B-series VMs.
- For premium disks that burst, Data Disk Used Burst IO Credits Percentage and Data Disk Used Burst BPS Credits Percentage (BPS: bytes per second) report the accumulated percentage of the IOPS burst and the throughput burst already used, emitted every five minutes[24], and the VM Cached and VM Uncached Used Burst IO and BPS Credits Percentage metrics report the same at VM scope. Data Disk Target IOPS and Data Disk Target Bandwidth give the disk's limits without bursting.
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.
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:
- Scope is the resource the rule watches. For Azure SQL Database it can be a single database, an elastic pool, or all databases or elastic pools in a resource group, or in a subscription within one region or across all regions[1]; a broad scope avoids sprawl of many independent alerts[10].
- Condition is the signal (for example CPU percentage), the threshold type (static or dynamic), the operator, the aggregation type (Total, Count, Average, Minimum, or Maximum), and the Check every and Lookback period settings[10]: Check every sets the evaluation frequency, and the rule evaluates aggregated metric values over the lookback period and compares them with the threshold[1].
- Actions come from the action group linked to the rule, which can send an email, an SMS, or a voice notification and trigger webhooks, automation runbooks, functions, or logic apps[1].
- Details name the rule and set its severity; Microsoft's recommended alert rules[1] suggest a severity for each example.
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.
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.
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
WHEREcondition; 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_fileorring_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.
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]:
- 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.
- 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
Hotblob 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_statsin that database. A request snapshot shows only work still running, andsys.resource_statslags by several minutes (Built-in signal sources for Azure SQL Database). - Last week's trend:
sys.resource_stats, orsys.elastic_pool_resource_statsfor a pool, inmaster; 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_filewith a blob credential the engine can use; aring_bufferloses its output when the session stops. - A reader who only needs ring buffer output in Azure SQL Database:
VIEW DATABASE STATE, notALTER 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
| Property | Azure Monitor metrics | Resource-statistics views | Diagnostic settings | Performance Monitor | Database watcher | Extended Events |
|---|---|---|---|---|---|---|
| Scope | Azure SQL databases and elastic pools; host metrics for a SQL Server VM | One 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 on | SQL Server and Windows on the VM | Selected databases, elastic pools, and managed instances | One database (Azure SQL Database) or the instance (SQL Server, Managed Instance) |
| What it records | Numeric time series such as CPU percentage and Availability | CPU, data IO, log write, worker, and memory percentages | Selected resource-log categories and metrics, exported | SQL Server counter objects and operating-system counters | Samples from SQL system views | Individual events with the actions you choose |
| Time window | Typically one-minute values, with metric-specific exceptions, aggregated to the chart granularity or the alert rule's lookback period | 15-second samples for about an hour, or 5-minute aggregates for about 14 days | Set by the destination's retention policy or workspace tier | Updated at a frequency you define | Dataset-specific, such as 10 seconds for performance counters | Until the session stops (ring_buffer) or while the blobs or files are kept (event_file) |
| Reach for it when | Charting and alerting on resource consumption | Diagnosing a recent spike or a two-week trend | Querying with KQL, streaming to third-party tools, or archiving | Correlating engine and host activity on a SQL VM | Comparing an already monitored estate before April 30, 2027 | Capturing specific executions and their context |
Decision tree
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
- You have an Azure SQL Managed Instance named SQLMI1 that hosts db1. A nightly maintenance window of three hours must contain both the full backup and a test restore of db1 to a separate database. The
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1 that hosts an order-processing database used by staff in two time zones. You plan to tune SQLVM1 next quarter. You need to create
- You have an Azure SQL Managed Instance named SQLMI1. Your performance baseline, recorded while the system was healthy, shows that the nightly inventory queries in db2 finish in 12 minutes. Since a sch
- 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
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1 that hosts db1 for a call-center app. Agents complain that screens are slow to start showing results, while the nightly batch job
- You have an Azure SQL Database named db1. Its baseline records response times measured with about 250 concurrent users of a reporting app. After an index change, a test with 25 concurrent users shows
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A nightly job must process 2 million orders within a three-hour window, and no user waits on individual results. You are adding a
- 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
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1 that runs Windows Server. You need to chart Batch Requests/sec and Page life expectancy every 15 seconds alongside the Windows dis
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. Performance Monitor shows that SQL Compilations/sec spikes every few minutes. You need to identify the individual statements that
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1 that runs Windows Server and has heavy disk activity. You plan to record Buffer Manager and SQL Statistics counters together with
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1 that runs Windows Server. CPU peaks at 90 percent several times a day. You need to determine whether the peaks rise with the volum
- 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
- You have an elastic pool named Pool1 that contains 15 Azure SQL databases. Your monitoring account can connect only to the database named db3 in Pool1 and can't connect to master. You need recent, gra
- You have an Azure SQL Database named db1. Six days ago, between 14:00 and 15:00, users reported slow responses. You need to review db1's CPU, data I/O, and log write percentages for that hour. You can
- You manage an elastic pool named Pool4 that hosts 12 Azure SQL databases. A dashboard query runs SELECT avg_cpu_percent FROM sys.dm_elastic_pool_resource_stats in a pooled database named db7 and label
- You have an Azure SQL logical server named SQL1 that hosts three elastic pools. Finance asks whether Pool2 has consistently used less than half of its resources over the last 10 days so that it can be
- 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
- You have an Azure SQL Managed Instance named SQLMI1 that hosts 20 databases. Auditors require the instance's vCore count, average CPU percentage, I/O requests, and reserved and used storage space to b
- You have an elastic pool named Pool1 that contains 10 Azure SQL databases. You need to send Pool1's Basic metrics and every pooled database's Errors and Deadlocks logs to a Log Analytics workspace. Yo
- You have an Azure SQL Database named db1. A diagnostic setting streams Basic metrics and the Errors category to a Log Analytics workspace. Analysts must also chart tempdb data file size and tempdb per
- You have an Azure SQL Managed Instance named SQLMI1 that hosts a database named db1. You open the diagnostic settings of db1 to stream deadlock, blocking, and timeout telemetry to a Log Analytics work
- 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
- You have an Azure SQL Database named db1 in the Standard service tier. A reporting script reads sys.dm_db_resource_stats in db1 and already returns avg_cpu_percent, avg_data_io_percent, and avg_log_wr
- You have an Azure SQL Database named db1 in the Premium service tier. Order-entry latency doubles every morning while the DTU percentage metric holds near 95 percent. A developer wants to rewrite the
- You have an Azure SQL Database named db1 in the Standard service tier. A custom dashboard calculates DTU percentage as the highest of avg_cpu_percent, avg_data_io_percent, avg_log_write_percent, and a
- You have an Azure SQL Database named db3 in the Standard service tier. A metric alert on DTU percentage above 90 percent pages the on-call engineer several times a week. Each time, the engineer must o
- 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
- You have an Azure SQL Database named db1 on a small vCore compute size. While a geo-replica of db1 is being created, application latency rises. CPU percentage stays at 45 percent, but SQL instance CPU
- You have an Azure SQL Database named db1 that uses the vCore purchasing model. During a promotion, query latency doubles. Azure Monitor shows CPU percentage at 100 percent for 30 minutes, while SQL in
- You have an Azure SQL Database named db1 on a small vCore compute size. Users report slow queries during the nightly backup window, although CPU percentage stays near 40 percent. You are connected to
- You have an Azure SQL Database named db1. Since a new release, average query duration has risen 35 percent. During business hours, CPU percentage averages 82 percent and SQL instance CPU percent avera
- 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
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. Users report slower reports since a new data load process started. You plan to use Performance Monitor counters and compare them
- You have an Azure SQL Database named db1 that has served a steady OLTP workload for two weeks. SQL instance memory percent stays between 96 and 98 percent. sys.dm_os_out_of_memory_events returns no ro
- You have an Azure SQL Database named db1 on a small compute size. Some reporting queries fail intermittently with insufficient memory errors while memory utilization is only 70 percent. A developer pr
- 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
- You have an Azure SQL Database named db1 that serves parallel reporting queries. The application's connection pool is capped at 60 connections. Last week, during a reporting burst, new requests were r
- You have an Azure SQL Database named db1. During peak hours, Workers percentage reaches 100 percent and some requests are rejected. A developer proposes raising the maximum connection pool size in the
- You have an Azure SQL Database named db1 with MAXDOP set to 0. During a report burst, clients receive error 10928 stating that the request limit has been reached, although the application opens only 1
- 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
- You have an Azure SQL Database named db1. You delete 200 GB of archived rows from db1. Afterward, the Data space used metric falls by about 200 GB, but Data space allocated remains unchanged. You need
- You have an Azure SQL Database named db1 on a vCore service objective that supports up to 1 TB of data. db1 was created with a 250 GB maximum data size. The Low data space alert fires because Data spa
- You have an Azure SQL Database named db1. Large sort and hash operations from a nightly job have filled the tempdb transaction log twice this month, causing the job to fail. The data files of db1 are
- 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
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. The transaction log file is on a data disk at LUN 3. Commits are slow, but Data Disk IOPS Consumed Percentage for LUN 3 is only 7
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. During nightly index rebuilds, metrics split by LUN show Data Disk Bandwidth Consumed Percentage at 100 percent and Data Disk IOP
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1 with four premium data disks on LUN 0 through LUN 3. Data Disk IOPS Consumed Percentage averages 45 percent, yet queries that read
- 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
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. After an online sales campaign starts, Performance Monitor shows Batch Requests/sec rising from 900 to 2,700 and SQL Compilations
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A monitoring script queries sys.dm_os_performance_counters for the Batch Requests/sec counter in the SQL Statistics object and ch
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. Over a stable workload period, Batch Requests/sec averages 500, SQL Compilations/sec averages 400, and SQL Re-Compilations/sec av
- 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
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A premium SSD data disk on LUN 2 uses credit-based bursting. After a 40-minute backup to that disk, throughput in MB/s drops to a
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1 on a B-series burstable VM. Query performance was good for the first two hours of a month-end run and then dropped sharply. CPU Cr
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1 whose four premium SSD data disks use no host caching. After a 30-minute ETL burst, IOPS drop. VM Uncached Used Burst IO Credits P
- 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
- You have an Azure SQL Database named db1. After a firewall change, a branch office's clients can't connect for two hours, yet db1's Availability metric shows 100 percent for every minute because other
- You have a serverless Azure SQL Database named db2. Users report intermittent login failures during the morning, but the Availability metric for db2 shows a flat 100 percent line. A manager wants to c
- You have an Azure SQL Database named db1 on provisioned compute. At 09:15, a deployment rotated the application's password, but one web server kept the old password. In the minute starting at 09:20, e
- 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
- You have an Azure SQL Database named db1. CPU saturates at 100 percent for one or two minutes several times an hour, causing timeouts. An alert rule that uses Average aggregation of CPU percentage ove
- You have an Azure SQL Database named db1. Clients occasionally fail to connect because of internal service errors. You need a metric alert that warns when more than 10 such failed connection attempts
- You have an Azure SQL Database named db1. An existing metric alert rule on db1 fires correctly and its alerts appear on the Azure Monitor Alerts page, but the on-call engineers never receive an email
- 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
- You have an Azure SQL Database named db2 that was created yesterday. You added a metric alert rule for Deadlocks that uses a dynamic threshold with Medium sensitivity. The application already produced
- You have an Azure SQL Database named db1 whose connection volume peaks every Monday morning. A Successful Connections alert rule with a dynamic threshold has 10 days of history and still fires on most
- You have an Azure SQL Database named db4 that was created on Monday at 08:00. At 09:00 the same day, you create a metric alert rule with a dynamic threshold on CPU percentage, which is emitted every m
- You have an Azure SQL Database named db9 that was created this morning by restoring a template database. Policy requires an alert starting today whenever Workers percentage exceeds 80 percent for five
- 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
- You have a Business Critical Azure SQL Database named db6 monitored by an existing database watcher. The estate heatmap shows two hexagons for db6. A KQL query that averages CPU utilization for db6 re
- You have an existing database watcher that monitors an Azure SQL Database named db1. You are writing a KQL query against the sqldb_database_resource_utilization dataset to find the exact times when CP
- You have an Azure SQL Managed Instance named SQLMI1 monitored by an existing database watcher. An analyst sums the wait time column of every sqlmi_wait_stats row for one day and reports total waits th
- You have a Hyperscale Azure SQL Database named db5 that has two high availability secondary replicas and is monitored by an existing database watcher. A KQL query filters sqldb_database_resource_utili
- 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
- You have an elastic pool named Pool2 that an existing database watcher has monitored for six months as a SQL elastic pool target through an empty anchor database. None of the pooled databases is a dat
- You have an elastic pool named Pool3 that contains 30 databases and is monitored by an existing database watcher through an anchor database. Pool3's CPU is saturated. A colleague reviews the query sta
- You are documenting an existing database watcher before its retirement. It monitors a dense elastic pool of 200 small databases as one elastic pool target instead of adding each database. A reviewer a
- You have an Azure SQL Database named db2 in an elastic pool, and db2 is a database target of an existing database watcher. An analyst expected the sqldb_database_wait_stats dataset for db2 to show all
- 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
- Operators depend on nine log search alert rules that your team created from database watcher templates for blocked requests and low transaction log storage. You are planning the move away from databas
- On December 1, 2026, a colleague tries to add an Azure SQL Managed Instance to your organization's existing database watcher and to change the watcher's data store. Both changes fail. The watcher's da
- In June 2027, after database watcher retired, a compliance review needs CPU utilization for an Azure SQL Database named db1 from February 2027. The watcher's Azure Data Explorer database has a one-yea
- Your organization's existing database watcher monitors 18 Azure SQL databases. On November 10, 2026, a new single database named db40, which isn't in an elastic pool, goes into production and needs pe
- Your organization used database watcher with a dedicated Azure Data Explorer cluster as its data store. On May 5, 2027, after the retirement date, finance reports that the cluster is still billing. Yo
- 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
- You have an existing database watcher that monitors the Azure SQL databases of three business units across two subscriptions. You need to rank the queries that consume the most CPU across all monitore
- Your organization has an existing database watcher that monitors 40 Azure SQL databases, three elastic pools, and two Azure SQL Managed Instances, with an Azure Data Explorer data store. You need to s
- You have an existing database watcher that stores its monitoring data in an Azure Data Explorer database. An analyst needs an ad hoc analysis that correlates wait statistics with CPU utilization for s
- You have an existing database watcher that stores data from 20 Azure SQL databases in an Azure Data Explorer data store. Management wants one custom dashboard that combines the watcher's CPU and wait
- 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
- You have an Azure SQL Database named db2 monitored by an existing database watcher. Every night at 01:00, a SQL Agent-style automation job kills sessions that have run longer than 10 minutes, and the
- You have an Azure SQL Database named db5 monitored by an existing database watcher. During a period of heavy blocking and CPU near 100 percent, the watcher shows larger than expected intervals between
- You have an Azure SQL Database named db3 monitored by an existing database watcher. At 10:00 you change a database-scoped configuration on db3. At 10:01 the resource dashboard still shows the old sett
- You have an Azure SQL Database named db7 monitored by an existing database watcher. The resource dashboard shows no data points for db7 during the last 20 minutes, and a manager wants to declare an ou
- 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
- You have an existing database watcher with an Azure Data Explorer data store. A user-assigned managed identity named AlertId2 was set as the default alert rule identity by a user who doesn't have the
- You have an existing database watcher. After a colleague edited the KQL query of an alert rule that was created from a watcher template, its fired alerts list the Azure Data Explorer cluster as the af
- Your existing database watcher monitors Azure SQL databases and two Azure SQL Managed Instances. Its only CPU alert rule was created from the High CPU utilization template for the SQL database target
- You have an existing database watcher where db8 is a SQL database target that appears on the estate dashboard. Last month, a colleague customized the KQL query of the high CPU alert rule. The rule now
- 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
- You have an Azure SQL Database named db1. A script that worked on SQL Server runs the following statement in db1 and fails: CREATE EVENT SESSION s1 ON SERVER ADD EVENT sqlserver.sql_batch_completed; Y
- You have an Azure SQL logical server named SQL1 that hosts three databases named db1, db2, and db3. You need to capture deadlock events from all three databases by using Extended Events. The solution
- You have an Azure SQL Managed Instance named SQLMI1 that hosts 25 user databases. You need to capture lock escalation events from every database on SQLMI1 by using Extended Events. The solution must f
- You are moving monitoring scripts from SQL Server to an Azure SQL Database named db1. One script lists defined and running Extended Events sessions by querying sys.server_event_sessions and sys.dm_xe_
- You have an Azure SQL logical server named SQL1 that hosts db1 and db2. You create an Extended Events session in db1 that captures sql_batch_completed events without a predicate. The session runs, but
- 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
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. You create an event session that captures the sqlserver.sql_batch_completed event. Analysts must see which session ran each batch
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. An application uses separate connections to submit an order and then update inventory. You are designing an event session to diag
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. You plan to build an event session for tempdb spills, but you don't know the exact names of the relevant events or the packages t
- You have an Azure SQL Database named db1 that runs thousands of batches per minute. You create an event session that captures sql_batch_completed events to an event_file target, but the files grow qui
- 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
- You have an Azure SQL Managed Instance named SQLMI1. Last night, an application reported several deadlock errors. No user-defined Extended Events sessions exist on SQLMI1. You need the deadlock graphs
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1 with 15 event session definitions. A health script must report which of these sessions are currently running so operators can rest
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. An event session writes deadlock reports to an event_file target. Operators watch the file and report that a deadlock often takes
- 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
- You have an Azure SQL Database named db1. An event session in db1 writes .xel files to a blob container, and a database-scoped credential for the container already exists. Analysts must query the capt
- You have an Azure SQL Database named db1. A running event session named s1 in db1 uses a ring_buffer target for a short troubleshooting exercise. You hold VIEW DATABASE STATE in db1. You need to retri
- You have an Azure SQL Database named db1. Compliance requires that captured login failure events be kept for 30 days and remain available after the session is stopped. You are adding a target to an Ex
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. An event session uses a ring_buffer target with MAX_MEMORY = 8192 KB. When you read the target_data XML, the output is often cut
- 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
- You have an Azure SQL Database named db1. You create a database-scoped credential named https://st1.blob.core.windows.net/xe/ and an event session in db1 with an event_file target in the xe container.
- You have an Azure SQL Database named db1. An event session in db1 writes to an event_file target in a blob container by using a SAS token stored in a database scoped credential named for the container
- You have an Azure SQL Database named db1 on a logical server that has a system-assigned managed identity. An event session in db1 must write to a blob container by using managed identity authenticatio
- You have an Azure SQL Database named db1 on a logical server whose managed identity holds Storage Blob Data Contributor on a container in a storage account named st2. The credential is named for the c
- 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
- Your organization is writing a monitoring standard for SQL Server on Azure Virtual Machines and Azure SQL Managed Instance. Several teams still create server-side traces with SQL Trace stored procedur
- You have an Azure SQL Database named db1. A developer wants to trace the statements an application sends to db1 during a test run and starts SQL Server Profiler, but the connection fails with a mislea
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. During incidents, DBAs open the T-SQL session of the SSMS XEvent Profiler to watch incoming statements. A security review flags t
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1. A DBA needs a quick live view of incoming queries during a short incident, similar to the old Profiler experience. The solution m
- 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
- You have an Azure SQL Database named db1. A database-scoped event session with a ring_buffer target runs in db1. An analyst named User1 must query sys.dm_xe_database_session_targets in db1 to read the
- You have an Azure SQL Database named db1. A support engineer named User2 must be allowed to create new database-scoped event sessions in db1 for an investigation. Another DBA owns the existing session
- You have a SQL Server 2022 instance on an Azure virtual machine named SQLVM1. A monitoring account named Mon1 must read the target data of running server-scoped event sessions by querying sys.dm_xe_se
- You have an Azure SQL Managed Instance named SQLMI1. A junior DBA must be able to create, start, stop, and modify server-scoped event sessions on SQLMI1 during troubleshooting. The DBA must not be add
- 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
- You have an Azure SQL Database named db1 with 12 started event sessions, each created with MAX_MEMORY = 10 MB. Starting a 13th session fails with an error that references a memory limit. A colleague s
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1 that processes 20,000 batches per second. You need an event session that samples lock acquisitions for trend analysis in productio
- You have an Azure SQL Database named db1. An auditor asks you to prove that an Extended Events session with an event_file target in Azure Storage captures every login failure. You created the session
- You have a SQL Server on Azure Virtual Machines instance named SQLVM1 in a test environment. During a one-hour compliance test, an event session that writes to a local event_file target must not lose
Also tested in
References
- https://learn.microsoft.com/en-us/azure/azure-sql/database/monitoring-metrics-alerts
- https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-pool-overview
- https://learn.microsoft.com/en-us/azure/azure-sql/database/logical-servers
- https://learn.microsoft.com/en-us/azure/azure-sql/database/monitoring-with-dmvs
- https://learn.microsoft.com/en-us/credentials/certifications/resources/study-guides/dp-300
- https://learn.microsoft.com/en-us/sql/relational-databases/performance/establish-a-performance-baseline
- https://learn.microsoft.com/en-us/sql/relational-databases/performance/monitor-and-tune-for-performance
- https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-resource-stats-azure-sql-database
- https://learn.microsoft.com/en-us/azure/azure-monitor/metrics/metrics-aggregation-explained
- https://learn.microsoft.com/en-us/azure/azure-sql/database/alerts-create
- https://learn.microsoft.com/en-us/azure/azure-monitor/platform/diagnostic-settings
- https://learn.microsoft.com/en-us/azure/azure-monitor/fundamentals/activity-log
- https://learn.microsoft.com/en-us/azure/azure-sql/database/metrics-diagnostic-telemetry-logging-streaming-export-configure
- https://learn.microsoft.com/en-us/azure/azure-monitor/platform/migrate-to-azure-storage-lifecycle-policy
- https://learn.microsoft.com/en-us/sql/relational-databases/performance-monitor/monitor-resource-usage-system-monitor
- https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-performance-counters-transact-sql
- https://learn.microsoft.com/en-us/sql/relational-databases/performance-monitor/sql-server-sql-statistics-object
- https://learn.microsoft.com/en-us/azure/azure-monitor/reference/supported-metrics/microsoft-compute-virtualmachines-metrics
- https://learn.microsoft.com/en-us/azure/azure-sql/database/resource-limits-logical-server
- https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-requests-transact-sql
- https://learn.microsoft.com/en-us/sql/relational-databases/performance-monitor/sql-server-buffer-manager-object
- https://learn.microsoft.com/en-us/training/modules/describe-performance-monitoring/4-establish-baseline-metrics
- https://learn.microsoft.com/en-us/sql/relational-databases/performance-monitor/sql-server-memory-manager-object
- https://learn.microsoft.com/en-us/azure/virtual-machines/disks-metrics
- https://learn.microsoft.com/en-us/azure/virtual-machines/b-series-cpu-credit-model/b-series-cpu-credit-model
- https://learn.microsoft.com/en-us/azure/azure-sql/database-watcher-alerts
- https://learn.microsoft.com/en-us/azure/azure-monitor/alerts/alerts-dynamic-thresholds
- https://learn.microsoft.com/en-us/azure/azure-sql/database-watcher-overview
- https://learn.microsoft.com/en-us/azure/azure-sql/database-watcher-data
- https://learn.microsoft.com/en-us/sql/relational-databases/extended-events/extended-events
- https://learn.microsoft.com/en-us/sql/tools/sql-server-profiler/sql-server-profiler
- https://learn.microsoft.com/en-us/sql/relational-databases/extended-events/use-the-ssms-xe-profiler
- https://learn.microsoft.com/en-us/sql/relational-databases/extended-events/quick-start-extended-events-in-sql-server
- https://learn.microsoft.com/en-us/sql/t-sql/statements/create-event-session-transact-sql
- https://learn.microsoft.com/en-us/sql/relational-databases/extended-events/sql-server-extended-events-packages
- https://learn.microsoft.com/en-us/azure/azure-sql/database/xevent-db-diff-from-svr
- https://learn.microsoft.com/en-us/sql/relational-databases/extended-events/use-the-system-health-session
- https://learn.microsoft.com/en-us/sql/relational-databases/extended-events/targets-for-extended-events-in-sql-server
- https://learn.microsoft.com/en-us/azure/azure-sql/database/xevent-code-event-file
- https://learn.microsoft.com/en-us/azure/azure-sql/database/xevent-code-ring-buffer
- https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-xe-database-session-targets-azure-sql-database
- https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/credentials-database-engine
- https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-xe-session-targets-transact-sql