Troubleshooting practice questions

You are troubleshooting a failover group for Azure SQL Database. The automatic failover is not triggering as expected during a regional outage. You verify that the grace period for data loss is set to 3600 seconds. The outage lasts 30 minutes. What is the most likely reason the automatic failover did not occur?

Trap 1: The failover policy is set to manual.

A manual failover policy means Azure SQL Database never initiates automatic failover, regardless of grace period or outage duration, so the 3600-second setting is irrelevant here. It is tempting because manual policy is a legitimate configuration, used deliberately when administrators want controlled, planned failovers rather than automatic ones.

Trap 2: The grace period for data loss is too short.

The grace period is the maximum data loss tolerated before automatic failover proceeds; 3600 seconds exceeds the 30-minute outage, so it is not too short. It is tempting because grace period governs failover timing, and it would be the correct suspect if the outage had exceeded the configured value.

Trap 3: The secondary region is also experiencing an outage.

A simultaneous secondary-region outage would block failover, but nothing in the scenario indicates the secondary is down; the stated 3600-second grace period versus 30-minute outage is the relevant mismatch. It is tempting because regional outages can affect both regions, which would genuinely prevent failover.

A
The failover policy is set to manual.
Why it fails: A manual failover policy means Azure SQL Database never initiates automatic failover, regardless of grace period or outage duration, so the 3600-second setting is irrelevant here. It is tempting because manual policy is a legitimate configuration, used deliberately when administrators want controlled, planned failovers rather than automatic ones.
B
The outage duration is less than the grace period for data loss.
Automatic failover only triggers once the grace period elapses, and 3600 seconds equals 60 minutes. The 30-minute outage falls short of that threshold, so Azure SQL Database withholds failover to avoid potential data loss, satisfying the stem's grace-period constraint.
C
The grace period for data loss is too short.
Why it fails: The grace period is the maximum data loss tolerated before automatic failover proceeds; 3600 seconds exceeds the 30-minute outage, so it is not too short. It is tempting because grace period governs failover timing, and it would be the correct suspect if the outage had exceeded the configured value.
D
The secondary region is also experiencing an outage.
Why it fails: A simultaneous secondary-region outage would block failover, but nothing in the scenario indicates the secondary is down; the stated 3600-second grace period versus 30-minute outage is the relevant mismatch. It is tempting because regional outages can affect both regions, which would genuinely prevent failover.

Question 2hardmultiple choice

Refer to the exhibit. You are troubleshooting an Azure SQL Database auditing configuration. The exhibit shows the blob auditing policy. The storage account access key is null, and the subscription ID is all zeros. What is the most likely issue?

Exhibit

Refer to the exhibit.

```json
{
  "properties": {
    "state": "Enabled",
    "storageEndpoint": "https://storagesample.blob.core.windows.net",
    "storageAccountAccessKey": null,
    "retentionDays": 90,
    "auditActionsAndGroups": [
      "SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP",
      "FAILED_DATABASE_AUTHENTICATION_GROUP"
    ],
    "storageAccountSubscriptionId": "00000000-0000-0000-0000-000000000000",
    "isStorageSecondaryKeyInUse": false,
    "queueDelayTime": 0
  }
}
```

Trap 1: Auditing will fall back to Log Analytics workspace.

Blob auditing writes only to Azure Storage; no automatic failover to a Log Analytics workspace occurs when the storage target is invalid. It tempts because Log Analytics is a valid auditing destination, and would be correct if the policy had been configured for workspace-level auditing instead.

Trap 2: Auditing will work because managed identity is used.

A null access key with zeroed subscription ID indicates the policy is misconfigured, so auditing cannot write; managed identity requires the policy to specify its storage endpoint explicitly. It tempts because managed identity is a supported authentication method, and would be correct if the policy referenced a valid identity-based target.

Trap 3: Auditing will write to the storage account using the…

The zeroed subscription ID and null key show the policy points at no valid storage target, so the system-assigned managed identity has nothing to authenticate against. It tempts because managed identity is a legitimate auditing authentication method, and would work if the policy named a real storage account.

