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

Question 1

Which THREE actions can you take to monitor and optimize database resources in Azure SQL Database? (Choose three.)

Accepted Answer

Use Query Store to track query performance over time.. Options B, C, and D are correct. Query Store tracks query performance over time, dynamic management views help identify blocking and resource waits, and automatic tuning automatically implements performance improvements. Option A (Microsoft Defender for SQL) is a security feature for vulnerability detection, not directly for monitoring and optimizing database resources. Option E (Microsoft Sentinel) is a security information and event management (SIEM) tool, not specifically for database monitoring and optimization.

Answer

Enable Microsoft Defender for Azure SQL to detect vulnerabilities.

Answer

Configure Microsoft Sentinel to monitor database activity.

Question 2

You are analyzing the exhibit KQL query that queries Azure Diagnostics logs for Query Store runtime statistics. The query is intended to show average CPU time per hour for each database. However, the result shows no data for the last 24 hours, although Query Store is enabled on all databases. What is the most likely reason?

Accepted Answer

The diagnostic settings are not configured to send QueryStoreRuntimeStatistics to Log Analytics.. For Query Store runtime statistics to appear in Log Analytics, the Azure SQL Database diagnostic settings must explicitly route the QueryStoreRuntimeStatistics category to the Log Analytics workspace. If that category is not enabled, the KQL query will return no rows even though Query Store is enabled on the databases, because the data never reaches the workspace. This is the most likely cause of the empty result set.

Answer

Query Store is not enabled on the databases.

Answer

The time range in the query is too narrow and excludes the last 24 hours.

Answer

The query syntax is incorrect and needs to use 'project' before 'summarize'.

Question 3

You have an Azure SQL Managed Instance used for an e-commerce platform. During a flash sale, you experience a deadlock that causes transaction rollbacks. You need to minimize deadlock occurrences in the future. What should you implement?

Accepted Answer

Enable READ COMMITTED SNAPSHOT isolation level.. Enabling READ COMMITTED SNAPSHOT isolation (RCSI) makes readers use row versioning from tempdb instead of taking shared locks, which eliminates the classic reader-writer deadlock pattern that plagues e-commerce workloads mixing SELECTs and UPDATEs. This directly reduces deadlock frequency without changing application code.

Answer

Configure deadlock graph in Extended Events.

Answer

Enable automatic tuning to force last good plan.

Answer

Increase the instance vCores to improve concurrency.

Question 4

You are monitoring an Azure SQL Database and notice high PAGELATCH waits. What is the most likely cause?

Accepted Answer

Concurrent inserts into a table with a clustered index causing last-page contention.. High PAGELATCH waits in Azure SQL Database are most commonly caused by concurrent inserts into a table with a clustered index, leading to last-page contention. Multiple threads trying to insert into the same page serialize on the page latch, causing PAGELATCH waits. This is a classic OLTP pattern where a monotonically increasing key (like an IDENTITY column) causes all inserts to target the last page.

Answer

Insufficient buffer pool size leading to frequent reads from disk.

Answer

High CPU usage due to inefficient queries.

Answer

Long-running transactions blocking other queries.

Question 5

The query returns a list of query hashes with high average duration. You need to identify which queries are most likely causing CPU pressure. What additional metric should you include?

Accepted Answer

Include avg_cpu_time to measure CPU usage.. To identify queries causing CPU pressure, you need to measure CPU usage per query. The `avg_cpu_time` metric directly indicates how much CPU time each query consumes on average, making it the most relevant additional metric.

Answer

Include wait_stats to see blocking.

Answer

Include count_executions to see frequency.

Answer

Include avg_logical_reads to see I/O consumption.

Question 6

You manage an Azure SQL Database that has automatic tuning enabled. You receive an alert that the database is experiencing plan regression. The automatic tuning has forced a plan, but performance is still poor. What should you do first?

Accepted Answer

Review the Query Store to identify the root cause of regression.. When automatic tuning has forced a plan but performance remains poor, the first step is to investigate the root cause using Query Store, which captures query plans, runtime statistics, and regressions. Query Store provides the historical data needed to understand why the forced plan is not optimal and whether the regression is due to parameter sniffing, statistics, or schema changes. Only after diagnosis should you consider reverting or disabling automatic tuning.

Answer

Disable automatic tuning and create a plan guide.

Answer

Manually revert to the previous plan using Query Store.

Answer

Scale up the database to reduce resource pressure.

Question 7

You have an Azure SQL Database in the General Purpose tier. You notice that the log write throughput is consistently above the service tier limit, causing transaction throttling. You need to resolve this without moving to Business Critical. What should you do?

Accepted Answer

Batch transactions and reduce log writes.. Batching transactions reduces the number of log write operations, lowering the log write throughput below the service tier limit and avoiding throttling. Option A is incorrect because increasing the max log size does not affect the log write rate; it only provides more storage. Option C is incorrect because accelerated database recovery (ADR) improves recovery time and may reduce log I/O for crash recovery, but it does not directly reduce the sustained log write throughput from transaction processing. Option D is incorrect because the requirement explicitly states not to move to Business Critical.

Answer

Increase the max log size using ALTER DATABASE.

Answer

Enable accelerated database recovery to reduce log I/O.

Answer

Move to Business Critical tier.

Question 8

You are monitoring an Azure SQL Database using sys.dm_db_wait_stats. You see a high percentage of WRITELOG waits. What is the most likely cause?

Accepted Answer

The transaction log is on a slow I/O subsystem.. WRITELOG waits occur when a session is waiting for the transaction log to be written to disk, typically at commit time. A high percentage of WRITELOG waits therefore points to slow transaction log I/O — the log disk cannot keep up with the write rate, so commits stall.

