DP-300 Practice Test 25 — 15 Questions

Question 1

You manage an Azure SQL Managed Instance that hosts a critical OLTP database. You notice that the average CPU usage is consistently above 90% during business hours. You have enabled Intelligent Insights, which recommends creating a missing index. What should you do first to validate the recommendation before implementing it?

Accepted Answer

Use Query Store to review query performance and missing index details.. Option B is correct because you should use Query Store to identify the specific queries causing high CPU and verify the index impact. Option A is wrong because automatic index tuning could be risky without validation. Option C is wrong because scaling up may not address the root cause. Option D is wrong because the index already exists in the recommendation.

Answer

Scale up the managed instance to a higher tier.

Answer

Enable automatic index tuning.

Answer

Create the recommended index immediately.

Question 2

Which TWO database properties can be configured to optimize performance for a read-heavy workload in Azure SQL Database?

Accepted Answer

Query Store. Options B and D are correct. Read scale-out offloads read queries to a secondary replica; enabling Query Store helps identify performance issues. Option A is a security feature. Option C is for indexing. Option E is for data retention.

Answer

Data masking

Answer

Long-term retention policy

Answer

Automatic tuning (CREATE INDEX)

Question 3

You have an Azure SQL Database that is part of an elastic pool. You notice that the pool's eDTU consumption is consistently high, and some databases are experiencing resource contention. You need to ensure that a critical database always gets a minimum amount of resources. What should you configure?

Accepted Answer

Configure per-database min eDTU for the critical database. Option B is correct because per-database min eDTU guarantees resources for a database. Option A is wrong because per-database max eDTU limits resource usage, not guarantee. Option C is wrong because DTU increase for the pool may not guarantee resources for a specific database. Option D is wrong because moving to a different pool may not be efficient.

Answer

Configure per-database max eDTU for the critical database

Answer

Increase the eDTU of the elastic pool

Answer

Move the critical database to a dedicated service tier

Question 4

Your Azure SQL Managed Instance is configured with a long-term backup retention policy of 10 years. You need to reduce storage costs while still meeting a compliance requirement to retain monthly backups for 7 years. What should you do?

Accepted Answer

Configure a new long-term retention policy that retains monthly backups for 7 years.. Option D is correct because configuring a long-term retention (LTR) policy with monthly backups for 7 years meets compliance while reducing cost by not retaining weekly/daily backups for the full 10 years. Option A is wrong because disabling LTR entirely loses compliance. Option B is wrong because moving to Azure SQL Database does not automatically reduce cost and may not be feasible. Option C is wrong because point-in-time restore (PITR) retention is limited to 35 days, not 7 years.

Answer

Disable long-term backup retention and rely solely on point-in-time restore backups.

Answer

Set the point-in-time restore retention period to 7 years.

Answer

Migrate the database to Azure SQL Database and use geo-redundant storage.

Question 5

You are monitoring an Azure SQL Database that is experiencing high DTU usage. Which TWO metrics should you examine to determine whether the bottleneck is CPU or I/O?

Accepted Answer

Log write bytes per second.. Options B and D are correct. CPU percentage directly indicates CPU usage, and log write bytes/sec indicates I/O activity for writes. Option A (deadlocks) is not a bottleneck. Option C (session count) shows concurrency, not resource usage. Option E (storage space) is capacity, not performance.

Answer

Number of deadlocks per second.

Answer

Used storage space in GB.

Answer

Number of active sessions.

Question 6

You are monitoring an Azure SQL Database using dynamic management views (DMVs). You want to identify the top queries by total CPU time over the last hour. Which DMV should you query?

Accepted Answer

sys.dm_exec_query_stats. Option B is correct because sys.dm_exec_query_stats provides aggregated performance statistics including CPU time. Option A is wrong because it shows currently executing queries, not historical. Option C is wrong because it shows resource governance stats. Option D is wrong because it shows index usage.

Answer

sys.dm_exec_requests

Answer

sys.dm_db_index_usage_stats

Answer

sys.dm_db_resource_stats

Question 7

The database 'mydb' is experiencing performance issues during peak hours. Based on the exhibit, what is the most likely cause?

Accepted Answer

The service tier S3 is not sufficient for the workload.. Option A is correct because the S3 tier has low DTU (100 DTU) and is likely overwhelmed. Option B is wrong because auto-pause delay is set to 3600 seconds (1 hour) and would not cause issues during active use. Option C is wrong because zone redundancy does not affect performance. Option D is wrong because read scale is disabled but not needed for primary workload.

Answer

Zone redundancy is disabled, causing failover delays.

Answer

Read scale is disabled, increasing load on primary.

Answer

The database is auto-pausing frequently due to autoPauseDelay.

Question 8

You administer an Azure SQL Database that uses the General Purpose tier. Users report that queries are slow during peak hours. You need to identify if the slow performance is due to log write latency. Which metric should you examine in Azure Monitor?

Accepted Answer

Log write latency. Option C is correct because 'Log write latency' directly measures the time to write to the log. Option A is wrong because 'Average IO latency' includes data and log. Option B is wrong because 'Log IO percent' measures log throughput, not latency. Option D is wrong because 'Transaction log usage' measures log file space used.

Answer

Log IO percent

Answer

Transaction log usage

Answer

Average IO latency

Question 9

