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

Question 1

Your company uses Azure SQL Database with the Hyperscale service tier. You notice that index maintenance operations are taking longer than expected. What is the most likely reason for this performance issue?

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

Accepted Answer

The log write throughput is insufficient. In Azure SQL Database Hyperscale, index rebuild operations generate a large volume of log records. Although Hyperscale uses a distributed architecture with fast log commit, the log write throughput per service objective is finite and can become a bottleneck during intensive write operations like index rebuilds. This is the most common cause of slow index maintenance. Option A (General Purpose tier) is irrelevant as the question specifies Hyperscale. Option B (page compression) does not significantly impact index rebuild duration. Option D (simple recovery) is not the cause; even with simple recovery, the log write throughput during rebuild can be insufficient.

Answer

The database is using the General Purpose tier

Answer

The index is using page compression

Answer

The transaction log is set to simple recovery

Question 2

You are configuring performance monitoring for Azure SQL Managed Instance. You need to collect and analyze query performance data with minimal overhead. Which solution should you use?

Accepted Answer

Query Store. Query Store is built-in and designed for low overhead query performance monitoring. Option B is wrong because Azure Monitor metrics provide resource-level metrics, not query-level details. Option C is wrong because Extended Events can have higher overhead and is more for custom event collection. Option D is wrong because SQL Server Profiler is deprecated and has high overhead.

Answer

Azure Monitor metrics

Answer

Extended Events

Answer

SQL Server Profiler

Question 3

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 4

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) policy allows you to retain backups beyond the default retention period, with weekly, monthly, and yearly options. Option A is wrong because geo-restore is for disaster recovery, not long-term retention. Option C is wrong because automated backups have a maximum retention of 35 days. Option D is wrong because Point-In-Time Restore (PITR) retention only goes up to 35 days and does not support long-term retention.

Answer

Geo-restore feature

Answer

Automated backups retention period

Answer

Point-In-Time Restore (PITR) retention

Question 5

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. Using Azure Key Vault to store the TDE protector provides centralized management, automatic key rotation, and high availability without manual intervention. Option B is incorrect because service-managed keys offer less control and are not recommended for scenarios requiring bring your own key (BYOK) compliance. Option C is incorrect because SQL Managed Instance does not support storing TDE certificates directly in the instance; instead, you can use Azure Key Vault to bring your own key. Option D is incorrect because TDE cannot be disabled after migration without decrypting the database, and Always Encrypted is a different feature for column-level encryption, not a replacement for TDE.

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

Question 6

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 7

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 captures historical query performance data and allows comparing performance before and after index changes, making it the correct choice for this requirement. SQL Server DMVs (Option B) only provide current state, not historical trends. Azure SQL Analytics solution (Option C) provides aggregated metrics but lacks per-query historical comparison. Intelligent Insights (Option D) offers diagnostic analysis but not detailed historical query data.

Answer

SQL Server DMVs

Answer

Azure SQL Analytics solution in Log Analytics

Answer

Azure SQL Database Intelligent Insights

Question 8

Your Azure SQL Database is experiencing high CPU utilization due to frequent recompilations of a stored procedure. Which TWO actions should you take to reduce recompilations?

Accepted Answer

Use the KEEPFIXED PLAN query hint. Options B and E are correct. Option B: Using the KEEPFIXED PLAN query hint prevents recompilations caused by statistics changes, ensuring the query plan remains stable. Option E: Ensuring parameter data types match the column data types avoids type mismatches that trigger recompilations. Option A (sp_refreshsqlmodule) updates metadata but doesn't reduce recompilations. Option C (OPTIMIZE FOR UNKNOWN) can lead to plan instability without reducing recompilations. Option D (RECOMPILE) forces recompilation on each execution, increasing recompilations.

Answer

Execute sp_refreshsqlmodule on the stored procedure

Answer

Use the OPTIMIZE FOR UNKNOWN query hint

Answer

Use the RECOMPILE query hint

Question 9

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. Options A, C, and D are correct. Automatic tuning for Azure SQL Database can automatically apply FORCE LAST GOOD PLAN, CREATE INDEX, and DROP INDEX recommendations. Option B is incorrect because modifying statistics is not an automatic tuning option; it requires manual execution. Option E is incorrect because enabling database compression is not part of automatic tuning.

