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

Scenario-based practice

Hard Difficulty 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 testRead exam guide
20
scenario questions
DP-300
exam code
Microsoft
vendor

Scenario guide

How to approach hard difficulty questions

These are the questions most candidates get wrong. They require connecting multiple concepts, reading tricky output, or knowing edge-case behaviour that isn't on most study cards. Practising them trains you to operate under uncertainty — a necessary skill on the real exam.

Quick answer

Hard Difficulty 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.

Related practice questions

Related DP-300 topic practice pages

Scenario questions usually connect to one or more exam topics. Use these links to review the underlying concepts behind the scenario.

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

Practise DP-300 questions linked to Plan and configure a high availability and disaster recovery environment.

Plan and implement data platform resources practice questions

Practise DP-300 questions linked to Plan and implement data platform resources.

Monitor, configure, and optimize database resources practice questions

Practise DP-300 questions linked to Monitor, configure, and optimize database resources.

Configure and manage automation of tasks practice questions

Practise DP-300 questions linked to Configure and manage automation of tasks.

Plan and configure high availability and disaster recovery practice questions

Practise DP-300 questions linked to Plan and configure high availability and disaster recovery.

Implement a secure environment practice questions

Practise DP-300 questions linked to Implement a secure environment.

DP-300 fundamentals practice questions

Practise DP-300 questions linked to DP-300 fundamentals.

DP-300 scenario practice questions

Practise DP-300 questions linked to DP-300 scenario.

DP-300 troubleshooting practice questions

Practise DP-300 questions linked to DP-300 troubleshooting.

Practice set

Practice scenarios

Question 1hardmultiple choice
Full question →

A company has an Azure SQL Database with a large table that is frequently updated. They notice performance degradation due to index fragmentation. Which maintenance strategy should you recommend to minimize impact on availability and performance?

Question 2hardmulti select
Full question →

You are responsible for performance tuning of an Azure SQL Database that hosts a customer relationship management (CRM) application. The database has several tables with millions of rows. Users report that a report query that joins four tables is slow. You examine the query execution plan and notice that the database engine is using an Index Spool (Lazy Spool) operator. Which TWO actions should you take to improve query performance? (Choose two.)

Question 3hardmultiple choice
Full question →

A company runs SQL Server 2019 on Azure Virtual Machines in an availability set. They need to achieve high availability for a critical database with automatic failover and no shared storage. The solution must minimize downtime during planned maintenance. What should they implement?

Question 4hardmulti select
Full question →

You are designing an automated backup strategy for Azure SQL Database. Which TWO actions satisfy a requirement to maintain daily backups for 30 days and ensure recoverability if the Azure region becomes unavailable?

Question 5hardmulti select
Full question →

You are monitoring an Azure SQL Database using Query Performance Insight and notice that a specific query has a high average duration and high CPU usage. The query plan shows a clustered index scan on a large table. Which two actions should you take to optimize performance? (Choose two.)

Question 6hardmultiple choice
Full question →

You are designing a database solution for an e-commerce application that requires high read throughput with sub-5 ms latency. The application runs on Azure VMs. You need to choose between Azure SQL Database and Azure SQL Managed Instance. Which factor most strongly supports choosing Azure SQL Database over SQL Managed Instance?

Question 7hardmulti select
Full question →

You are automating index maintenance for an Azure SQL Database. Which THREE of the following should you consider to minimize performance impact?

Question 8hardmultiple choice
Full question →

You are responsible for automating backups of on-premises SQL Server databases to Azure Blob Storage. The solution must use the least administrative effort and provide point-in-time restore capability. What should you implement?

Question 9hardmultiple choice
Full question →

You are the database administrator for an Azure SQL Managed Instance hosting a data warehouse workload. You notice that the storage space consumed by the database is significantly larger than expected. The database has multiple large tables with clustered columnstore indexes. You suspect that the columnstore indexes have become fragmented and that deleted rows are consuming space. You need to reclaim storage space with minimal impact on query performance during business hours. What should you do?

Question 10hardmultiple choice
Full question →

You are troubleshooting an Always On Availability Group named AG1. The exhibit shows the current state. The primary replica shows PENDING_FAILOVER. The secondary replica is DISCONNECTED with error 35202. What is the most likely cause of this issue?

Exhibit

Refer to the exhibit.

```
-- Current state of the availability group
SELECT ag.name AS ag_name, 
       replica_server_name, 
       role_desc, 
       operational_state_desc, 
       connected_state_desc, 
       synchronization_health_desc, 
       last_connect_error_number, 
       last_connect_error_time
FROM sys.dm_hadr_availability_replica_states rs
JOIN sys.availability_groups ag ON rs.group_id = ag.group_id
WHERE ag.name = 'AG1';
```

Output:
ag_name | replica_server_name | role_desc | operational_state_desc | connected_state_desc | synchronization_health_desc | last_connect_error_number | last_connect_error_time
AG1     | SQLVM-Primary        | PRIMARY   | PENDING_FAILOVER       | CONNECTED            | HEALTHY                     | 0                         | NULL
AG1     | SQLVM-Secondary      | SECONDARY | ONLINE                 | DISCONNECTED         | NOT_HEALTHY                 | 35202                     | 2024-03-15 10:30:00.000
Question 11hardmultiple choice
Full question →