Your Azure SQL Database is configured with the Hyperscale service tier. You observe that log write latency is consistently high, affecting transaction throughput. What is the most likely cause and the recommended mitigation?

Accepted Answer

High log generation rate is causing log rate governance throttling; reduce the log generation rate by batching transactions.. Option C is correct because in Hyperscale, log rate is governed to protect secondary replicas; high latency indicates throttling. Option A is wrong because Hyperscale uses local SSD for log, not PIOPS. Option B is wrong because log rate governance affects all log writes. Option D is wrong because scaling up compute doesn't increase log throughput limits.

Answer

The log IOPS is limited by the disk performance; increase the provisioned IOPS.

Answer

The compute replica is undersized; scale up the compute to increase log throughput.

Answer

The log write latency is due to network congestion; move the database to a different region.

Question 10

You are managing an Azure SQL Database that is experiencing intermittent performance degradation. Query Store shows that a specific query's execution plan changed, causing increased CPU usage. You need to ensure consistent performance without rewriting the application. What should you do?

Accepted Answer

Force the previous query plan using Query Store. Option C is correct because forcing the previous known good plan via Query Store plan forcing stabilizes performance without code changes. Option A is wrong because index tuning may not revert the plan. Option B is wrong because dropping and recreating the index is disruptive. Option D is wrong because DTU increase addresses symptoms not root cause.

Answer

Increase the DTU/service tier of the database

Answer

Create a missing index recommendation

Answer

Drop and recreate the index used by the query

Question 11

You are monitoring an Azure SQL Managed Instance using dynamic management views. You want to identify which queries are consuming the most IOPS. Which DMV should you query?

Accepted Answer

sys.dm_db_io_stats. Option A is correct because sys.dm_db_io_stats provides IO statistics per database. Option B is wrong because sys.dm_exec_query_stats provides query execution stats but not IOPS specifically. Option C is wrong because sys.dm_db_index_usage_stats provides index usage, not IOPS. Option D is wrong because sys.dm_os_performance_counters provides OS-level counters, not per-query IOPS.

Answer

sys.dm_os_performance_counters

Answer

sys.dm_exec_query_stats

Answer

sys.dm_db_index_usage_stats

Question 12

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?

Accepted Answer

Use Query Performance Insight to identify and optimize top resource-consuming queries. Query Performance Insight helps identify the top resource-consuming queries. Optimizing those queries is the most direct way to reduce DTU consumption. Option A is wrong because scaling up increases cost and may not address root cause. Option B is wrong because index maintenance can increase DTU temporarily. Option D is wrong because increasing the log rate limit does not reduce DTU consumption.

Answer

Increase the log rate limit

Answer

Scale up the database to a higher service tier

Answer

Rebuild all indexes in the database

Question 13

You are the database administrator for a hybrid environment with on-premises SQL Server 2022 and Azure SQL Database. All databases use the AdventureWorks schema. You notice that a critical stored procedure runs slower on Azure SQL Database than on-premises. Both have identical indexes and statistics. What is the most likely cause?

Accepted Answer

The version of the cardinality estimator differs between environments.. Option C is correct because differences in the cardinality estimation model (e.g., legacy CE vs. default CE) can cause different plan choices, even with identical schema and stats. Option A is wrong because DTU vs. vCore is a purchasing model, not a performance feature that inherently slows queries. Option B is wrong because if indexes are identical, fragmentation impact should be similar. Option D is wrong because intelligent query processing is generally beneficial, not detrimental, and differences would be minimal.

Answer

Azure SQL Database uses a different resource governance model (DTU vs vCore).

Answer

The index fragmentation level is higher on Azure SQL Database due to automatic tuning.

Answer

Azure SQL Database has intelligent query processing features that are not available on-premises.

Question 14

Refer to the exhibit. You have configured the automatic tuning policy as shown. After a week, you notice that an index has been dropped automatically, causing a critical query to run slowly. What should you do to prevent this in the future while still benefiting from automatic tuning?

Accepted Answer

Set the dropIndex option state to Disabled in the tuning policy.. Option C is correct because disabling the dropIndex option prevents automatic index drops while keeping forcePlan and createIndex enabled. Option A is wrong because disabling all automatic tuning removes all benefits. Option B is wrong because reverting by creating a manual index does not prevent future drops. Option D is wrong because Query Store does not prevent index drops.

Answer

Manually create the dropped index and mark it as a required index.

Answer

Enable Query Store to track index usage.

Answer

Disable automatic tuning entirely.

Question 15

Your company plans to migrate a large on-premises SQL Server database to Azure SQL Managed Instance. The database uses Transparent Data Encryption (TDE) with a certificate stored in the local machine store. How should you manage the TDE protector after migration to ensure minimal administrative overhead and high availability?

Accepted Answer

Use Azure Key Vault to store the TDE protector. Option B is correct because using Azure Key Vault as the TDE protector is recommended for centralized management and high availability. Option A is wrong because SQL Managed Instance does not support TDE with a certificate stored in the instance; it must use Azure Key Vault or service-managed. Option C is wrong because service-managed keys have less control and are not recommended for production. Option D is wrong because TDE cannot be disabled after migration; it must be managed.

Answer

Use the service-managed TDE key provided by Azure

Answer

Bring your own certificate and store it in the managed instance

Answer

Disable TDE after migration and use Always Encrypted instead