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

Question 1

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?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Log write latency. The correct metric to examine is Log write latency (option D), as it directly measures the time taken to write to the transaction log, which can indicate slow performance due to log write latency. Option A (Log IO percent) measures the percentage of log throughput used, not latency. Option B (Transaction log usage) measures log file space used, not performance. Option C (Average IO latency) includes both data and log I/O, so it is not specific to log writes.

Answer

Log IO percent

Answer

Transaction log usage

Answer

Average IO latency

Question 2

You manage an Azure SQL Managed Instance. You need to monitor storage space usage. Which TWO dynamic management views can you use?

Accepted Answer

sys.dm_db_file_space_usage. Options C and D are correct. sys.dm_db_file_space_usage provides data file space usage, and sys.dm_db_log_space_usage provides transaction log space usage. Option A is incorrect because sys.dm_db_partition_stats shows row counts and partition-level information, not storage space. Option B is incorrect because sys.dm_exec_query_stats is for query performance metrics. Option E is incorrect because sys.dm_os_performance_counters includes various performance counters but does not directly show per-database space usage.

Answer

sys.dm_db_partition_stats

Answer

sys.dm_exec_query_stats

Answer

sys.dm_os_performance_counters

Question 3

You have an Azure SQL Database that uses automatic tuning. Which TWO benefits does automatic tuning provide?

Accepted Answer

Automatically identify and correct query plan regressions. Options B and E are correct. Automatic tuning can identify and correct query plan regressions (B) and automatically create missing indexes (E). Option A is wrong because automatic tuning does not automatically scale the service tier; that's auto-scale. Option C is wrong because automatic tuning does not automatically create read replicas. Option D is wrong because automatic tuning does not automatically update statistics.

Answer

Automatically scale up the database service tier

Answer

Automatically create read replicas

Answer

Automatically update statistics

Question 4

You are optimizing an Azure SQL Database that uses the Business Critical tier. Which TWO factors affect the maximum log rate?

Accepted Answer

Service level objective (SLO). The maximum log rate for an Azure SQL Database in the Business Critical tier is determined by the service level objective (SLO) and the number of vCores. Option A is correct because the SLO defines the performance tier and hardware configuration, which directly caps the log generation rate (e.g., Business Critical with 4 vCores has a specific log rate limit). Option B is correct because within a given SLO, the log rate scales with the number of vCores—more vCores allow a higher maximum log rate. Option C is incorrect because backup retention period affects storage and recovery, not log throughput. Option D is incorrect because Azure SQL Database manages log files automatically; the number of log files is not a user-configurable factor affecting log rate. Option E is incorrect because page compression reduces data size and I/O but does not directly determine the maximum log generation rate.

Answer

Backup retention period

Answer

Number of log files

Answer

Page compression level

Question 5

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?

Accepted Answer

Scale up the database to a higher service objective (e.g., S3).. Scaling up the Azure SQL Database from S2 to a higher service objective (e.g., S3) increases the allocated DTUs/vCores, memory, and IOPS, directly addressing resource saturation during peak hours without requiring any application changes. This is the fastest, least invasive remediation when the workload is CPU/IO bound and the tier is the bottleneck. It preserves connection strings, schema, and code, making it the correct first recommendation.

Answer

Enable Query Store and run the Performance Dashboard.

Answer

Enable read scale-out to offload read queries.

Answer

Create nonclustered indexes on all tables.

Question 6

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.. Before acting on Intelligent Insights' missing index recommendation, you should validate it against actual workload evidence. Query Store captures query text, plans, runtime stats, and wait categories, so you can confirm the query is genuinely expensive and that the missing index would help. This avoids creating an index that adds write overhead without meaningful read benefit.

Answer

Scale up the managed instance to a higher tier.

Answer

Enable automatic index tuning.

Answer

Create the recommended index immediately.

Question 7

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.. 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 8

You are monitoring an Azure SQL Database using the sys.dm_db_resource_stats DMV. The avg_log_write_percent column shows 95% for the last hour. What does this indicate, and what should you do?

Accepted Answer

The database is nearing its log write IOPS limit; consider scaling up or optimizing log writes.. The avg_log_write_percent metric in sys.dm_db_resource_stats measures the percentage of the log write IOPS limit used. At 95%, the database is nearing its log write IOPS limit, which can cause transaction delays and throttling. The appropriate response is to scale up the service tier (e.g., to a higher DTU or vCore level) or optimize log writes to reduce IOPS consumption. Option A is incorrect because the metric does not indicate running out of log space; log space is separate. Option B is incorrect because storage scale (size) does not directly affect log write IOPS. Option D is incorrect because this metric is specifically about log I/O, not CPU.

Answer

The database is out of transaction log space; increase the max log size.