A
Auditing will fall back to Log Analytics workspace.
Why it fails: Blob auditing writes only to Azure Storage; no automatic failover to a Log Analytics workspace occurs when the storage target is invalid. It tempts because Log Analytics is a valid auditing destination, and would be correct if the policy had been configured for workspace-level auditing instead.
B
Auditing will work because managed identity is used.
Why it fails: A null access key with zeroed subscription ID indicates the policy is misconfigured, so auditing cannot write; managed identity requires the policy to specify its storage endpoint explicitly. It tempts because managed identity is a supported authentication method, and would be correct if the policy referenced a valid identity-based target.
C
Auditing will fail because the storage account access key is null.
Blob auditing writes audit logs to the storage account using its access key; a null key means the database cannot authenticate to that account. Auditing therefore fails to record events, regardless of the subscription ID placeholder shown in the exhibit.
D
Auditing will write to the storage account using the system-assigned managed identity.
Why it fails: The zeroed subscription ID and null key show the policy points at no valid storage target, so the system-assigned managed identity has nothing to authenticate against. It tempts because managed identity is a legitimate auditing authentication method, and would work if the policy named a real storage account.

Question 3hardmultiple choice

You are troubleshooting a performance issue on Azure SQL Database. The database uses the General Purpose tier with 100 DTUs. Users report intermittent slowdowns during peak hours. Query Store shows frequent waits for RESOURCE_SEMAPHORE. What is the most likely cause?

Trap 1: There is a blocking chain due to unoptimized queries.

Blocking chains produce LCK_M_* waits, not RESOURCE_SEMAPHORE, which specifically tracks memory grant contention. It is tempting because blocking is a common cause of slowdowns, but the observed wait type rules it out; the issue is concurrent queries awaiting workspace memory.

Trap 2: The disk IOPS limit is being reached, causing queuing.

RESOURCE_SEMAPHORE waits indicate queries queuing for memory grants, not storage IOPS saturation, which surfaces as PAGEIOLATCH or WRITELOG waits. It is tempting because General Purpose tier storage is remote, but the wait type points to insufficient memory grant capacity under concurrency.

Trap 3: The DTU limit is being reached, causing CPU throttling.

CPU throttling shows SOS_SCHEDULER_YIELD waits.

A
There is a blocking chain due to unoptimized queries.
Why it fails: Blocking chains produce LCK_M_* waits, not RESOURCE_SEMAPHORE, which specifically tracks memory grant contention. It is tempting because blocking is a common cause of slowdowns, but the observed wait type rules it out; the issue is concurrent queries awaiting workspace memory.
B
The disk IOPS limit is being reached, causing queuing.
Why it fails: RESOURCE_SEMAPHORE waits indicate queries queuing for memory grants, not storage IOPS saturation, which surfaces as PAGEIOLATCH or WRITELOG waits. It is tempting because General Purpose tier storage is remote, but the wait type points to insufficient memory grant capacity under concurrency.
C
The DTU limit is being reached, causing CPU throttling.
Why it fails: CPU throttling shows SOS_SCHEDULER_YIELD waits.
D
The database is experiencing memory pressure due to concurrent queries exceeding available memory.
RESOURCE_SEMAPHORE waits occur when queries cannot obtain a memory grant because concurrent queries have consumed the available workspace memory. At 100 DTUs the General Purpose tier caps memory, so peak-hour concurrency triggers the intermittent slowdowns.

Question 4mediummultiple choice

You are troubleshooting a connectivity issue: an application running on an Azure virtual machine (VM) cannot connect to an Azure SQL Database. The VM is in the same region as the SQL Database. The VM can ping other resources, but the SQL connection fails. The SQL Database has a firewall rule allowing the VM's private IP address. What is the most likely cause?

Trap 1: The SQL Database has the public endpoint disabled

Disabling the public endpoint removes the only route the VM's connection string can use, since private endpoints are not configured; the firewall rule then permits nothing. Public endpoints are correct when clients connect over the internet rather than through private connectivity.