Answer

Modify statistics

Answer

Enable database compression

Question 10

You are troubleshooting a performance issue on an Azure SQL Database. Which THREE Dynamic Management Views (DMVs) should you query to identify the most resource-intensive queries?

Accepted Answer

sys.dm_exec_requests. The correct answers are A, C, and E. Option A (sys.dm_exec_requests) shows currently executing requests, which helps identify active resource-intensive queries. Option C (sys.dm_exec_query_plan) provides the execution plan for cached queries, enabling analysis of query performance. Option E (sys.dm_exec_query_stats) returns aggregated performance statistics (e.g., CPU time, logical reads) for cached query plans, making it ideal for identifying resource-intensive queries across all executions. Option B (sys.dm_os_wait_stats) is incorrect because it shows instance-level wait statistics, not query-specific resource usage. Option D (sys.dm_exec_sessions) is incorrect as it provides session-level information, not query-level resource metrics.

Answer

sys.dm_os_wait_stats

Answer

sys.dm_exec_sessions

Question 11

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.. Query Store can identify query performance regressions and provide plan forcing. Option B is wrong because automatic tuning might not address the specific query without Query Store data. Option C is wrong because index tuning may not help if the query plan is suboptimal. Option D is wrong because read replicas offload read traffic but do not optimize CPU usage of a single query on the primary.

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 12

Your Azure SQL Managed Instance is experiencing high latency for write transactions. You have identified that log write latency is the bottleneck. The instance uses Premium SSD with 5000 IOPS and 200 MB/s throughput. You observe that the log file is 500 GB and has grown significantly. What is the most likely cause and solution?

Accepted Answer

The disk is not fast enough for random writes; switch to Ultra Disk.. For Azure SQL Managed Instance, log write latency is critical for transaction write performance. Premium SSD P30 with 5000 IOPS and 200 MB/s throughput may become a bottleneck under heavy write workloads. Switching to Azure Ultra Disk provides significantly lower latency and higher IOPS/throughput, which can reduce log write latency. Option D is incorrect because shrinking the transaction log does not improve performance and can cause severe fragmentation and performance degradation. Proper log backup scheduling is the correct method to manage log size.

Answer

Increase the log file size to allow better write performance.

Answer

The instance has insufficient CPU; scale up the managed instance.

Answer

The log file is too large causing fragmentation; shrink it to reduce latency.

Question 13

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. This DMV returns aggregated performance statistics for cached query plans, including total_worker_time (which represents total CPU time). By filtering on last_execution_time, you can identify queries executed within the last hour. sys.dm_exec_requests (B) shows only currently executing requests, not historical data. sys.dm_db_index_usage_stats (C) tracks index seek/scan operations, not CPU usage. sys.dm_db_resource_stats (D) provides resource consumption at the database level (e.g., DTU, CPU percentage), not per query.

Answer

sys.dm_exec_requests

Answer

sys.dm_db_index_usage_stats

Answer

sys.dm_db_resource_stats

Question 14

Your Azure SQL Database is experiencing deadlocks. You have enabled deadlock graphs in the extended events session. After capturing a deadlock, you need to analyze it to determine which queries are involved. What should you use?

Accepted Answer

Open the deadlock graph file in SQL Server Management Studio (SSMS).. To analyze a deadlock graph captured via extended events, you should open the .xdl file in SQL Server Management Studio (SSMS), which provides a graphical representation of the deadlock, showing the processes involved, the resources, and the queries. Option B is correct. Option A is incorrect because sys.dm_exec_requests with a blocking filter shows current blocking, not historical deadlock details. Option C is incorrect because Azure Monitor for SQL provides metrics but not the detailed deadlock graph. Option D is incorrect because Query Store tracks query performance over time but does not capture deadlock events or graphs.

Answer

Query sys.dm_exec_requests with a filter on blocking.

Answer

Azure Monitor for SQL and view deadlock metrics.

Answer

Query Store and review the regressed queries.

Question 15

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