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

Question 1

Your Azure SQL Database is experiencing a sudden increase in wait time due to PAGEIOLATCH_SH waits. What should you do to reduce these waits?

Accepted Answer

Add appropriate indexes to reduce table scans. PAGEIOLATCH_SH waits indicate I/O bottlenecks caused by excessive page reads from disk. Adding appropriate indexes reduces the number of pages read by enabling more efficient data access (e.g., index seeks instead of table scans), directly reducing I/O. Option A is incorrect because increasing max memory does not address the underlying query inefficiency driving I/O. Option C is incorrect because page compression reduces storage but may increase CPU and does not primarily reduce I/O waits. Option D is incorrect because forcing parameterization improves plan reuse but does not target I/O reduction.

Answer

Increase the database max memory

Answer

Enable page compression on large tables

Answer

Force parameterization of queries

Question 2

You need to configure a long-term retention policy for backups of an Azure SQL Database that must retain weekly full backups for 5 years and monthly full backups for 10 years. Which backup retention feature should you use?

Accepted Answer

Long-Term Retention (LTR) policy. Long-Term Retention (LTR) policies in Azure SQL Database are specifically designed to retain full backups for extended periods — up to 10 years — with configurable weekly, monthly, and yearly retention. To keep weekly full backups for 5 years and monthly full backups for 10 years, the consultant must configure an LTR policy with the appropriate weekly and monthly retention settings.

Answer

Geo-restore feature

Answer

Automated backups retention period

Answer

Point-In-Time Restore (PITR) retention

Question 3

You are monitoring an Azure SQL Database using Intelligent Insights. You receive an alert indicating 'Degradation in performance due to increased log write wait time'. What is the most likely cause of this issue?

Accepted Answer

The log rate limit has been reached due to high transaction throughput. High log write wait times typically indicate that the transaction log throughput is a bottleneck, often due to the log rate limit. Option A is wrong because high CPU utilization would cause other wait types like SOS_SCHEDULER_YIELD, not WRITELOG. Option B is wrong because long-running blocking transactions cause wait types like LCK_M_*, not increased log write wait time. Option D is wrong because insufficient storage space for data files causes different symptoms, such as write errors or data file growth issues, but not specifically log write wait.

Answer

High CPU utilization on the database server

Answer

Long-running blocking transactions

Answer

Insufficient storage space for data files

Question 4

You need to recommend a performance monitoring solution for a new Azure SQL Managed Instance deployment. The solution must provide historical query performance data and the ability to compare performance before and after index changes. What should you include in the recommendation?

Accepted Answer

Query Store with custom retention settings. Query Store is the only feature that persists query execution plans and runtime statistics in the database, enabling historical performance analysis and before/after comparisons for index changes. It captures query text, plans, and runtime metrics over time, which is exactly what is needed to compare performance before and after index modifications. Custom retention settings allow you to control how long the data is kept, ensuring you have the necessary history.

Answer

SQL Server DMVs

Answer

Azure SQL Analytics solution in Log Analytics

Answer

Azure SQL Database Intelligent Insights

Question 5

You are configuring automatic tuning for an Azure SQL Database. Which THREE recommendations can be applied automatically without manual approval?

Accepted Answer

FORCE LAST GOOD PLAN. Azure SQL Database automatic tuning can automatically apply three types of recommendations: FORCE LAST GOOD PLAN (A), which reverts a query to a previously known good execution plan when a plan regression is detected; CREATE INDEX (C), which adds missing indexes identified by the query optimizer; and DROP INDEX (D), which removes redundant or unused indexes. These three are the built-in automatic tuning actions that Azure SQL Database can execute without manual approval, provided automatic tuning is enabled. Modifying statistics (B) is not an automatic tuning action — statistics updates are handled separately by the database engine's automatic statistics maintenance, not by the automatic tuning feature. Enabling database compression (E) is a manual performance optimization and is not part of the automatic tuning recommendation set.

Answer

Modify statistics

Answer

Enable database compression

Question 6

You are a database administrator for a large e-commerce platform using Azure SQL Database. You notice that a specific query frequently causes high CPU usage during peak hours. The query is a SELECT with multiple JOINs and a WHERE clause on a non-clustered index. You have already updated statistics and rebuilt indexes. What should you do next to optimize performance?

Accepted Answer

Use Query Store to identify and force a better execution plan.. When statistics are updated and indexes rebuilt but a specific query still causes high CPU, the next step is to use Query Store to identify a better execution plan and force it. Query Store captures plan history and runtime stats, so you can pinpoint a previously good plan (e.g., before a plan regression) and force it, immediately stabilizing performance without schema changes.

Answer

Enable automatic tuning to let Azure SQL Database handle the issue.

Answer

Add more indexes on the columns used in JOINs and WHERE clause.

Answer

Create a read replica and offload the query to it.

Question 7

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. sys.dm_exec_query_stats exposes cumulative per-plan statistics including total_worker_time, which represents total CPU time consumed by a query plan. Aggregating and ordering by total_worker_time identifies the top CPU-consuming queries. It is the standard DMV for query-level CPU analysis in Azure SQL Database.

Answer

sys.dm_exec_requests

Answer

sys.dm_db_index_usage_stats

Answer

sys.dm_db_resource_stats

Question 8

You are configuring automatic tuning for an Azure SQL Database. The database has a heavy OLTP workload. You want to automatically correct query plan choice regressions without manual intervention. Which automatic tuning option should you enable?

