Refer to the Exhibit Practice Questions

CPU bottleneck

Why it fails: PAGEIOLATCH_SH waits occur when a thread waits for a data page to be read from storage into the buffer pool, so the bottleneck is I/O, not processor scheduling. CPU pressure instead manifests as SOS_SCHEDULER_YIELD or CXPACKET waits, making this tempting when slow queries are assumed to be compute-bound.

Blocking between concurrent transactions

Why it fails: PAGEIOLATCH_SH is a storage-read wait, so it points to slow I/O rather than lock contention. Blocking produces LCK_M_* waits, which is why this misleads anyone who equates long-running queries with concurrent-transaction contention; the correct diagnosis here is disk latency or insufficient memory.

Memory pressure

Why it fails: PAGEIOLATCH_SH measures time waiting for pages to be read from disk, which is an I/O symptom; memory pressure instead shows RESOURCE_SEMAPHORE or low page-life-expectancy. It tempts because insufficient buffer-pool memory does force extra physical reads, but the wait itself names storage latency, not memory shortage.

Full backups are taken every 12 hours and retained for 7 days.

Why it fails: The retention configuration sets the point-in-time restore window, not a 12-hour full backup frequency; Azure SQL takes full backups roughly weekly with differential and log backups in between. A 12-hour full schedule would apply to custom backup policies, not this setting.

Long-term retention (LTR) is set to 7 days.

Why it fails: Seven days is the short-term point-in-time restore retention, not long-term retention; LTR uses separate weekly, monthly, or yearly policies with their own retention periods. LTR would be the choice when compliance requires copies retained for months or years.

Transaction log backups are taken every 12 hours.

Why it fails: Transaction log backups occur roughly every 5–10 minutes to support point-in-time restore, not every 12 hours. A 12-hour interval describes neither log nor full backups in Azure SQL's automated schedule; it would only match a custom maintenance plan.

To enable geo-replication for the managed instance.

Why it fails: Managed backup configures automated backup retention and storage redundancy for the instance's databases; geo-replication is a separate feature configured on individual databases, not through backup settings. It is tempting because both concern resilience, and geo-replication would be the right choice when the goal is a readable secondary in another region.

To set up disaster recovery across Azure regions.

Why it fails: Managed backup governs automated backup scheduling, retention periods and storage redundancy within the instance; cross-region disaster recovery requires failover groups or geo-replication, which backup settings do not provision. It is tempting because backups underpin recovery, and would be correct if the requirement were simply retaining backups for restore.

To configure point-in-time restore for the managed instance.

Why it fails: Point-in-time restore is a capability enabled by the automated backups that managed backup governs, not its purpose; the configuration sets retention and redundancy policy rather than defining a restore operation. It is tempting because PITR depends on those backups, and would be the right answer if the question asked what the backups enable.

The database has one high-availability replica.

Why it fails: A high-availability replica is provided by the Business Critical tier, not General Purpose, which relies on remote storage; the exhibit shows General Purpose. The statement tempts because Business Critical does include such a replica, but that tier is not configured here.

The configuration is invalid because General Purpose tier cannot be zone-redundant.

Why it fails: General Purpose supports zone-redundant configuration in supported regions, so the configuration is valid; the claim of invalidity is false. It tempts because zone redundancy was historically limited to Business Critical, but that restriction no longer applies.

The database uses locally-redundant backup storage.

Why it fails: Locally-redundant storage keeps three copies within one datacentre; the exhibit's backup storage redundancy value determines this, and it specifies zone-redundant instead. LRS tempts as the default cheap option, but the configuration shown does not select it.

Query Store is not enabled on the databases.

Why it fails: The stem explicitly confirms Query Store is enabled on all databases, so this cannot explain the gap. Disabled Query Store is the correct diagnosis when no runtime statistics appear at all, including historical data, rather than only the recent window.

The time range in the query is too narrow and excludes the last 24 hours.

Why it fails: The stem states results are missing for the last 24 hours specifically, so a narrow time filter would exclude recent data only if it ended earlier; the query already covers that window. Narrow ranges are the culprit when older data is missing instead.

The query syntax is incorrect and needs to use 'project' before 'summarize'.

Why it fails: The query syntax does not require 'project' before 'summarize'; 'summarize' can directly follow a table reference or other operators, and the query would still be syntactically valid. The lack of data is due to missing diagnostic settings, not syntax.

It configures the database to pause during a disaster recovery scenario.