Trap 2: The SQL Database firewall is configured at the database level, not…

Database-level firewall rules only govern contained database users, whereas the connection arrives from the VM's private IP and is evaluated by server-level rules first. It is tempting because database-level rules genuinely exist for scoping access to individual databases, but they cannot admit a client whose IP the server-level firewall has already rejected.

Trap 3: The VM does not have an outbound security rule allowing traffic to…

Azure SQL Database is reached over the Azure backbone via its public service endpoint; the platform's default outbound rule permits internet-bound traffic, so no custom NSG outbound rule is required. It would be the right check if the VM used a private endpoint or a forced-tunnel route that blackholed the traffic.

A
The SQL Database has the public endpoint disabled
Why it fails: Disabling the public endpoint removes the only route the VM's connection string can use, since private endpoints are not configured; the firewall rule then permits nothing. Public endpoints are correct when clients connect over the internet rather than through private connectivity.
B
The firewall rule uses the VM's private IP address, but Azure SQL Database sees the VM's public IP address
Azure SQL Database's firewall evaluates the source IP as seen by the gateway, which is the VM's public IP after SNAT, not its private address. The allowlisted private IP therefore never matches, so the connection is refused despite the rule existing.
C
The SQL Database firewall is configured at the database level, not the server level
Why it fails: Database-level firewall rules only govern contained database users, whereas the connection arrives from the VM's private IP and is evaluated by server-level rules first. It is tempting because database-level rules genuinely exist for scoping access to individual databases, but they cannot admit a client whose IP the server-level firewall has already rejected.
D
The VM does not have an outbound security rule allowing traffic to Azure SQL Database
Why it fails: Azure SQL Database is reached over the Azure backbone via its public service endpoint; the platform's default outbound rule permits internet-bound traffic, so no custom NSG outbound rule is required. It would be the right check if the VM used a private endpoint or a forced-tunnel route that blackholed the traffic.

Question 5hardmulti select

You need to automate monitoring and alerting for an Azure SQL Database. Which THREE actions can you achieve using Azure Monitor and SQL Insights?

Trap 1: Automatically rebuild fragmented indexes.

Index rebuilding is not an Azure Monitor feature.

Trap 2: Automatically scale the database to a higher service tier.

Auto-scaling is not available through Azure Monitor; it requires autoscale settings.

A
Automatically rebuild fragmented indexes.
Why it fails: Index rebuilding is not an Azure Monitor feature.
B
Automatically scale the database to a higher service tier.
Why it fails: Auto-scaling is not available through Azure Monitor; it requires autoscale settings.
C
Stream diagnostic logs to a Log Analytics workspace.
Diagnostic settings allow log streaming.
D
Set up an alert when DTU consumption exceeds 80% for 5 minutes.
Azure Monitor can alert on performance metrics.
E
Create a custom metric alert based on a KQL query.
Custom log searches can be used for alerts.

Question 6mediummulti select

You are troubleshooting a performance issue on an Azure SQL Database. Which TWO actions should you prioritize to identify the root cause of high resource consumption?

Trap 1: Rebuild all indexes to improve query performance.

Rebuilding indexes is a remediation that changes the workload before evidence is gathered, so the original cause may be erased. It is tempting because fragmentation commonly degrades performance, and would be correct after Query Store or wait statistics confirm fragmentation, but the question asks for root-cause identification.

Trap 2: Change the database recovery model to Simple.

Switching to Simple recovery reduces log growth and transaction log waits, altering behaviour instead of revealing the cause. It is tempting because log-related waits frequently drive high consumption, and would be valid for a log-heavy workload, but it is a remediation action, not a diagnostic one.

Trap 3: Scale the database to a higher service tier to mitigate the issue.

Scaling changes the resource allocation, masking the symptom rather than identifying which query, wait type or plan causes consumption. It is tempting because scaling often relieves pressure, and would be correct once diagnosis confirms the tier is genuinely undersized, but the question asks for root-cause identification.