Accepted Answer

FORCE_LAST_GOOD_PLAN. FORCE_LAST_GOOD_PLAN, is the correct automatic tuning option for Azure SQL Database to automatically correct query plan choice regressions. When the database engine detects that a newly compiled query plan performs worse than the previously known good plan, it can automatically force the last known good plan without manual intervention, which is ideal for a heavy OLTP workload where performance stability is critical.

Answer

DROP_INDEX

Answer

CREATE_INDEX

Answer

CORRECT_INDEX

Question 9

You need to monitor the long-running queries in an Azure SQL Database. Which dynamic management view should you query to see queries that have been running for more than 30 seconds?

Accepted Answer

sys.dm_exec_requests. sys.dm_exec_requests is the correct DMV because it returns one row per active request currently executing on the SQL Server instance, including the total elapsed time. You can filter on total_elapsed_time > 30000 to find queries running longer than 30 seconds. It also provides session_id, status, command, and wait information, making it ideal for identifying long-running queries.

Answer

sys.dm_db_resource_stats

Answer

sys.dm_exec_sessions

Answer

sys.dm_exec_query_stats

Question 10

Which THREE metrics should you monitor to proactively detect potential performance issues in an Azure SQL Database?

Accepted Answer

Log IO percentage (sys.dm_db_resource_stats). Option A (Log IO percentage via sys.dm_db_resource_stats) is correct because this DMV reports resource utilization such as log write percentage against the service-tier limits, so sustained high log IO percentage signals throttling and impending performance degradation. Option D (Wait statistics via sys.dm_os_wait_stats) is correct because aggregating wait types reveals where sessions are blocked or stalled (for example PAGEIOLATCH, CXPACKET, or WRITELOG), which is the standard method for diagnosing the root cause of slow performance. Option E (Query Store for query performance regressions) is correct because Query Store persists query plans, runtime statistics, and execution history, letting you proactively detect plan regressions and parameter-sniffing issues before users report them. Option B (Log backup frequency) is not a performance metric for Azure SQL Database, since log backups are managed automatically by the platform and their frequency is not exposed as a tunable performance indicator. Option C (Database size and growth rate) is a capacity-planning metric rather than a real-time performance signal, so it does not proactively reveal latency, blocking, or resource-throttling issues.

Answer

Log backup frequency

Answer

Database size and growth rate

Question 11

Which TWO options are valid methods to optimize query performance in Azure SQL Managed Instance?

Accepted Answer

Use columnstore indexes on large tables. Options A and D are correct. Columnstore indexes (A) improve analytical query performance by reducing I/O and using batch processing. Query Store (D) helps identify regressions by tracking execution plans and performance metrics. Option B is incorrect; setting database compatibility level to 150 may enable new features but is not a direct query optimization method. Option C is incorrect; increasing storage size does not improve query performance. Option E is incorrect; Transparent Data Encryption (TDE) secures data but does not enhance performance.

Answer

Set database compatibility level to 150

Answer

Increase the maximum storage size

Answer

Enable Transparent Data Encryption (TDE)

Question 12

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.. Azure SQL Database service tier S3 (Standard tier, 100 DTUs) is a relatively low-performance tier. If the database experiences performance issues during peak hours, the most likely cause is that the service tier cannot handle the workload's concurrency and throughput demands. Upgrading to a higher tier (e.g., Premium or vCore-based) would provide more DTUs/vCores and IOPS.

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 13

You observe that the average of Maximum DTU consumption over the last hour is consistently above 90%. What should you do next?

Accepted Answer

Scale up the database to a higher service tier or increase DTU.. A sustained average of Maximum DTU consumption above 90% over an hour indicates the database is consistently resource-bound and approaching its tier ceiling. The correct next step is to scale up to a higher service tier or add DTUs so the workload has headroom and latency does not degrade. Query Store and index maintenance are optimization steps, but the immediate signal is capacity saturation.

Answer

Enable Query Store to analyze top queries.

Answer

Rebuild all indexes in the database.

Answer

Do nothing; it's normal for DTU to be high.

Question 14

You manage an Azure SQL Database that experiences periodic performance degradation. You need to identify the top queries by CPU consumption over the last hour. Which dynamic management view should you query?

Accepted Answer

sys.dm_exec_query_stats. sys.dm_exec_query_stats returns one row per cached query plan and includes cumulative execution statistics such as total_worker_time (CPU), total_elapsed_time, and execution_count. Aggregating total_worker_time over the last hour identifies the top CPU-consuming queries. This is the standard DMV for query-level performance analysis in Azure SQL Database.

Answer

sys.dm_exec_sessions

Answer

sys.dm_exec_query_plan

Answer

sys.dm_exec_requests

Question 15

Your Azure SQL Managed Instance is experiencing high PAGELATCH_SH waits. You need to reduce this contention. What should you implement?

Accepted Answer

Add more data files to the filegroup. Adding more data files to the filegroup spreads out page allocations, reducing contention for allocation structures and thus decreasing PAGELATCH_SH waits. Option A is incorrect because scaling up may provide more resources but does not directly address the page latch contention caused by allocation bottlenecks. Option B is incorrect because delayed durability only affects transaction log write behavior, not page latches. Option C is incorrect because a readable secondary replica does not alleviate latch contention on the primary; it is designed for read workload offloading, not contention reduction.

Answer

Scale up the managed instance to a higher service tier

Answer

Enable delayed durability

Answer

Configure a readable secondary replica