Why it fails: AutoPauseDelayInMinutes sets the idle interval before a serverless database auto-pauses; it has no disaster-recovery role, since geo-failover is handled by failover groups or active geo-replication. It is tempting because pause and failover both concern availability, but the parameter purely governs serverless idle suspension.

It sets the maximum duration for which the database can be paused.

Why it fails: The parameter specifies the idle period before auto-pause triggers, not a cap on pause duration; a paused serverless database stays paused until a connection resumes it. It is tempting because the name suggests a pause timeout, but the value is a delay before pausing, not a maximum pause length.

It determines how long the database takes to resume after a pause.

Why it fails: Resume time is determined by the service, not configured by this parameter, which sets how many minutes of inactivity precede auto-pause. It is tempting because both values are expressed in minutes and concern the pause cycle, but the parameter controls the idle delay before pausing, not the resume duration.

The query is experiencing I/O latency

Why it fails: Logical reads count pages read from the buffer pool, so they indicate memory-resident page access volume, not physical I/O latency. I/O latency is tempting because heavy reading sounds I/O-bound, and it would be correct if the metric were physical reads or PAGEIOLATCH waits rather than total_logical_reads.

The query is using a lot of CPU time

Why it fails: Logical reads measure buffer pool page accesses, not processor cycles; a query can perform many logical reads with minimal CPU. CPU is tempting because expensive queries often consume both, and it would be correct if the metric were total_worker_time or CPU time rather than total_logical_reads.

The query is using a lot of memory

Why it fails: High logical reads reflect pages touched, not memory consumption; a query can read millions of buffer pages while using little workspace memory. Memory is tempting because logical reads occur in the buffer pool, and it would be correct if the metric were granted workspace memory or a memory grant wait.

4 days.

Why it fails: Four days reflects the weekly retention value read as days rather than weeks; the weeklyRetention property counts weeks, so the figure is understated by a factor of seven. Four days would be right only for a daily retention setting, not the weekly tier shown in the ARM snippet.

4 months.

Why it fails: Four months applies the monthly retention unit to the weeklyRetention property, which is measured in weeks. Four months would be correct only if the snippet configured monthlyRetention as P4M; the weekly field shown cannot yield months.

4 years.

Why it fails: The ARM snippet's weekly retention value is expressed in weeks (or days), not years, so four years misreads the unit and the field. Four years would be correct only if the weeklyRetention property were set to P4Y or 48 months, which the exhibit does not show.

Blocking due to lock contention

Why it fails: Lock contention would appear as LCK_M_* waits with blocking chains in the Extended Events session; the exhibit's waits do not match. Blocking is tempting because it is a frequent cause of sudden slowdowns, and it would be correct if the captured waits showed sessions waiting on locks held by another transaction.

CPU pressure from high-complexity queries

Why it fails: The exhibit's waits would need to show CPU-related signals such as SOS_SCHEDULER_YIELD alongside high worker utilisation; the stem's evidence points elsewhere. CPU pressure is tempting because expensive queries are a common cause of degradation, and it would be correct if the waits and Query Store showed sustained high CPU with those signals.

Insufficient memory allocation for the database

Why it fails: Memory pressure would surface as RESOURCE_SEMAPHORE or PAGEIOLATCH waits with low page-life expectancy; the exhibit's evidence indicates otherwise. Insufficient memory is tempting because it commonly degrades Azure SQL Database performance, and it would be correct if the waits and memory DMVs showed grant waits or buffer-pool pressure.

Increase the maximum storage size

Why it fails: Intelligent Insights alerts flag query-plan regressions, blocking, or resource contention, not capacity exhaustion, so storage size is irrelevant. Increasing maximum storage is the right first action when the database approaches its size limit; here the diagnosis should target the identified performance regression.

Change the service tier to BusinessCritical

Why it fails: Intelligent Insights alerts already identify the specific query or workload causing degradation; changing tier to Business Critical only alters underlying hardware and replica count, leaving the diagnosed root cause unaddressed. It is tempting because Business Critical provides local SSD and in-memory OLTP, which suits latency-sensitive workloads, but remediation must follow the alert's findings first.

Scale up to Standard S3 (100 DTU)

Why it fails: Scaling to Standard S3 adds DTUs but does not resolve the specific regression Intelligent Insights has detected, such as a plan change or blocking chain. It is tempting because DTU exhaustion commonly causes performance degradation, making a tier bump the correct response when the alert attributes the issue to resource saturation rather than a query-level fault.

