How many Troubleshooting Scenario Questions questions are on this page?

This page has 10 Troubleshooting Scenario Questions scenario questions for the DP-300 exam, each with detailed explanations and wrong-answer analysis.

How should I approach DP-300 scenario questions?

Read the full scenario before looking at the answer options. Identify the constraint or requirement in the scenario, then eliminate options that are generally true but wrong for this specific case. Scenario questions reward careful reading over pattern matching.

← Back to Microsoft Azure Database Administrator Associate DP-300 questions

Scenario-based practice

Troubleshooting Scenario Questions

Practise Microsoft Azure Database Administrator Associate DP-300 practice questions — original exam-style scenarios covering every exam domain, with detailed explanations, wrong-answer analysis, and common exam traps.

Start full practice test Read exam guide

scenario questions

DP-300

exam code

Microsoft

vendor

Scenario guide

How to approach troubleshooting scenario questions

These questions describe a network symptom and ask you to identify the root cause or the correct fix. They appear across all certification exams and reward systematic thinking over memorisation. The best candidates follow a consistent troubleshooting framework even under time pressure.

Quick answer

Troubleshooting Scenario Questions questions test whether you can apply the concept in context, not just recognise a definition.

How the topic appears in realistic exam-style scenarios.

Which detail in the question changes the correct answer.

How to eliminate plausible but wrong options.

How to connect the question back to the wider exam objective.

Practice scenarios

Question 1mediummultiple choice

Full question →

You are managing an Azure SQL Database that experiences intermittent performance degradation. Query Store shows a significant increase in wait time for PAGEIOLATCH_SH. You need to identify the most likely cause. What should you investigate first?

A
Out-of-date statistics
Why wrong: Out-of-date statistics cause cardinality estimation errors, not I/O waits. Therefore, this is incorrect.
B
Insufficient IOPS or throughput at the database level
PAGEIOLATCH_SH waits indicate I/O subsystem pressure, often due to insufficient IOPS or throughput.
C
Missing indexes
Why wrong: Missing indexes typically cause table scans but not necessarily PAGEIOLATCH_SH waits. Therefore, this is incorrect.
D
Blocking from long-running transactions
Why wrong: Blocking causes waits like LCK_M_*, not PAGEIOLATCH.

Full breakdown with real-world context →

Question 2easymultiple choice

Full question →

You are monitoring an Azure SQL Database using Intelligent Insights. You receive an alert that resource usage has exceeded 90% for the past hour. What is the recommended first step to troubleshoot?

A
Scale up the database to a higher service tier immediately.
Why wrong: Should not scale without understanding the cause.
B
Check sys.dm_os_wait_stats for high wait types.
Why wrong: Less efficient than using Intelligent Insights.
C
Enable the Query Store and wait for data collection.
Why wrong: Too slow; Intelligent Insights already has data.
D
Review the Intelligent Insights root cause analysis report.
Provides automated analysis of performance issues.

Full breakdown with real-world context →

Question 3mediummultiple choice

Full question →

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?

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.

Full breakdown with real-world context →

Question 4mediummulti select

Full question →

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?

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.

Full breakdown with real-world context →

Question 5mediummultiple choice

Full question →

Refer to the exhibit. A user reports being unable to connect to the database. What is the most likely cause?

Exhibit

Azure SQL Database error log excerpt:
2023-03-01 14:32:15.67 Login failed for user 'appuser'. Reason: An error occurred while evaluating the password. [CLIENT: 192.168.1.100]
2023-03-01 14:32:16.10 Login failed for user 'appuser'. Reason: Password did not match that for the login provided. [CLIENT: 192.168.1.100]
2023-03-01 14:32:16.55 Login failed for user 'appuser'. Reason: An error occurred while evaluating the password. [CLIENT: 192.168.1.100]

A
The user account is locked out due to too many failed login attempts.
Why wrong: The logs do not show a lockout message; they only show failed login attempts.
B
The user does not have permission to access the database.
Why wrong: The error is about password evaluation, not authorization.
C
The server firewall is blocking the IP address.
Why wrong: The logs show the client IP is reaching the server, so firewall is not blocking.
D
The user is using an incorrect password.
The error 'Password did not match' directly indicates an incorrect password.