A
Rebuild all indexes to improve query performance.
Why it fails: Rebuilding indexes is a remediation that changes the workload before evidence is gathered, so the original cause may be erased. It is tempting because fragmentation commonly degrades performance, and would be correct after Query Store or wait statistics confirm fragmentation, but the question asks for root-cause identification.
B
Change the database recovery model to Simple.
Why it fails: Switching to Simple recovery reduces log growth and transaction log waits, altering behaviour instead of revealing the cause. It is tempting because log-related waits frequently drive high consumption, and would be valid for a log-heavy workload, but it is a remediation action, not a diagnostic one.
C
Scale the database to a higher service tier to mitigate the issue.
Why it fails: Scaling changes the resource allocation, masking the symptom rather than identifying which query, wait type or plan causes consumption. It is tempting because scaling often relieves pressure, and would be correct once diagnosis confirms the tier is genuinely undersized, but the question asks for root-cause identification.
D
Review the Query Store Top Resource Consuming Queries report.
Query Store captures per-query runtime statistics, including CPU, duration, and logical reads, persisted across plan changes. The Top Resource Consuming Queries report ranks statements by total consumption, directly isolating which queries drive the high resource usage reported in the stem, rather than merely confirming that consumption is elevated.
E
Query sys.dm_exec_query_stats to find queries with high total_worker_time.
Querying sys.dm_exec_query_stats exposes cumulative CPU consumption per cached plan via total_worker_time, directly satisfying the stem's need to pinpoint which queries drive high resource use. Aggregated worker time ranks the worst offenders, letting you correlate top CPU consumers with the observed performance issue before tuning indexes or rewriting statements.

Question 7mediummulti select

Which TWO configurations can help improve the performance of an Azure SQL Database experiencing high `WRITELOG` waits?

Trap 1: Enable Transparent Data Encryption (TDE).

TDE encrypts data and log pages at rest, adding CPU overhead to log writes without lowering WRITELOG waits. It tempts because encryption is a common database hardening step, and would be the right answer for a compliance requirement, not a log-throughput bottleneck.

Trap 2: Enable Query Store.

Query Store captures execution plans and runtime statistics for plan regression analysis; it does not alter log flush behaviour or commit latency. It tempts because it is the standard tool for diagnosing query performance, and would be correct when investigating plan changes rather than WRITELOG waits.

Trap 3: Increase the frequency of database backups.

Backups read committed pages and write to separate storage; they do not reduce log flush frequency or latency, so WRITELOG waits persist. It tempts because backup tuning affects I/O load generally, and would be relevant when diagnosing backup-related IO stalls rather than transaction log throughput.

A
Enable Transparent Data Encryption (TDE).
Why it fails: TDE encrypts data and log pages at rest, adding CPU overhead to log writes without lowering WRITELOG waits. It tempts because encryption is a common database hardening step, and would be the right answer for a compliance requirement, not a log-throughput bottleneck.
B
Use in-memory OLTP to reduce log writes.
In-memory OLTP stores hot tables and their indexes in memory-optimised structures, so transactional changes are applied without conventional page-latch logging. This directly reduces the volume of log records generated per transaction, lowering WRITELOG waits caused by log-write throughput saturation on the target Azure SQL Database.
C
Increase the service tier to a higher performance level.
Increasing the service tier raises the provisioned IOPS and log throughput limits of the underlying storage, directly relieving the log-write bottleneck that high WRITELOG waits indicate. This satisfies the stem's constraint of reducing log flush latency under sustained write load.
D
Enable Query Store.
Why it fails: Query Store captures execution plans and runtime statistics for plan regression analysis; it does not alter log flush behaviour or commit latency. It tempts because it is the standard tool for diagnosing query performance, and would be correct when investigating plan changes rather than WRITELOG waits.
E
Increase the frequency of database backups.
Why it fails: Backups read committed pages and write to separate storage; they do not reduce log flush frequency or latency, so WRITELOG waits persist. It tempts because backup tuning affects I/O load generally, and would be relevant when diagnosing backup-related IO stalls rather than transaction log throughput.

