DP-300 Practice Test 26 — 15 Questions

Question 1

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 2

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.. Option B is correct because high DTU indicates resource pressure; scaling up provides immediate relief. Option A is wrong because indexes may not be the cause. Option C is wrong because Query Store helps identify queries, but scaling is a direct solution. Option D is wrong because waiting may cause further issues.

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 3

You have an Azure SQL Database with Intelligent Insights enabled. You receive an alert that 'SQLInsights: Resource utilization is consistently high'. You need to determine whether the issue is caused by an increase in user workload or a degradation in query performance. Which Intelligent Insights dimension should you review?

Accepted Answer

Impact. Option A is correct because the 'Impact' dimension shows whether performance degradation is due to increased workload or query regression. Option B is wrong because 'Metric' shows the metric name, not root cause. Option C is wrong because 'Resource type' is the Azure resource type. Option D is wrong because 'Severity' indicates alert level, not cause.

Answer

Metric

Answer

Severity

Answer

Resource type

Question 4

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.. Option A is correct because the metric shows log write IO usage, not CPU. Option B is wrong because it misinterprets the metric. Option C is wrong because it's not about storage size. Option D is wrong because log write percent is about I/O, not transaction log space.

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 5

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. Option D is correct because log write latency on the secondary is typically due to network latency between regions. Option A is wrong because the primary performance is normal. Option B is wrong because the secondary is readable but not for writes. Option C is wrong because the secondary does not generate its own log; it applies log from primary.

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 6

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. Options B and D are correct. avg_data_io_percent measures data IO usage relative to limit; avg_log_write_percent measures log IO usage. Option A is CPU. Option C is memory. Option E is storage space.

Answer

avg_cpu_percent

Answer

max_size_percent

Answer

avg_memory_usage_percent

Question 7

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. Option B is correct because Query Store provides detailed plan history to identify plan regression. Option A is wrong because index tuning may not address plan regression directly. Option C is wrong because restarting the database is disruptive and not a troubleshooting step. Option D is wrong because increasing DTU does not fix plan regression.

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 8

You have an Azure SQL Database with automatic tuning enabled. You notice that a query that previously ran quickly is now running slower. Automatic tuning has implemented a plan correction. However, the new plan is performing worse. What should you do to revert to the previous behavior?

Accepted Answer

Revert the automatic tuning plan correction using the Azure portal or T-SQL. Option B is correct because you can manually revert the plan correction via the Azure portal or T-SQL. Option A is wrong because disabling automatic tuning would prevent future corrections but not revert the current one. Option C is wrong because Query Store retains plans but reverting requires explicit action. Option D is wrong because index recreation is not related to plan correction.

Answer

Drop and recreate the index used by the query

Answer

Wait for automatic tuning to revert the plan automatically

Answer

Disable automatic tuning for the database

Question 9

You manage an Azure SQL Database that is experiencing higher than expected DTU consumption. You need to identify which queries are consuming the most resources. Which dynamic management view should you query?

Accepted Answer

Query sys.dm_db_resource_stats. sys.dm_db_resource_stats provides per-minute resource consumption data for the database. Option A is wrong because sys.dm_exec_query_stats is not available in Azure SQL Database (it is for SQL Server). Option C is wrong because sys.dm_os_wait_stats is for server-level waits. Option D is wrong because sys.dm_exec_requests shows only currently executing requests.

Answer

Query sys.dm_exec_requests

Answer

Query sys.dm_os_wait_stats

Answer

Query sys.dm_exec_query_stats

Question 10

You are troubleshooting a performance issue on an Azure SQL Database. The database is experiencing high PAGELATCH_EX waits. Which THREE measures can help reduce these waits?

Accepted Answer

Use a hash distribution or round-robin distribution in a table design. PAGELATCH_EX waits are due to contention on the last page of indexes (often due to sequential inserts). Using OPTIMIZE_FOR_SEQUENTIAL_KEY, partitioning, and using hash or round-robin distributions can reduce contention. Option A, B, and C are correct. Option D is wrong because increasing MAXDOP may increase contention. Option E is wrong because snapshot isolation does not directly reduce PAGELATCH_EX.

