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

Question 1

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. Option B is correct because using Azure Key Vault as the TDE protector is recommended for centralized management and high availability. Option A is wrong because SQL Managed Instance does not support TDE with a certificate stored in the instance; it must use Azure Key Vault or service-managed. Option C is wrong because service-managed keys have less control and are not recommended for production. Option D is wrong because TDE cannot be disabled after migration; it must be managed.

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 2

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. Option C is correct because 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 CPU pressure does not directly cause log write waits. Option B is wrong because insufficient storage space causes different symptoms. Option D is wrong because blocking causes other wait types like LCK_M_*.

Answer

High CPU utilization on the database server

Answer

Long-running blocking transactions

Answer

Insufficient storage space for data files

Question 3

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. Option B is correct because Query Store captures historical query performance and allows plan comparison. Option A is wrong because Azure SQL Analytics provides aggregated metrics but not per-query historical comparison. Option C is wrong because Intelligent Insights provides diagnostic analysis but not detailed historical data. Option D is wrong because Dynamic Management Views (DMVs) provide current state, not historical trends.

Answer

SQL Server DMVs

Answer

Azure SQL Analytics solution in Log Analytics

Answer

Azure SQL Database Intelligent Insights

Question 4

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 D are correct. B is correct because using RECOMPILE hint forces recompilation only for that execution, but the question asks to reduce recompilations, so that's wrong. Wait, the correct answers are A and C. Option A is correct because using KEEPFIXED PLAN prevents recompilations due to statistics changes. Option C is correct because keeping parameter data types consistent avoids recompilations due to type mismatch. Option B is wrong because RECOMPILE hint increases recompilations. Option D is wrong because OPTIMIZE FOR UNKNOWN can cause plan instability but not reduce recompilations. Option E is wrong because sp_refreshsqlmodule does not reduce recompilations.

Answer

Execute sp_refreshsqlmodule on the stored procedure

Answer

Use the OPTIMIZE FOR UNKNOWN query hint

Answer

Use the RECOMPILE query hint

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. Options A, B, and D are correct. Automatic tuning includes FORCE LAST GOOD PLAN, CREATE INDEX, and DROP INDEX. Option C is wrong because modify statistics is not an automatic tuning option. Option E is wrong because enabling database compression is not part of automatic tuning.

Answer

Modify statistics

Answer

Enable database compression

Question 6

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. Options A, C, and D are correct. sys.dm_exec_query_stats returns aggregated performance statistics for cached query plans. sys.dm_exec_requests shows currently executing requests. sys.dm_exec_query_plan shows the execution plan for cached queries. Option B is wrong because sys.dm_exec_sessions provides session-level information, not query-specific resource usage. Option E is wrong because sys.dm_os_wait_stats shows wait statistics, not query-specific resource use.

Answer

sys.dm_os_wait_stats

Answer

sys.dm_exec_sessions

Question 7

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.. Option A is correct because 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 8

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 log file is too large causing fragmentation; shrink it to reduce latency.. Option A is correct because a large log file can cause log write latency due to file fragmentation and long I/O paths. Option B is wrong because transaction log is sequential write, not random I/O. Option C is wrong because log write latency is primarily about storage, not CPU. Option D is wrong because increasing log file size further would exacerbate the issue.

Answer

Increase the log file size to allow better write performance.

Answer

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

Answer

The disk is not fast enough for random writes; switch to Ultra Disk.

Question 9

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. Option B is correct because sys.dm_exec_query_stats provides aggregated performance statistics including CPU time. Option A is wrong because it shows currently executing queries, not historical. Option C is wrong because it shows resource governance stats. Option D is wrong because it shows index usage.

Answer

sys.dm_exec_requests

Answer

sys.dm_db_index_usage_stats

Answer

sys.dm_db_resource_stats

Question 10

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).. Option D is correct because SSMS can graphically open deadlock graphs from extended events. Option A is wrong because Query Store does not capture deadlocks. Option B is wrong because Azure Monitor collects metrics but not deadlock details. Option C is wrong because sys.dm_exec_requests does not show deadlock history.

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 11

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. Option A is correct because FORCE_LAST_GOOD_PLAN automatically forces the last known good plan when a regression is detected. Option B is wrong because it creates indexes automatically, which is not about plan regression. Option C is wrong because it drops unused indexes. Option D is wrong because it corrects nonclustered indexes, not query plans.

Answer

DROP_INDEX

Answer

CREATE_INDEX

Answer

CORRECT_INDEX

Question 12

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. Option A is correct because sys.dm_exec_requests shows currently executing requests with elapsed time. Option B is wrong because it shows query statistics, not currently running queries. Option C is wrong because it shows resource usage. Option D is wrong because it shows session-level waits.

Answer

sys.dm_db_resource_stats

Answer

sys.dm_exec_sessions

Answer

sys.dm_exec_query_stats

Question 13

Your Azure SQL Database is hitting the DTU limit frequently during business hours. You need to identify which queries are consuming the most DTU. What should you do?

Accepted Answer

Query sys.dm_exec_query_stats and correlate with sys.dm_db_resource_stats.. Option C is correct because sys.dm_db_resource_stats provides DTU consumption per minute, and sys.dm_exec_query_stats can be joined to find top queries. Option A is wrong because it shows resource consumption per database, not per query. Option B is wrong because it shows per minute, but per query requires sys.dm_exec_query_stats. Option D is wrong because it shows waits, not DTU.

Answer

Query sys.dm_db_resource_stats to see DTU usage per minute.

Answer

Use Azure Metrics for SQL Database to view DTU consumption per database.

Answer

Enable Query Store and review the Wait Statistics report.

Question 14

You are migrating an on-premises SQL Server database to Azure SQL Database. The database uses Service Broker for asynchronous messaging. After migration, you notice that performance is degraded. What should you do to optimize?

Accepted Answer

Replace Service Broker with Azure Queue Storage or Event Grid.. Option B is correct because Service Broker is not fully supported in Azure SQL Database; you should use Azure Queue Storage or Event Grid. Option A is wrong because Service Broker cannot be enabled in Azure SQL Database. Option C is wrong because Service Broker uses internal activation, not external. Option D is wrong because scaling up does not solve the feature incompatibility.

Answer

Enable Service Broker in Azure SQL Database by setting ENABLE_BROKER.

Answer

Scale up the database to a higher service tier to improve Service Broker performance.

Answer

Configure Service Broker to use external activation via Azure Functions.

Question 15

You are troubleshooting a performance issue in Azure SQL Database. You suspect that parameter sniffing is causing suboptimal query plans. What feature can you use to mitigate this without code changes?

Accepted Answer

Set the database to FORCE_PARAMETERIZATION = ON.. Option A is correct because the FORCE_PARAMETERIZATION database option forces parameterization and can help with sniffing issues. Option B is wrong because it's for plan forcing after regression, not prevention. Option C is wrong because it's a query hint, not a database-wide setting. Option D is wrong because it's for index maintenance.

Answer

Reorganize indexes regularly.

Answer

Use Query Store to force a good plan.

Answer

Add OPTIMIZE FOR UNKNOWN query hint to all queries.