Question 8hardmulti select

You are troubleshooting a performance issue on an Azure SQL Database. The database is experiencing high PAGELATCH_EX waits. Which TWO measures can help reduce these waits?

Trap 1: Use a hash distribution or round-robin distribution in a table…

Hash or round-robin distribution applies to Azure Synapse dedicated SQL pools, not Azure SQL Database, so it cannot reduce PAGELATCH_EX waits there. It is tempting because distribution design genuinely relieves latch contention in massively parallel warehouses, but Azure SQL Database uses a different storage engine where that mechanism does not exist.

Trap 2: Increase MAXDOP for the queries

Increasing MAXDOP spreads a query across more schedulers, which does not relieve PAGELATCH_EX waits; those arise from concurrent threads contending on the same in-memory page, typically insert-heavy workloads hitting the last page of a narrow index. MAXDOP tuning addresses parallelism-related waits such as CXPACKET, so it would be the right lever for a query suffering from skewed parallel execution.

Trap 3: Enable snapshot isolation level

Snapshot isolation removes reader-writer blocking, but PAGELATCH_EX waits are in-memory page-latch contention on hot last-page inserts, not lock waits, so it changes nothing. It is tempting because it addresses blocking, and it would be correct for reducing reader-writer lock contention in a read-heavy OLTP workload.

A
Use a hash distribution or round-robin distribution in a table design
Why it fails: Hash or round-robin distribution applies to Azure Synapse dedicated SQL pools, not Azure SQL Database, so it cannot reduce PAGELATCH_EX waits there. It is tempting because distribution design genuinely relieves latch contention in massively parallel warehouses, but Azure SQL Database uses a different storage engine where that mechanism does not exist.
B
Increase MAXDOP for the queries
Why it fails: Increasing MAXDOP spreads a query across more schedulers, which does not relieve PAGELATCH_EX waits; those arise from concurrent threads contending on the same in-memory page, typically insert-heavy workloads hitting the last page of a narrow index. MAXDOP tuning addresses parallelism-related waits such as CXPACKET, so it would be the right lever for a query suffering from skewed parallel execution.
C
Partition the table to distribute inserts
PAGELATCH_EX waits arise from concurrent inserts contending on the last page of an ascending index. Partitioning the table across multiple filegroups or partition ranges spreads inserts over several hot pages, reducing that contention and therefore the exclusive page-latch waits the database is experiencing.
D
Use OPTIMIZE_FOR_SEQUENTIAL_KEY index option
PAGELATCH_EX waits arise from concurrent inserts contending on the last-page insert point of monotonically increasing keys. OPTIMIZE_FOR_SEQUENTIAL_KEY changes the latch acquisition behaviour for such indexes, reducing this contention without altering the query plan or schema.
E
Enable snapshot isolation level
Why it fails: Snapshot isolation removes reader-writer blocking, but PAGELATCH_EX waits are in-memory page-latch contention on hot last-page inserts, not lock waits, so it changes nothing. It is tempting because it addresses blocking, and it would be correct for reducing reader-writer lock contention in a read-heavy OLTP workload.

Question 9mediummultiple choice

You deploy a new Azure SQL Database and need to ensure that all queries are logged for performance analysis. Which configuration should you enable?

Trap 1: Data classification

Data classification labels columns by sensitivity for governance and compliance reporting; it records no query text or execution statistics. It is tempting because it appears under security and auditing, but it classifies data rather than capturing workload activity. Query Store is what logs queries for performance analysis.

Trap 2: Server-level audit

Server-level auditing writes audit records for server-level events and administrative actions, not per-query performance data. It is tempting because auditing sounds like logging, but its records target security compliance, not execution plans or runtime statistics. Query Store captures the query-level detail required here.

Trap 3: Diagnostic settings for SQLInsights