Answer

Increase MAXDOP for the queries

Answer

Enable snapshot isolation level

Question 11

You are monitoring an Azure SQL Database using dynamic management views (DMVs). You run a query against `sys.dm_exec_query_stats` to find the top 10 queries by total worker time. Several queries show high worker time but low logical reads. The database is not experiencing any blocking or deadlocks. What is the most likely cause of the high worker time?

Accepted Answer

The queries are CPU-bound due to inefficient query plans.. Option A is correct because high CPU consumption (worker time) with low I/O indicates CPU-bound queries, often due to inefficient joins, aggregations, or non-sargable predicates. Option B is wrong because low logical reads suggest not memory pressure. Option C is wrong because parameter sniffing would show varied plans. Option D is wrong because high worker time with low I/O is not typical for transaction log writes.

Answer

The queries suffer from parameter sniffing leading to suboptimal plans.

Answer

The queries are experiencing memory pressure causing excessive lazy writes.

Answer

The queries are waiting on transaction log writes.

Question 12

You manage an Azure SQL Database that runs a customer-facing application. You notice that during peak hours, the DTU consumption reaches 100% and queries experience timeouts. You need to ensure consistent performance without manual intervention. What should you implement?

Accepted Answer

Scale up the database to a higher service tier (e.g., from S2 to S3).. Option B is correct because scaling up the service tier to a higher DTU level provides more resources, reducing contention and timeouts. Option A is wrong because read scale-out only distributes read-only queries. Option C is wrong because Query Store is a monitoring tool, not a scaling solution. Option D is wrong because automatic tuning can help with plan regression but not resource shortage.

Answer

Enable Query Store to identify costly queries.

Answer

Implement read scale-out to offload read queries.

Answer

Configure automatic tuning to force plan regression fixes.

Question 13

You are optimizing an Azure SQL Database that has a heavy workload of both reads and writes. The database has a clustered columnstore index on a large fact table. You notice that the index has high fragmentation and the performance of queries against this table is degrading. What should you do to improve performance?

Accepted Answer

Rebuild the clustered columnstore index. Option C is correct because rebuilding the columnstore index reduces fragmentation and improves performance. Option A is wrong because reorganizing a columnstore index is not supported; only rebuild is. Option B is wrong because dropping and recreating is more disruptive. Option D is wrong because increasing service tier addresses symptoms not fragmentation.

Answer

Increase the service tier to get more IOPS

Answer

Drop and recreate the clustered columnstore index

Answer

Reorganize the clustered columnstore index

Question 14

You have an Azure SQL Managed Instance with a large number of databases. You need to monitor the storage space used by each database to proactively manage capacity. Which tool should you use?

Accepted Answer

Azure SQL Analytics (preview). Azure SQL Analytics (preview) in Azure Monitor provides a solution specifically for monitoring Azure SQL databases and managed instances, including storage usage per database. Option A is wrong because Query Performance Insight focuses on query performance. Option C is wrong because Automatic tuning is for performance optimization. Option D is wrong because Intelligent Insights provides proactive diagnostics but not per-database storage monitoring.

Answer

Query Performance Insight

Answer

Automatic tuning

Answer

Intelligent Insights

Question 15

Which TWO metrics in Azure SQL Database indicate that the database might need to be scaled up?

Accepted Answer

Log write percent consistently above 90%. High DTU/CPU consumption and high log write percent indicate the database is hitting resource limits. Option A and D are correct. Option B is wrong because low data IO is not an indicator. Option C is wrong because low memory indicates underutilization. Option E is wrong because low session percent is not a scaling trigger.

Answer

Data IO percentage consistently below 20%

Answer

Session percent consistently below 10%

Answer

Memory consumption consistently below 30%