DP-300 Monitor, configure, and optimize database resources Practice Test 10 — 15 Questions

Question 1

You are tuning an Azure SQL Database that has a heavy write workload. You need to reduce the number of log writes. Which TWO actions should you take? (Choose two.)

Accepted Answer

Use delayed durability for transactions. Option A is correct because larger batch sizes reduce log writes per row. Option D is correct because using delayed durability reduces log flush frequency. Option B is wrong because simple recovery model is not available in Azure SQL Database. Option C is wrong because minimal logging is not fully supported. Option E is wrong because index rebuilds generate log writes.

Answer

Change the database recovery model to SIMPLE

Answer

Use minimal logging for bulk operations

Answer

Rebuild indexes during peak hours

Question 2

You are monitoring an Azure SQL Database and notice that the 'tempdb' database is experiencing contention. Which THREE actions can reduce tempdb contention? (Choose three.)

Accepted Answer

Add multiple tempdb data files. Option A correct because adding files reduces allocation contention. Option C correct because using multiple tempdb files distributes contention. Option D correct because optimizing queries that use tempdb reduces load. Option B wrong because increasing size does not reduce contention. Option E wrong because changing collation is unrelated.

Answer

Increase the size of tempdb

Answer

Change the collation of tempdb

Question 3

You need to configure monitoring for an Azure SQL Database to meet the following requirements: 
- Alert when average DTU consumption exceeds 90% for 10 minutes.
- Track failed logins.
- Analyze query performance over the last 30 days.
Which THREE Azure services or features should you use? (Choose three.)

Accepted Answer

SQL auditing. Option A correct because Azure Monitor alerts can alert on DTU. Option C correct because audit logs track failed logins. Option D correct because Query Store retains query performance data. Option B wrong because SQL Assessment checks best practices, not performance. Option E wrong because Autoscale is not manual monitoring.

Answer

Automatic tuning

Answer

Azure SQL Assessment

Question 4

You manage an Azure SQL Database that is experiencing higher than expected DTU consumption. You need to identify which queries are consuming the most resources. Which dynamic management view should you query?

Accepted Answer

Query sys.dm_db_resource_stats. sys.dm_db_resource_stats provides per-minute resource consumption data for the database. Option A is wrong because sys.dm_exec_query_stats is not available in Azure SQL Database (it is for SQL Server). Option C is wrong because sys.dm_os_wait_stats is for server-level waits. Option D is wrong because sys.dm_exec_requests shows only currently executing requests.

Answer

Query sys.dm_exec_requests

Answer

Query sys.dm_os_wait_stats

Answer

Query sys.dm_exec_query_stats

Question 5

You have an Azure SQL Managed Instance with a large number of databases. You need to monitor the storage space used by each database to proactively manage capacity. Which tool should you use?

Accepted Answer

Azure SQL Analytics (preview). Azure SQL Analytics (preview) in Azure Monitor provides a solution specifically for monitoring Azure SQL databases and managed instances, including storage usage per database. Option A is wrong because Query Performance Insight focuses on query performance. Option C is wrong because Automatic tuning is for performance optimization. Option D is wrong because Intelligent Insights provides proactive diagnostics but not per-database storage monitoring.

Answer

Query Performance Insight

Answer

Automatic tuning

Answer

Intelligent Insights

Question 6

You need to configure automatic tuning for an Azure SQL Database to automatically identify and fix performance issues. Which two tuning options can be enabled?

Accepted Answer

FORCE LAST GOOD PLAN. Automatic tuning in Azure SQL Database includes FORCE LAST GOOD PLAN and CREATE INDEX. DROP INDEX is not an automatic tuning option. Option A and B are the correct ones. Option C is wrong because automatic tuning does not include DROP INDEX. Option D is wrong because it includes DROP INDEX.

Answer

DROP INDEX

Answer

FORCE LAST GOOD PLAN and DROP INDEX

Question 7

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 8

You are reviewing an Azure Resource Manager template snippet for configuring long-term backup retention for an Azure SQL Database. The deployment fails with an error indicating the storage account is not accessible. What is the most likely cause?

Accepted Answer

The SAS token has expired or is invalid. The exhibit shows a SAS token in the template. The most common issue is that the SAS token has expired or is invalid. Option B is correct. Option A is wrong because the storage container URI is valid. Option C is wrong because the location is valid. Option D is wrong because the SAS token is used for authentication.