SQLInsights is an Intelligent Insights telemetry feed that surfaces automated diagnoses of performance problems, not a per-query log. It cannot satisfy the requirement to log all queries; Query Store or diagnostic settings streaming QueryStoreRuntimeStatistics provide that. It is tempting because it is a diagnostic setting, but its purpose is anomaly detection.

A
Data classification
Why it fails: Data classification labels columns by sensitivity for governance and compliance reporting; it records no query text or execution statistics. It is tempting because it appears under security and auditing, but it classifies data rather than capturing workload activity. Query Store is what logs queries for performance analysis.
B
Server-level audit
Why it fails: Server-level auditing writes audit records for server-level events and administrative actions, not per-query performance data. It is tempting because auditing sounds like logging, but its records target security compliance, not execution plans or runtime statistics. Query Store captures the query-level detail required here.
C
Diagnostic settings for SQLInsights
Why it fails: SQLInsights is an Intelligent Insights telemetry feed that surfaces automated diagnoses of performance problems, not a per-query log. It cannot satisfy the requirement to log all queries; Query Store or diagnostic settings streaming QueryStoreRuntimeStatistics provide that. It is tempting because it is a diagnostic setting, but its purpose is anomaly detection.
D
Query Store
Query Store continuously captures query text, execution plans and runtime statistics into internal catalog views, giving historical performance analysis without trace overhead. It satisfies the requirement that all queries be logged for later analysis, and can be enabled per database in Azure SQL Database.

Question 10mediummultiple choice

You are troubleshooting a performance degradation on an Azure SQL Database. You notice that the database is hitting the maximum DTU limit frequently. Which action should you take first to reduce DTU consumption?

Trap 1: Increase the log rate limit

The log rate limit is a governor threshold on log generation, not a DTU consumer; raising it permits more logging and can increase resource pressure. It is tempting when write-heavy workloads hit log throttling, but that scenario differs from sustained DTU saturation.

Trap 2: Scale up the database to a higher service tier

Scaling up raises the DTU ceiling rather than reducing consumption, so the underlying inefficient queries remain. It is tempting because tier changes are the standard fix once tuning is exhausted, but the question asks for the first action to lower DTU usage, not to buy more capacity.

Trap 3: Rebuild all indexes in the database

Rebuilding every index is heavy maintenance that itself consumes DTUs and may not address the top wait type. It is tempting because fragmentation is a real cause of high CPU, but the first step should target the queries or plans actually driving consumption, identified via Query Store.

A
Increase the log rate limit
Why it fails: The log rate limit is a governor threshold on log generation, not a DTU consumer; raising it permits more logging and can increase resource pressure. It is tempting when write-heavy workloads hit log throttling, but that scenario differs from sustained DTU saturation.
B
Scale up the database to a higher service tier
Why it fails: Scaling up raises the DTU ceiling rather than reducing consumption, so the underlying inefficient queries remain. It is tempting because tier changes are the standard fix once tuning is exhausted, but the question asks for the first action to lower DTU usage, not to buy more capacity.
C
Use Query Performance Insight to identify and optimize top resource-consuming queries
Query Performance Insight surfaces the top CPU, duration and DTU-consuming queries, letting you tune indexes or rewrite statements to cut DTU usage. Scaling the tier would mask the problem rather than reduce consumption, so identifying the offending queries comes first.
D
Rebuild all indexes in the database
Why it fails: Rebuilding every index is heavy maintenance that itself consumes DTUs and may not address the top wait type. It is tempting because fragmentation is a real cause of high CPU, but the first step should target the queries or plans actually driving consumption, identified via Query Store.

Question 11hardmultiple choice

You are troubleshooting a performance issue in an Azure SQL Database. The database is in the Hyperscale service tier. You observe that read queries are slow, and you suspect that a specific query plan is causing excessive physical reads. You want to identify the query and its plan, and then force a better plan if available. Which tool should you use to capture and analyze the plan, and then force a plan?

Trap 1: Extended Events session with sql_batch_completed and…

Extended Events can capture query plans via query_post_execution_showplan, but it is resource-intensive and does not provide a built-in mechanism to force a plan. It is a diagnostic tool, not a plan management tool. Query Store is more efficient for this purpose.

