Troubleshooting practice questions

A company has an Azure SQL Database that is experiencing performance degradation during peak hours. The database is configured with the Standard tier (S2). Which action should you recommend to improve performance without changing the application code?

Trap 1: Enable Query Store and run the Performance Dashboard.

Only monitors performance, does not fix it.

Trap 2: Enable read scale-out to offload read queries.

Not applicable for Standard tier; only for Premium/Business Critical.

Trap 3: Create nonclustered indexes on all tables.

Requires application code changes and index design.

A
Scale up the database to a higher service objective (e.g., S3).
Increases DTU limit, providing more resources.
B
Enable Query Store and run the Performance Dashboard.
Why wrong: Only monitors performance, does not fix it.
C
Enable read scale-out to offload read queries.
Why wrong: Not applicable for Standard tier; only for Premium/Business Critical.
D
Create nonclustered indexes on all tables.
Why wrong: Requires application code changes and index design.

Question 2mediummultiple 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

Increasing the log rate limit does not reduce DTU consumption; it affects transaction log throughput.

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

Scaling up increases cost and may not address root cause; it is better to optimize queries first.

Trap 3: Rebuild all indexes in the database

Rebuilding all indexes can temporarily increase DTU consumption and may not be necessary.

A
Increase the log rate limit
Why wrong: Increasing the log rate limit does not reduce DTU consumption; it affects transaction log throughput.
B
Scale up the database to a higher service tier
Why wrong: Scaling up increases cost and may not address root cause; it is better to optimize queries first.
C
Use Query Performance Insight to identify and optimize top resource-consuming queries
Query Performance Insight helps identify and optimize top resource-consuming queries, directly reducing DTU usage.
D
Rebuild all indexes in the database
Why wrong: Rebuilding all indexes can temporarily increase DTU consumption and may not be necessary.

Question 3mediummulti 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.

This is a remediation step, not a diagnostic step.

Trap 2: Change the database recovery model to Simple.

Not supported in Azure SQL Database and does not help diagnose resource consumption.

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

Reactive measure, not diagnostic.

A
Rebuild all indexes to improve query performance.
Why wrong: This is a remediation step, not a diagnostic step.
B
Change the database recovery model to Simple.
Why wrong: Not supported in Azure SQL Database and does not help diagnose resource consumption.
C
Scale the database to a higher service tier to mitigate the issue.
Why wrong: Reactive measure, not diagnostic.
D
Review the Query Store Top Resource Consuming Queries report.
Identifies queries consuming the most resources historically.
E
Query sys.dm_exec_query_stats to find queries with high total_worker_time.
Shows current cached queries with high CPU usage.

Question 4easymulti select

You are troubleshooting a performance issue on an Azure SQL Database. Which THREE Dynamic Management Views (DMVs) should you query to identify the most resource-intensive queries?

Trap 1: sys.dm_os_wait_stats

This DMV shows wait statistics, not per-query resource usage.

Trap 2: sys.dm_exec_sessions

This DMV provides session-level information, not query resource usage.

A
sys.dm_exec_requests
This DMV shows currently executing requests and their resource consumption.
B
sys.dm_os_wait_stats
Why wrong: This DMV shows wait statistics, not per-query resource usage.
C
sys.dm_exec_query_plan
This DMV retrieves the execution plan for a given plan handle.
D
sys.dm_exec_sessions
Why wrong: This DMV provides session-level information, not query resource usage.
E
sys.dm_exec_query_stats
`sys.dm_exec_query_stats` returns aggregated performance statistics for cached query plans, including total worker time, logical reads, and execution count. Querying it with an `ORDER BY` clause on `total_elapsed_time DESC` or `total_logical_reads DESC` directly identifies the most resource-intensive queries, satisfying the stem’s requirement to pinpoint high-resource consumers for troubleshooting.

Question 5hardmulti select

You are troubleshooting a transaction log growth issue on an Azure SQL Database. Which THREE conditions can cause the transaction log to grow unexpectedly?

Trap 1: A memory-optimized table with a large number of rows

Memory-optimized tables log differently but not inherently more.