Full breakdown with real-world context →

Question 6mediumdrag order

Full question →

Drag and drop the steps to troubleshoot a high CPU usage issue in Azure SQL Database in the correct order.

Drag steps to the numbered slots on the right, or tap a step then tap a slot.

Steps

Order

1Step 1

2Step 2

3Step 3

4Step 4

5Step 5

A
Identify high CPU queries, then Analyze execution plans, then Check for missing indexes, then Implement fixes, then Monitor performance.
This is the correct order because you first need to identify which queries are causing high CPU, then analyze their execution plans to understand inefficiencies, check for missing indexes that could improve performance, implement necessary fixes, and finally monitor to ensure the issue is resolved.
B
Identify high CPU queries, then Check for missing indexes, then Analyze execution plans, then Implement fixes, then Monitor performance.
Why wrong: This order is incorrect because you should analyze execution plans before checking for missing indexes. Without understanding the plan, you might miss other optimization opportunities like query rewriting or parameter sniffing issues.
C
Analyze execution plans, then Identify high CPU queries, then Check for missing indexes, then Implement fixes, then Monitor performance.
Why wrong: This order is incorrect because you must first identify which queries are consuming high CPU before analyzing their plans. Without a targeted approach, you might waste time on plans unrelated to the problem.
D
Identify high CPU queries, then Analyze execution plans, then Implement fixes, then Check for missing indexes, then Monitor performance.
Why wrong: This order is incorrect because you should check for missing indexes before implementing fixes. Indexing is a common remedy; if you implement other fixes first, you might miss a simple optimization or apply a solution that is not needed.

Full breakdown with real-world context →

Question 7easymulti select

Full question →

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?

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.

Full breakdown with real-world context →

Question 8hardmulti select

Full question →

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

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.

Full breakdown with real-world context →

Question 9hardmultiple choice

Full question →

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?

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.

Full breakdown with real-world context →

Question 10mediummultiple choice

Full question →

You run the above KQL query in Azure Monitor Log Analytics to investigate performance issues in SalesDB. What is the primary purpose of this query?

Exhibit

Refer to the exhibit.

```kusto
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.SQL"
| where Category == "QueryStoreRuntimeStatistics"
| where database_name_s == "SalesDB"
| summarize avg_duration = avg(duration_s) by query_id_d, interval_start_time_t
| where avg_duration > 1000
| order by avg_duration desc
```

A
Identify queries with high average duration
Filters for avg_duration > 1000 ms and orders descending.
B
Identify queries that have had plan changes
Why wrong: No plan comparison is done.
C
Find the most frequently executed queries
Why wrong: It summarizes by avg_duration, not count.
D
Compare query performance over different time intervals
Why wrong: It doesn't compare intervals; it averages over intervals.

Full breakdown with real-world context →

These DP-300 practice questions are part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style DP-300 questions with detailed explanations, topic-based practice, mock exams, readiness tracking, and study analytics.

Troubleshooting Scenario Questions

How to approach troubleshooting scenario questions

Quick answer

Related DP-300 topic practice pages

Plan and configure a high availability and disaster recovery environment practice questions

Plan and implement data platform resources practice questions

Monitor, configure, and optimize database resources practice questions

Configure and manage automation of tasks practice questions

Plan and configure high availability and disaster recovery practice questions

Implement a secure environment practice questions

DP-300 fundamentals practice questions

DP-300 scenario practice questions

DP-300 troubleshooting practice questions

Practice scenarios

You are managing an Azure SQL Database that experiences intermittent performance degradation. Query Store shows a significant increase in wait time for PAGEIOLATCH_SH. You need to identify the most likely cause. What should you investigate first?

You are monitoring an Azure SQL Database using Intelligent Insights. You receive an alert that resource usage has exceeded 90% for the past hour. What is the recommended first step to troubleshoot?

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?

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?

Refer to the exhibit. A user reports being unable to connect to the database. What is the most likely cause?

Exhibit

Drag and drop the steps to troubleshoot a high CPU usage issue in Azure SQL Database in the correct order.

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?

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

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?

You run the above KQL query in Azure Monitor Log Analytics to investigate performance issues in SalesDB. What is the primary purpose of this query?

Exhibit