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

Question 1

Your Azure SQL Database is experiencing high CPU usage. You suspect a specific query is causing the issue. You have enabled Query Store. How can you identify the query that has consumed the most cumulative CPU time over the last hour?

Accepted Answer

Use the Query Store Top Resource Consuming Queries report and filter by the last hour.. Option D is correct because the Top Resource Consuming Queries report in Query Store shows cumulative CPU time, duration, and other metrics, allowing easy identification. Option A is wrong because sys.dm_exec_query_stats shows current cached plans but not cumulative over a specific time window. Option B is wrong because the Regressed Queries report shows plan changes, not CPU consumption. Option C is wrong because sys.dm_exec_requests shows currently running queries, not historical cumulative use.

Answer

Use sys.dm_exec_query_stats to sort by total_worker_time.

Answer

Use sys.dm_exec_requests to find queries with high CPU.

Answer

Use the Query Store Regressed Queries report.

Question 2

You are optimizing a data warehouse workload on Azure SQL Database. The workload involves large batch inserts and nightly aggregations. You notice that the transaction log is growing excessively during the batch inserts, causing performance degradation. You need to reduce log growth without affecting data consistency. What should you do?

Accepted Answer

Use bulk insert operations with TABLOCK hint to enable minimal logging.. Option B is correct because using minimally logged operations (e.g., bulk insert with TABLOCK) reduces log space for large imports under the full recovery model, but requires specific conditions. Option A is wrong because simple recovery model is not supported in Azure SQL Database (only FULL, BULK_LOGGED is not available). Option C is wrong because partitioning does not reduce log growth. Option D is wrong because increasing log size only accommodates growth, does not prevent it.

Answer

Change the database recovery model to Simple.

Answer

Create a partition function and scheme to spread the inserts.

Answer

Increase the maximum log size of the database.

Question 3

Your Azure SQL Database has a recurring job that rebuilds indexes weekly. After a recent change, the job is taking much longer to complete. You suspect that the index fragmentation is higher than usual. What is the most efficient way to check index fragmentation across the database?

Accepted Answer

Use the sys.dm_db_index_physical_stats dynamic management function.. Option C is correct because sys.dm_db_index_physical_stats returns fragmentation details for all indexes in a database with minimal overhead when used with limited scanning. Option A is wrong because SHOWPLAN_XML shows query plans, not fragmentation. Option B is wrong because sys.indexes does not include fragmentation info. Option D is wrong because missing index DMVs suggest new indexes, not fragmentation.

Answer

Query sys.indexes to check the fragmentation percentage.

Answer

Use SET SHOWPLAN_XML ON and run sample queries.

Answer

Use the sys.dm_db_missing_index_details DMV.

Question 4

You are troubleshooting a performance issue on an Azure SQL Database. Which TWO actions should you prioritize to identify the root cause of high resource consumption?

Accepted Answer

Review the Query Store Top Resource Consuming Queries report.. Options A and C are correct. Query Store identifies top resource-consuming queries, and sys.dm_exec_query_stats shows current CPU/memory usage. Option B (rebuilding indexes) is a fix, not diagnostic. Option D (scaling up) is reactive. Option E (changing recovery model) is not relevant to resource consumption.

Answer

Rebuild all indexes to improve query performance.

Answer

Change the database recovery model to Simple.

Answer

Scale the database to a higher service tier to mitigate the issue.

Question 5

You are optimizing an Azure SQL Database that runs a heavy reporting workload. The database uses the Business Critical service tier. Which THREE configuration changes can improve query performance for reporting queries without significantly impacting OLTP operations?

Accepted Answer

Create nonclustered columnstore indexes on large reporting tables.. Options A, B, and D are correct. Read-scale replicas offload reporting, columnstore indexes improve aggregation performance, and result set caching reduces repeated reads. Option C (increasing MAXDOP) could cause parallelism issues in OLTP. Option E (disabling automatic tuning) is detrimental.

Answer

Increase MAXDOP for the database to 8.

Answer

Disable automatic tuning to prevent plan changes.

Question 6

You are monitoring an Azure SQL Database that is experiencing high DTU usage. Which TWO metrics should you examine to determine whether the bottleneck is CPU or I/O?

Accepted Answer

Log write bytes per second.. Options B and D are correct. CPU percentage directly indicates CPU usage, and log write bytes/sec indicates I/O activity for writes. Option A (deadlocks) is not a bottleneck. Option C (session count) shows concurrency, not resource usage. Option E (storage space) is capacity, not performance.

Answer

Number of deadlocks per second.

Answer

Used storage space in GB.

Answer

Number of active sessions.

Question 7

Refer to the exhibit. You have configured the automatic tuning policy as shown. After a week, you notice that an index has been dropped automatically, causing a critical query to run slowly. What should you do to prevent this in the future while still benefiting from automatic tuning?

Accepted Answer