A retail company is migrating its on-premises SQL Server database to Azure SQL Database. The database has a table with 500 million rows and receives 10,000 INSERT operations per second during peak hours. The application requires read-committed snapshot isolation. Which Azure SQL Database tier and configuration should the DBA recommend to minimize cost while meeting performance requirements?

Question 12hardmultiple choice
Full question →

A company has an Azure SQL Managed Instance that is experiencing high CPU usage. The DBA observes that a specific query is causing high compile time due to parameter sniffing. The query is executed frequently with varying parameter values. Which approach should the DBA use to reduce CPU usage without changing the T-SQL code?

Question 13hardmulti select
Full question →

Which THREE of the following are features available in Azure SQL Managed Instance that are not available in Azure SQL Database (single database)?

Question 14hardmultiple choice
Full question →

A DBA is migrating a large on-premises database to Azure SQL Database using the Data Migration Assistant (DMA). The migration fails with an error indicating that the source database contains cross-database queries. What is the best remediation?

Question 15hardmultiple choice
Full question →

Refer to the exhibit. An Azure SQL Database administrator sees the error in the SQL Server error log. The database is in the General Purpose tier with 100 GB of storage. Which action should the DBA take to resolve the issue?

Exhibit

Refer to the exhibit.

Exhibit:
```
Event ID: 4140
Severity: 16
Message: Could not allocate space for object 'sys.sysrscols' in database 'mydb' because the 'PRIMARY' filegroup is full.
```
Question 16hardmultiple choice
Full question →

Refer to the exhibit. An Azure SQL Database is experiencing performance degradation. Based on the Extended Events and wait statistics, which is the most likely root cause?

Exhibit

Extended Events session trace:

name: system_health
session_id: 5
total_events: 12450
event_name: sql_statement_completed
cpu_time: 2500 ms
logical_reads: 45000
duration: 3000 ms

name: system_health
session_id: 5
total_events: 12451
event_name: sql_statement_completed
cpu_time: 3200 ms
logical_reads: 62000
duration: 4000 ms

Wait statistics from sys.dm_os_wait_stats:

wait_type: PAGEIOLATCH_SH
waiting_tasks_count: 500000
wait_time_ms: 120000

wait_type: LCK_M_S
waiting_tasks_count: 200
wait_time_ms: 500
Question 17hardmultiple choice
Full question →

Refer to the exhibit. An Azure SQL Database in the Standard tier (S2: 50 DTU) is consistently showing high DTU consumption. Which action would most effectively reduce DTU usage?

Exhibit

Azure Monitor metric for Azure SQL Database:

Metric: dtu_consumption_percent
Time range: Past hour
Average: 95%
Max: 100%
Min: 30%

Metric: cpu_percent
Average: 80%
Max: 95%

Metric: physical_data_read_percent
Average: 90%
Max: 100%

Metric: log_write_percent
Average: 20%

Query Store top resource consumers:

Query ID: 1234
Execution count: 5000
Total logical reads: 2.5 million
Total duration: 10,000 ms

Query ID: 5678
Execution count: 100
Total logical reads: 50,000
Total duration: 500 ms
Question 18hardmultiple choice
Full question →

Refer to the exhibit. The SalesDB database is experiencing log space full errors. Based on the exhibit, what is the most likely reason?

Exhibit

Azure CLI output from 'az sql db list-editions -o table':

Edition       | SLO        | DTU | MaxStorageGB
GeneralPurpose | GP_Gen5_2  | 100 | 512
GeneralPurpose | GP_Gen5_4  | 200 | 512
GeneralPurpose | GP_Gen5_8  | 400 | 1024
BusinessCritical | BC_Gen5_2 | 100 | 512
BusinessCritical | BC_Gen5_4 | 200 | 512

Current database properties:

Name: SalesDB
Edition: GeneralPurpose
ServiceObjective: GP_Gen5_2
Storage used: 480 GB
Max storage: 512 GB

Query Store shows:

Total log write rate: 5 MB/s
Log IO percentage: 80%

sys.dm_db_log_space_usage:

Total log space: 100 GB
Used log space: 95 GB
Question 19hardmultiple choice
Full question →

Refer to the exhibit. An automatic tuning recommendation to force the last good plan is active. What should the database administrator do next?

Exhibit

Azure SQL Database automatic tuning recommendations:

Recommendation ID: 1
Type: CREATE_INDEX
Reason: Improve query performance
Score: 80
State: Active
Impacted queries: 3

Recommendation ID: 2
Type: DROP_INDEX
Reason: Index not used
Score: 90
State: Pending verification
Impacted queries: 0

Recommendation ID: 3
Type: FORCE_LAST_GOOD_PLAN
Reason: Regression detected
Score: 100
State: Active
Impacted queries: 1

Current query performance metrics for impacted query of recommendation 3:

Before plan change: avg CPU 500 ms, avg duration 1000 ms
After plan change: avg CPU 1000 ms, avg duration 2000 ms
Question 20hardmultiple choice
Full question →

You manage an Azure SQL Managed Instance with a large database. You notice that the automatic tuning recommendations are not being applied. You need to ensure that automatic tuning is enabled for the instance. Which PowerShell cmdlet should you run?

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.