Answer

Tempdb has allocation contention.

Answer

Queries are blocked by locks.

Answer

CPU is under pressure.

Question 9

Which TWO metrics are available in Azure Monitor for an Azure SQL Database that can be used to set autoscale rules? (Select two.)

Accepted Answer

CPU percentage. Option A (CPU percentage) is correct because Azure SQL Database exposes the 'cpu_percent' metric through Azure Monitor, which reports the average CPU utilization of the database and is a standard metric used to drive autoscale rules (for example, scaling up when CPU exceeds a threshold). Option D (DTU percentage) is correct because the 'dtu_consumption_percent' metric measures the percentage of the Database Transaction Unit limit consumed and is one of the most commonly used metrics for autoscale decisions on DTU-based databases. The unmarked options do not belong: Log write throughput is not a directly exposed Azure Monitor metric for autoscale on Azure SQL Database, Deadlock count is a diagnostic/query-level statistic rather than an autoscale metric, and Query Store size is an internal Query Store property, not an Azure Monitor metric available for autoscale rules.

Answer

Log write throughput

Answer

Deadlock count

Answer

Query Store size

Question 10

You need to optimize costs for SalesDB, which is used only during business hours (8 AM to 6 PM). The database currently runs 24/7. Which change should you make?

Accepted Answer

Enable serverless with auto-pause enabled.. Azure SQL Database serverless with auto-pause enabled automatically pauses the database after a period of inactivity and resumes on the next connection, so the database incurs no compute charges outside business hours. This directly matches the 8 AM–6 PM usage pattern and reduces cost without manual intervention.

Answer

Reduce storage to 512 GB.

Answer

Change tier to Hyperscale.

Answer

Reduce capacity to 2 vCores.

Question 11

Your Azure SQL Database is experiencing high DTU consumption. You need to identify the top resource-consuming queries. What should you do?

Accepted Answer

Use the Query Store reports in the Azure portal. Query Store in Azure SQL Database provides built-in reports to identify top resource-consuming queries by CPU, IO, and duration, making it the easiest and most direct method. Option B (SQL Server Profiler) is not supported in Azure SQL Database. Option C (Extended Events) is more complex and not the simplest approach for this task. Option D (sys.dm_exec_query_stats) can be used but lacks persistent historical data and is more effort than using Query Store reports.

Answer

Use SQL Server Profiler

Answer

Create an Extended Events session to capture query events

Answer

Query sys.dm_exec_query_stats

Question 12

You have an Azure SQL Managed Instance and notice that automatic tuning is not enabled. You want to automatically force a plan that performed better than the existing plan. What should you enable?

Accepted Answer

Automatic plan correction (FORCE_LAST_GOOD_PLAN). Automatic plan correction (FORCE_LAST_GOOD_PLAN) is the automatic tuning feature that detects plan regressions and forces the last known good plan. It is designed exactly for the scenario where a plan performs worse than a previous plan. Enabling it allows Azure SQL Managed Instance to automatically correct plan choice regressions.

Answer

Automatic index management (DROP INDEX)

Answer

Automatic index management (CREATE INDEX)

Answer

Intelligent Insights

Question 13

Your Azure SQL Database is configured with the Hyperscale service tier. You observe increased redo log latency. Which resource is most likely the bottleneck?

Accepted Answer

Log service throughput. In Hyperscale, the log service is a separate component that handles redo; high latency there directly affects redo speed. Option A is wrong because compute node CPU affects query processing, not redo latency specifically. Option C is wrong because page servers handle data reads, not redo. Option D is wrong because storage IO is distributed and rarely the bottleneck for redo in Hyperscale.

Answer

Compute node CPU

Answer

Page server IO

Answer

Remote storage IOPS

Question 14

Which TWO metrics should you monitor in Azure SQL Database to detect a potential memory pressure issue?

Accepted Answer

Page Life Expectancy. Page Life Expectancy (PLE) is a key metric that indicates how long a data page remains in the buffer pool before being evicted. A consistently low PLE (e.g., below 300 seconds) signals that pages are being flushed too quickly due to memory pressure, often from insufficient buffer pool memory. Memory Grants Pending tracks the number of queries waiting for a memory grant to execute; a non-zero value indicates that the server cannot allocate enough memory to satisfy query workspace requirements, directly pointing to memory pressure.

Answer

Average CPU percent

Answer

Log IO

Answer

Data IO

Question 15

Which THREE actions can you take to optimize query performance in Azure SQL Database using Intelligent Query Processing?

Accepted Answer

Enable adaptive joins. Adaptive joins (A) are an Intelligent Query Processing feature that lets the optimizer defer the choice between a hash join and a nested loops join until runtime, switching to the better plan based on actual row counts and thereby improving performance for queries with inaccurate cardinality estimates. Interleaved execution for MSTVFs (B) is also an IQP feature that lets multi-statement table-valued functions be executed in an interleaved manner so the optimizer can use actual row counts from the function's first execution to produce a better overall plan. Approximate count distinct (E) is an IQP capability (APPROX_COUNT_DISTINCT) that returns statistically accurate distinct counts with much less CPU and memory than exact COUNT(DISTINCT), speeding up aggregation-heavy queries. Query Store (C) is a monitoring and plan-capture feature, not an IQP query-optimization action, and columnstore indexes (D) are a physical data-access/columnar storage technology rather than an Intelligent Query Processing optimization, so neither belongs among the three IQP actions.

Answer

Enable Query Store

Answer

Enable columnstore indexes