The principal is incorrectly specified; it should be a security group.

Why it fails: The principal's type does not determine connectivity; a correctly assigned user account authenticates and connects exactly as a security group does. Security groups are tempting because they simplify bulk permission management, but they are a governance convenience, not a requirement, so this cannot explain the connection failure.

The scope is too broad; it should be at the server level.

Why it fails: Broadening scope to the server would not fix a connection failure; scope controls which resources an assignment covers, not whether a client can reach the logical server. Server-level scope is tempting because it covers every database at once, which suits estate-wide administration, but the user cannot connect at all.

The action 'Microsoft.Sql/servers/databases/read' is not valid; it should be 'Microsoft.Sql/servers/databases/dataReader'.

Why it fails: 'Microsoft.Sql/servers/databases/read' is a valid control-plane action; the dataReader action does not exist as a resource-provider operation. Data-plane roles such as SQL DB Reader are tempting because they grant table access, but the reported symptom is failure to connect, which points to server-level authentication or firewall configuration instead.

The key specified in the URI does not exist

Why it fails: A missing key produces a distinct 'key not found' error during TDE configuration, not the 'Inaccessible' state, which specifically indicates the key exists but cannot be unwrapped. It tempts because key deletion or rotation is a frequent CMK failure mode, and verifying the URI is a sensible first diagnostic step.

The user-assigned managed identity is not assigned to the SQL Database server

Why it fails: The identity is specified in the template, so it is assigned.

The Key Vault firewall is enabled and does not allow Azure services

Why it fails: Key Vault firewall rules govern the vault's data plane, but TDE's key unwrap call from Azure SQL Database originates from the SQL logical server's user-assigned managed identity, which the firewall's 'Allow trusted Microsoft services' bypass does not cover for user-assigned identities. It tempts because firewall misconfiguration commonly blocks access.

Zone redundancy is disabled, causing failover delays.

Why it fails: Zone redundancy affects durability and recovery from datacentre failure, not query throughput; failover delays occur only during an outage, whereas the exhibit shows recurring peak-hour slowness. Zone redundancy is the right choice when the requirement is high availability across availability zones, not performance tuning.

Read scale is disabled, increasing load on primary.

Why it fails: Read scale offloads read-only workloads to a secondary replica; if the exhibit shows no read-heavy queries being routed away, disabling it changes nothing about primary write contention. Read scale is the correct lever when reporting queries saturate the primary and can tolerate replication lag.

The database is auto-pausing frequently due to autoPauseDelay.

Why it fails: Auto-pausing suspends compute after inactivity, so it cannot cause contention during peak hours when the database is continuously active. The autoPauseDelay setting applies only to serverless tiers and resumes on the next connection; it is the right consideration when diagnosing cold-start latency after idle periods, not sustained peak load.

The user is created but not granted any permissions.

Why it fails: The exhibited GRANT statement assigns permissions to the created user, so claiming none were granted contradicts the script. It is tempting because CREATE USER alone grants nothing, and would be correct if the GRANT line were absent from the batch.

The commands fail because Entra ID users cannot be created in Azure SQL Database.

Why it fails: Entra ID logins are created as contained database users via FROM EXTERNAL PROVIDER, so the syntax succeeds; the option mistakes authentication source for creation capability. It tempts because Entra ID identities are mastered in the directory, yet Azure SQL Database supports the database-scoped replica needed for T-SQL creation.

A SQL Server authentication user is created and granted read access.

Why it fails: The exhibited statements create a contained database user without a login, so no server-level SQL authentication user results. It is tempting because CREATE USER plus GRANT is the pattern for logins, and would be correct had the script used FROM LOGIN with a server credential.

The source database must have point-in-time restore enabled.

Why it fails: Point-in-time restore governs recovery to a specific moment within the retention window, not cross-region capability. It is tempting because PITR is a prerequisite for many restore operations, but the command's success depends on geo-replication or geo-restore configuration, not on PITR being enabled on the source.

The source database must be online at the time of restoration.

Why it fails: A restore is precisely the operation performed when the source is unavailable, so requiring it to be online defeats disaster recovery. It is tempting because most restores run against live sources, but cross-region restore relies on replicated backups that persist independently of source availability.

The target server must be in the same region as the source server.

Why it fails: Cross-region restore explicitly targets a different region, so requiring the target server to share the source's region contradicts the scenario's goal. It is tempting because same-region restores are the common default, and geo-replication pairs servers regionally, but the command's purpose is regional failover.