Answer

The server name in the template does not match the actual server

Answer

The storage container URI is incorrectly formatted

Answer

The database is not in the same region as the storage account

Question 9

You are monitoring an Azure SQL Database and notice that the average CPU usage is consistently above 90%. The database is using the S3 service tier. What should you do first to resolve this performance issue?

Accepted Answer

Scale up to a higher service tier. Scaling up to a higher service tier (e.g., S4) provides more CPU resources. Option A is correct. Option B is wrong because the database is not at the maximum tier (S12 is max for Standard). Option C is wrong because enabling read scale-out does not increase CPU. Option D is wrong because query store recommendations take time.

Answer

Enable read scale-out

Answer

Wait for Automatic tuning to provide recommendations

Answer

Scale down to a lower service tier

Question 10

You need to configure alerts for an Azure SQL Database to notify the operations team when the database exceeds 80% DTU consumption for more than 10 minutes. What should you use?

Accepted Answer

Create a metric alert in Azure Monitor. Azure Monitor can create metric alerts based on DTU consumption. Option B is correct. Option A is wrong because SQL Agent cannot send alerts based on DTU. Option C is wrong because Azure Advisor provides recommendations but not real-time alerts. Option D is wrong because Azure SQL Auditing is for compliance.

Answer

Configure a SQL Agent alert

Answer

Configure Azure SQL Auditing

Answer

Use Azure Advisor recommendations

Question 11

You have an Azure SQL Database that uses the Hyperscale service tier. You notice that the log rate is frequently throttled. Which configuration change can help reduce log rate throttling?

Accepted Answer

Increase the log rate limit by scaling up the service level objective. Increasing the log rate limit allows more transactions per second. Option D is correct. Option A is wrong because Hyperscale does not have compute replicas for log rate. Option B is wrong because changing backup retention does not affect log rate. Option C is wrong because increasing max degree of parallelism may not reduce log generation.

Answer

Increase max degree of parallelism

Answer

Reduce backup retention period

Answer

Add more compute replicas

Question 12

You need to monitor the performance of a set of Azure SQL Databases from a single dashboard. Which Azure service should you use?

Accepted Answer

Azure SQL Analytics. Azure SQL Analytics provides a unified monitoring solution for multiple Azure SQL databases. Option C is correct. Option A is wrong because Query Performance Insight is per-database. Option B is wrong because Azure Monitor Workbooks can be used but require manual configuration. Option D is wrong because Azure Data Studio is a client tool.

Answer

Azure Monitor Workbooks

Answer

Query Performance Insight

Answer

Azure Data Studio

Question 13

You are tuning a query in Azure SQL Database. Which TWO actions can reduce logical reads?

Accepted Answer

Create a nonclustered index on the columns used in WHERE clause. Creating appropriate indexes can reduce logical reads by enabling seeks instead of scans. Updating statistics helps the optimizer choose efficient plans. Option A and C are correct. Option B is wrong because increasing memory does not reduce logical reads directly. Option D is wrong because rewriting as a stored procedure does not reduce logical reads.

Answer

Add query hints to force index usage

Answer

Rewrite the query as a stored procedure

Answer

Increase the database max memory setting

Question 14

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

Accepted Answer

Use a hash distribution or round-robin distribution in a table design. PAGELATCH_EX waits are due to contention on the last page of indexes (often due to sequential inserts). Using OPTIMIZE_FOR_SEQUENTIAL_KEY, partitioning, and using hash or round-robin distributions can reduce contention. Option A, B, and C are correct. Option D is wrong because increasing MAXDOP may increase contention. Option E is wrong because snapshot isolation does not directly reduce PAGELATCH_EX.

Answer

Increase MAXDOP for the queries

Answer

Enable snapshot isolation level

Question 15

Which TWO metrics in Azure SQL Database indicate that the database might need to be scaled up?

Accepted Answer

Log write percent consistently above 90%. High DTU/CPU consumption and high log write percent indicate the database is hitting resource limits. Option A and D are correct. Option B is wrong because low data IO is not an indicator. Option C is wrong because low memory indicates underutilization. Option E is wrong because low session percent is not a scaling trigger.

Answer

Data IO percentage consistently below 20%

Answer

Session percent consistently below 10%

Answer

Memory consumption consistently below 30%