Trap 2: Page compression enabled on tables

Compression reduces log size.

A
Replication that has not delivered transactions to the subscriber
Log space held for replication.
B
A memory-optimized table with a large number of rows
Why wrong: Memory-optimized tables log differently but not inherently more.
C
A long-running transaction that has not been committed
Prevents log truncation.
D
A missing index on a large table that causes excessive updates
Large updates generate many log records.
E
Page compression enabled on tables
Why wrong: Compression reduces log size.

Question 6hardmultiple choice

You are troubleshooting a performance issue on an Azure SQL Database. Query Store shows a significant increase in query duration for a specific query. The execution plan has changed from a hash join to a nested loops join. What is the most likely cause?

Trap 1: Parameter sniffing caused the optimizer to choose a plan for…

Could cause a different plan but less likely than stale stats.

Trap 2: An index was dropped, forcing a table scan

Would cause scan, not nested loops join.

Trap 3: Forced parameterization was enabled

Would not typically change join type.

A
Parameter sniffing caused the optimizer to choose a plan for atypical parameter values
Why wrong: Could cause a different plan but less likely than stale stats.
B
An index was dropped, forcing a table scan
Why wrong: Would cause scan, not nested loops join.
C
Forced parameterization was enabled
Why wrong: Would not typically change join type.
D
Stale statistics caused the optimizer to underestimate cardinality
Leads to nested loops instead of hash join due to underestimation.

Question 7hardmulti 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 wrong: Index rebuilding is not an Azure Monitor feature.
B
Automatically scale the database to a higher service tier.
Why wrong: 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 8easymultiple choice

You are troubleshooting a performance issue in Azure SQL Database. You suspect that parameter sniffing is causing suboptimal query plans. What feature can you use to mitigate this without code changes?

Trap 1: Reorganize indexes regularly.

Index maintenance does not address parameter sniffing.

Trap 2: Use Query Store to force a good plan.

Plan forcing addresses a regression, not prevents sniffing.

Trap 3: Add OPTIMIZE FOR UNKNOWN query hint to all queries.

This requires code changes and is per query.

A
Reorganize indexes regularly.
Why wrong: Index maintenance does not address parameter sniffing.
B
Use Query Store to force a good plan.
Why wrong: Plan forcing addresses a regression, not prevents sniffing.
C
Add OPTIMIZE FOR UNKNOWN query hint to all queries.
Why wrong: This requires code changes and is per query.
D
Set the database to FORCE_PARAMETERIZATION = ON.
Forces parameterization to reduce sniffing effects.

Question 9hardmultiple 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 causes LCK_M_ waits, not RESOURCE_SEMAPHORE.

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

Disk IO waits manifest as PAGEIOLATCH or WRITELOG, not RESOURCE_SEMAPHORE.

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 wrong: Blocking causes LCK_M_ waits, not RESOURCE_SEMAPHORE.
B
The disk IOPS limit is being reached, causing queuing.
Why wrong: Disk IO waits manifest as PAGEIOLATCH or WRITELOG, not RESOURCE_SEMAPHORE.
C
The DTU limit is being reached, causing CPU throttling.
Why wrong: CPU throttling shows SOS_SCHEDULER_YIELD waits.
D
The database is experiencing memory pressure due to concurrent queries exceeding available memory.
RESOURCE_SEMAPHORE specifically indicates waiting for memory grant; common in under-provisioned tiers.

Question 10mediummultiple 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

Public endpoint is enabled by default for Azure SQL Database.

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

Firewall rules are typically set at the server level and apply to all databases.

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

The VM can ping other resources, so outbound traffic is generally allowed.

A
The SQL Database has the public endpoint disabled
Why wrong: Public endpoint is enabled by default for Azure SQL Database.
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 receives the public IP of the VM, so the rule with private IP does not match.
C
The SQL Database firewall is configured at the database level, not the server level
Why wrong: Firewall rules are typically set at the server level and apply to all databases.
D
The VM does not have an outbound security rule allowing traffic to Azure SQL Database
Why wrong: The VM can ping other resources, so outbound traffic is generally allowed.