Trap 2: Dynamic management views sys.dm_exec_requests and…

These DMVs provide real-time information about currently executing requests and their SQL text, but they do not retain historical data or allow plan forcing. They are useful for immediate troubleshooting but cannot be used to analyze past queries or force a plan.

Trap 3: Azure SQL Database automatic tuning with CREATE_INDEX.

Automatic tuning's CREATE_INDEX option can create indexes to improve performance, but it does not allow you to capture and force a specific query plan. It operates automatically based on workload analysis and does not provide manual plan forcing capabilities.

A
Extended Events session with sql_batch_completed and query_post_execution_showplan events.
Why it fails: Extended Events can capture query plans via query_post_execution_showplan, but it is resource-intensive and does not provide a built-in mechanism to force a plan. It is a diagnostic tool, not a plan management tool. Query Store is more efficient for this purpose.
B
Dynamic management views sys.dm_exec_requests and sys.dm_exec_sql_text.
Why it fails: These DMVs provide real-time information about currently executing requests and their SQL text, but they do not retain historical data or allow plan forcing. They are useful for immediate troubleshooting but cannot be used to analyze past queries or force a plan.
C
Query Store, using the Top Resource Consuming Queries report and plan forcing.
Query Store is fully supported in the Hyperscale service tier and captures query plans and runtime statistics. It allows you to identify resource-intensive queries, view their plans, and force a specific plan. This directly meets the need to capture, analyze, and force a plan for a query causing excessive physical reads.
D
Azure SQL Database automatic tuning with CREATE_INDEX.
Why it fails: Automatic tuning's CREATE_INDEX option can create indexes to improve performance, but it does not allow you to capture and force a specific query plan. It operates automatically based on workload analysis and does not provide manual plan forcing capabilities.

Question 12easymultiple choice

You manage an Azure SQL Database that is critical for a financial application. The database has a read-heavy workload, and you need to monitor and diagnose performance issues. You want to enable a feature that automatically captures detailed information about query plans and runtime statistics for later analysis. Which feature should you enable?

Trap 1: Extended Events

Extended Events is a lightweight tracing system that can capture detailed query execution events, but it requires manual session setup and management. It does not automatically capture and retain query plans and runtime statistics in a queryable format like Query Store does, making it less convenient for ongoing performance monitoring.

Trap 2: Automatic tuning

Automatic tuning uses Query Store data to create and drop indexes and force plans, but it does not itself capture detailed query plans and runtime statistics. It relies on Query Store to be enabled. Therefore, enabling automatic tuning alone will not provide the raw diagnostic data needed for deep analysis.

Trap 3: Dynamic management views (DMVs)

DMVs provide real-time information about current query execution and system health, but they do not persist historical data. Once the query completes or the server restarts, the information is lost. For historical analysis and trend identification, a persistent store like Query Store is required.

A
Extended Events
Why it fails: Extended Events is a lightweight tracing system that can capture detailed query execution events, but it requires manual session setup and management. It does not automatically capture and retain query plans and runtime statistics in a queryable format like Query Store does, making it less convenient for ongoing performance monitoring.
B
Query Store
Query Store captures query plans and runtime execution statistics automatically, persisting them in the user database. It provides historical data on query performance, enabling you to identify regressions, analyze plan changes, and force plans. For a read-heavy workload, Query Store is essential for diagnosing issues without manual tracing.
C
Automatic tuning
Why it fails: Automatic tuning uses Query Store data to create and drop indexes and force plans, but it does not itself capture detailed query plans and runtime statistics. It relies on Query Store to be enabled. Therefore, enabling automatic tuning alone will not provide the raw diagnostic data needed for deep analysis.
D
Dynamic management views (DMVs)
Why it fails: DMVs provide real-time information about current query execution and system health, but they do not persist historical data. Once the query completes or the server restarts, the information is lost. For historical analysis and trend identification, a persistent store like Query Store is required.