Answer

The database storage is running out; scale up storage.

Answer

The CPU is overloaded; scale up CPU.

Question 9

You are optimizing an Azure SQL Database that runs a heavy reporting workload. The database uses the General Purpose tier. You notice that many queries are scanning large tables. What is the best first action to improve performance?

Accepted Answer

Analyze the missing index recommendations from Query Store.. Query Store's missing index recommendations directly identify indexes that, if created, would likely improve the performance of the observed scanning queries. This is the most targeted, low-risk first action because it is based on actual workload data and addresses the root cause (missing indexes causing scans) without changing the service tier or making structural changes. It is also the cheapest and fastest to implement.

Answer

Partition the large tables by date.

Answer

Scale up to Business Critical tier.

Answer

Implement columnstore indexes on all large tables.

Question 10

You need to monitor the storage space usage of an Azure SQL Database over time. Which tool should you use?

Accepted Answer

Azure SQL Analytics (Azure Monitor). Azure SQL Analytics in Azure Monitor provides historical storage metrics. Option A is wrong because Intelligent Insights is for proactive diagnostics, not storage monitoring. Option C is wrong because Query Store focuses on query performance. Option D is wrong because SSMS does not provide historical monitoring.

Answer

Intelligent Insights

Answer

Query Store

Answer

SQL Server Management Studio (SSMS)

Question 11

Which TWO metrics from sys.dm_db_resource_stats should you monitor to identify a disk IO bottleneck in an Azure SQL Database?

Accepted Answer

avg_data_io_percent. The two correct metrics are C, avg_data_io_percent, and E, avg_log_write_percent. avg_data_io_percent reports the percentage of the data-file IOPS/throughput limit consumed by the database, so sustained values near 100% directly indicate a data-file disk IO bottleneck. avg_log_write_percent reports the percentage of the log-write throughput limit consumed, so elevated values reveal a transaction-log write bottleneck. Together these two IO-related counters isolate disk IO pressure in Azure SQL Database. The unmarked options do not belong: avg_cpu_percent (A) measures compute utilization, max_size_percent (B) tracks storage-space consumption against the size limit, and avg_memory_usage_percent (D) measures memory pressure, none of which identify disk IO bottlenecks.

Answer

avg_cpu_percent

Answer

max_size_percent

Answer

avg_memory_usage_percent

Question 12

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. Force the previous query plan using Query Store. This approach directly addresses the root cause by locking the query to a known good plan, ensuring consistent performance without application changes. Option A is incorrect because increasing the DTU/service tier may temporarily improve performance but does not fix the plan regression. Option B is incorrect because creating a missing index recommendation may help but does not guarantee the query will use the previous plan. Option C is incorrect because dropping and recreating the index is disruptive and may not force the plan to revert.

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 13

Your company uses Azure SQL Database with active geo-replication. You notice that the secondary database in a different region has a high log write latency. Users report that the primary database performance is normal. What is the most likely cause?

Accepted Answer

Network latency between the primary and secondary regions. In Azure SQL Database active geo-replication, the secondary database continuously receives transaction log from the primary over the network. High log write latency on the secondary while the primary performs normally points to network latency between the two regions, since the log must traverse the inter-region link and be hardened on the secondary.

Answer

Insufficient log IOPS on the secondary database

Answer

High CPU usage on the primary database

Answer

Excessive read workload on the secondary database

Question 14

You are monitoring an Azure SQL Database using Intelligent Insights. You receive an alert that 'Query performance degradation' was detected. After reviewing the details, you find that a specific query now has a higher duration and is using a different execution plan. What is the recommended first step to troubleshoot?

Accepted Answer

Query the Query Store to compare the previous and current plans. Query Store captures historical execution plans, runtime statistics, and plan changes per query, so it is the correct first step to compare the previous and current plans and identify what regressed (e.g., a plan change, parameter sniffing, or missing index). Intelligent Insights flags the degradation, but Query Store provides the forensic detail needed to diagnose it.

Answer

Restart the database to clear the plan cache

Answer

Increase the service tier of the database

Answer

Run the Database Engine Tuning Advisor

Question 15

You need to monitor the performance of an Azure SQL Database and set up alerts when the DTU consumption exceeds 80% for more than 5 minutes. Which Azure service should you use?

Accepted Answer

Azure Monitor metric alerts. Azure Monitor metric alerts can be configured on DTU percentage. Option B is wrong because Azure Advisor provides recommendations but not real-time alerts. Option C is wrong because Azure SQL Insights is for visualization, not alerting. Option D is wrong because Log Analytics workspaces store logs but do not natively provide metric alerts.

Answer

Azure Advisor

Answer

Azure SQL Insights (preview)

Answer

Log Analytics workspace