Set the dropIndex option state to Disabled in the tuning policy.. Option C is correct because disabling the dropIndex option prevents automatic index drops while keeping forcePlan and createIndex enabled. Option A is wrong because disabling all automatic tuning removes all benefits. Option B is wrong because reverting by creating a manual index does not prevent future drops. Option D is wrong because Query Store does not prevent index drops.

Answer

Manually create the dropped index and mark it as a required index.

Answer

Enable Query Store to track index usage.

Answer

Disable automatic tuning entirely.

Question 8

Refer to the exhibit. You are analyzing query performance using sys.dm_exec_query_stats. Based on the output, which query is the best candidate for optimization to reduce overall CPU usage?

Accepted Answer

Query 2 because it has the highest average CPU per execution.. Option B is correct because Query 2 has the highest average worker time per execution (4500 ms), indicating it is the most expensive per run, and optimizing it could yield significant CPU savings if it runs frequently. Option A is wrong because Query 1 has high total but lower per execution (500 ms). Option C is wrong because Query 3 has low per execution (150 ms). Option D is wrong because Query 2 is the best candidate.

Answer

Query 1 because it has the highest total_cpu_time.

Answer

All queries should be optimized equally.

Answer

Query 3 because it runs most frequently.

Question 9

Refer to the exhibit. You executed the Azure CLI command to list databases. You need to resume db3 to make it available for connections. Which command should you use?

Accepted Answer

az sql db resume --resource-group rg1 --server server1 --name db3. Option B is correct because az sql db resume is the correct command to resume a paused database. Option A is wrong because az sql db start does not exist. Option C is wrong because az sql db update can change properties but not resume a paused database. Option D is wrong because az sql db restart does not exist.

Answer

az sql db restart --resource-group rg1 --server server1 --name db3

Answer

az sql db start --resource-group rg1 --server server1 --name db3

Answer

az sql db update --resource-group rg1 --server server1 --name db3 --set status=Online

Question 10

Your organization runs a critical e-commerce application on Azure SQL Database. You notice that during peak hours, query performance degrades significantly. You have enabled Intelligent Insights and Query Store. Which diagnostic metric would you analyze first to identify the root cause of the performance degradation?

Accepted Answer

DTU/CPU consumption. Option B is correct because DTU/CPU consumption directly correlates with query performance during peak load. Option A is wrong because deadlocks are rare and not the typical cause of sustained degradation. Option C is wrong because although important, the primary indicator is resource consumption. Option D is wrong because failed connections indicate availability issues, not performance.

Answer

Query store wait statistics

Answer

Deadlock count

Answer

Failed connections

Question 11

You are a database administrator for a large financial services company. You need to ensure that all queries that read sensitive customer data use an optimized execution plan. What feature should you enable to automatically identify and fix regressed query plans?

Accepted Answer

Automatic Tuning. Option C is correct because Automatic Tuning can automatically identify and fix regressed query plans. Option A is wrong because Query Store captures performance data but does not automatically fix plans. Option B is wrong because Intelligent Insights provides monitoring, not automatic fixing. Option D is wrong because Database Advisor gives recommendations but requires manual action.

Answer

Query Store

Answer

Intelligent Insights

Answer

Database Advisor for SQL Database

Question 12

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?

Accepted Answer

The log write throughput is insufficient. Option B is correct because in Hyperscale, index rebuild operations are logged and can cause significant log throughput, which is a common bottleneck. Option A is wrong because page compression is not directly related to index rebuild time. Option C is wrong because Hyperscale uses a distributed architecture with fast log commit. Option D is wrong because Hyperscale does not have a fixed DTU limit; it scales compute independently.

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 13

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. Option A is correct because Query Store is built-in and designed for low overhead query performance monitoring. Option B is wrong because Extended Events can have higher overhead and is more for custom event collection. Option C is wrong because Azure Monitor metrics provide resource-level metrics, not query-level details. 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 14

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. Option D is correct because PAGEIOLATCH_SH waits indicate I/O bottlenecks; adding indexes can improve data access patterns and reduce I/O. Option A is wrong because increasing memory will not directly reduce I/O waits if the issue is inefficient queries. Option B is wrong because enabling compression may increase CPU but not necessarily reduce I/O waits. Option C is wrong because forcing parameterization may help plan stability but not I/O.

Answer

Increase the database max memory

Answer

Enable page compression on large tables

Answer

Force parameterization of queries

Question 15

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. Option A is correct because Long-Term Retention (LTR) is designed for retaining backups beyond the default retention period, with weekly, monthly, yearly options. Option B is wrong because Point-in-Time Restore (PITR) only allows restore to a point within the retention period, not long-term. Option C is wrong because automated backups have a maximum retention of 35 days. Option D is wrong because geo-restore is for disaster recovery, not long-term retention.

Answer

Geo-restore feature

Answer

Automated backups retention period

Answer

Point-In-Time Restore (PITR) retention