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

Question 1

You are managing an Azure SQL Database that uses Intelligent Insights. You receive an alert that there is a performance issue with a specific query. You need to analyze the root cause. What should you use?

Accepted Answer

Query Store to review query execution plans and wait statistics. Option A is correct because Query Store captures query performance metrics and allows detailed analysis. Option B is incorrect because Intelligent Insights provides summaries, not detailed query analysis. Option C is incorrect because database-level metrics don't pinpoint queries. Option D is incorrect because Automatic Tuning applies fixes but doesn't analyze root cause.

Answer

Intelligent Insights report

Answer

Automatic Tuning recommendations

Answer

Azure Monitor metrics for the database

Question 2

You have an Azure SQL Database that uses the Hyperscale service tier. You notice that the log rate is consistently high. You need to reduce the log write latency. What should you do?

Accepted Answer

Increase the log rate limit in the Hyperscale service tier. Option B is correct because increasing the log rate limit allows more log throughput, reducing latency. Option A is incorrect because changing tier to General Purpose would reduce performance. Option C is incorrect because data compression does not affect log writes. Option D is incorrect because enabling Accelerated Database Recovery does not reduce log write latency.

Answer

Enable data compression on the tables

Answer

Enable Accelerated Database Recovery

Answer

Change the service tier to General Purpose

Question 3

You are monitoring an Azure SQL Database and notice that the average CPU usage is 80% and the average data IO percentage is 70%. You need to identify the most likely cause of the high resource usage. What should you check first?

Accepted Answer

Use Query Store to identify top resource-consuming queries. Option C is correct because Query Store provides detailed query performance data to identify high resource queries. Option A is incorrect because blocking and deadlocks cause waits, not necessarily high CPU/IO. Option B is incorrect because maintenance tasks like index rebuild are scheduled and not typically the first check. Option D is incorrect because connection pooling issues cause connection errors, not high CPU/IO.

Answer

Check for long-running maintenance tasks

Answer

Check for connection pooling issues

Answer

Check for blocking and deadlocks

Question 4

You manage an Azure SQL Database that uses a Serverless compute tier. You notice that during idle periods, the database auto-pauses and then auto-resumes when a connection is made. However, users report that the first query after a pause is slow. You need to improve the performance of the first query. What should you do?

Accepted Answer

Disable auto-pause for the serverless database. Option D is correct because the serverless database auto-pauses after inactivity; disabling auto-pause keeps the database warm. Option A is incorrect because scaling vCores does not affect cold start delay. Option B is incorrect because Query Store does not speed up cold start. Option C is incorrect because creating a job to ping the database is a workaround but not the recommended solution; disabling auto-pause is simpler.

Answer

Increase the maximum vCores

Answer

Create a SQL Agent job to ping the database every hour

Answer

Enable Query Store

Question 5

You are optimizing an Azure SQL Database that has a large table with a clustered columnstore index. You notice that the index has been fragmented over time. You need to improve query performance. What should you do?

Accepted Answer

Reorganize the columnstore index. Option C is correct because REORGANIZE is the recommended maintenance for columnstore indexes to remove rows from deleted bitmaps. Option A is incorrect because REBUILD is more resource-intensive and not always necessary. Option B is incorrect because dropping and recreating is disruptive. Option D is incorrect because updating statistics alone does not address fragmentation.

Answer

Drop and recreate the columnstore index

Answer

Update statistics on the table

Answer

Rebuild the columnstore index

Question 6

You have an Azure SQL Database that is experiencing high wait times on RESOURCE_SEMAPHORE waits. You need to identify the root cause. What should you check?

Accepted Answer

Queries with large memory grants. Option A is correct because RESOURCE_SEMAPHORE waits indicate memory grant pressure, often due to large queries requiring memory. Option B is incorrect because disk I/O causes PAGEIOLATCH waits. Option C is incorrect because blocking causes LCK_M_* waits. Option D is incorrect because CPU pressure causes SOS_SCHEDULER_YIELD waits.

Answer

Blocking and deadlocks

Answer

High CPU usage

Answer

Disk I/O bottlenecks

Question 7

You are configuring performance monitoring for an Azure SQL Database. You need to identify which two tools can be used to analyze query performance over time. Which TWO should you select?

Accepted Answer

Azure SQL Analytics (Azure Monitor). Option A is correct: Query Store tracks query history. Option D is correct: Azure SQL Analytics (now part of Azure Monitor) provides historical performance data. Option B is incorrect: Dynamic Management Views show current state, not historical. Option C is incorrect: Intelligent Insights provides summaries, not detailed query history. Option E is incorrect: Extended Events are for real-time tracing, not historical analysis.

Answer

Dynamic Management Views (DMVs)

Answer

Intelligent Insights

Answer

Extended Events

Question 8

You are optimizing an Azure SQL Database that uses the Hyperscale service tier. You need to identify which three actions can improve write performance. Which THREE should you select?

Accepted Answer

Use smaller log writes. Option B is correct: Increasing log rate limit improves log throughput. Option C is correct: Using smaller log writes reduces latency. Option D is correct: Enabling Accelerated Database Recovery reduces log amplification. Option A is incorrect: Increasing service tier to Business Critical does not apply to Hyperscale. Option E is incorrect: Read scale-out does not improve write performance.

Answer

Increase the service tier to Business Critical

Answer

Enable read scale-out

Question 9

You are monitoring an Azure SQL Database. You need to identify which two metrics are most important for detecting a memory pressure issue. Which TWO should you select?

Accepted Answer

Memory grants pending. Option A is correct: Page life expectancy (PLE) indicates how long pages stay in cache; low PLE suggests memory pressure. Option B is correct: Memory grants pending indicates queries waiting for memory. Option C is incorrect: CPU percentage indicates CPU pressure. Option D is incorrect: Data IO percentage indicates I/O pressure. Option E is incorrect: Log IO percentage indicates log write pressure.

Answer

Log IO percentage

Answer

CPU percentage

Answer

Data IO percentage

Question 10

You are monitoring a critical production Azure SQL Database that is experiencing intermittent query timeouts. The database is configured with the General Purpose service tier. You need to identify the root cause of the timeouts with minimal overhead. What should you review first?

Accepted Answer

Use sys.dm_exec_query_stats to identify queries with high wait statistics.. Option A is correct because sys.dm_exec_query_stats provides aggregated query performance data without adding significant overhead, and high wait stats often point to resource contention. Option B is wrong because Query Store insights require Query Store to be enabled and may not be the first step. Option C is wrong because automatic tuning recommendations are for optimization, not troubleshooting. Option D is wrong because scaling up is a reactive measure, not a diagnostic step.

Answer

Implement automatic tuning to force plan regression fixes.

Answer

Increase the database service tier to Business Critical.

Answer

Enable Query Store and review the Regressed Queries report.

Question 11

Your Azure SQL Managed Instance is configured with a long-term backup retention policy of 10 years. You need to reduce storage costs while still meeting a compliance requirement to retain monthly backups for 7 years. What should you do?

Accepted Answer

Configure a new long-term retention policy that retains monthly backups for 7 years.. Option D is correct because configuring a long-term retention (LTR) policy with monthly backups for 7 years meets compliance while reducing cost by not retaining weekly/daily backups for the full 10 years. Option A is wrong because disabling LTR entirely loses compliance. Option B is wrong because moving to Azure SQL Database does not automatically reduce cost and may not be feasible. Option C is wrong because point-in-time restore (PITR) retention is limited to 35 days, not 7 years.

Answer

Disable long-term backup retention and rely solely on point-in-time restore backups.

Answer

Set the point-in-time restore retention period to 7 years.

Answer

Migrate the database to Azure SQL Database and use geo-redundant storage.

Question 12

You are the database administrator for a hybrid environment with on-premises SQL Server 2022 and Azure SQL Database. All databases use the AdventureWorks schema. You notice that a critical stored procedure runs slower on Azure SQL Database than on-premises. Both have identical indexes and statistics. What is the most likely cause?

Accepted Answer

The version of the cardinality estimator differs between environments.. Option C is correct because differences in the cardinality estimation model (e.g., legacy CE vs. default CE) can cause different plan choices, even with identical schema and stats. Option A is wrong because DTU vs. vCore is a purchasing model, not a performance feature that inherently slows queries. Option B is wrong because if indexes are identical, fragmentation impact should be similar. Option D is wrong because intelligent query processing is generally beneficial, not detrimental, and differences would be minimal.

Answer

Azure SQL Database uses a different resource governance model (DTU vs vCore).

Answer

The index fragmentation level is higher on Azure SQL Database due to automatic tuning.

Answer

Azure SQL Database has intelligent query processing features that are not available on-premises.

Question 13

You are monitoring an Azure SQL Database that hosts a financial application. You notice that the average DTU consumption is 20%, but occasionally spikes to 95% for 5-minute intervals. Users report slow response times during these spikes. You need to ensure consistent performance without over-provisioning resources. What should you do?

Accepted Answer

Identify and optimize the queries running during the spike periods, possibly rescheduling a heavy ETL job.. Option B is correct because a scheduled job that runs during the spikes can be optimized by moving it to off-peak hours or tuning it, reducing the spike impact. Option A is wrong because scaling to a higher tier would eliminate spikes but increase cost unnecessarily. Option C is wrong because Query Store helps diagnose but does not resolve the issue. Option D is wrong because Hyperscale is for large databases, not for smoothing spikes.

Answer

Migrate the database to the Hyperscale service tier.

Answer

Scale the database to a higher service tier to absorb the spikes.

Answer

Enable Query Store and use the Regressed Queries feature to find slow queries.

Question 14

Your Azure SQL Database is configured with Active Geo-Replication to a secondary region for disaster recovery. During a routine failover drill, you notice that after failover, the application cannot connect to the new primary because the login credentials fail. The logins are contained in the master database. What is the most likely cause?

Accepted Answer

The SQL logins in the master database are not replicated to the secondary server.. Option D is correct because contained database users are replicated automatically, but non-contained logins in master are not replicated with Active Geo-Replication. Since logins are in master, they are not present on the secondary after failover. Option A is wrong because Firewall rules for Azure services are enabled by default. Option B is wrong because contained users are replicated. Option C is wrong because DNS changes are not required for connectivity after failover if using the same connection string with failover group.

Answer

The DNS name of the secondary server changed after failover.

Answer

The firewall rules on the secondary server do not allow connections from the application IP.

Answer

The application uses contained database users, which are not replicated.

Question 15

You are managing an Azure SQL Database that has Automatic Tuning enabled. You receive an alert that a query plan regression was detected and a plan correction was automatically applied. You want to verify the performance improvement. What should you use?

Accepted Answer

Query the Query Store to compare query performance before and after the plan change.. Option C is correct because Query Store provides detailed query performance data, including plan regressions and improvements. Option A is wrong because Azure Monitor alerts only notify that a regression occurred, not the improvement. Option B is wrong because the automatic tuning log shows actions taken but not performance metrics. Option D is wrong because dynamic management views give current stats but not historical comparison.

Answer

Use sys.dm_exec_query_stats to view current performance.

Answer

Review the Azure Monitor alert details.

Answer

Check the automatic tuning log in the Azure portal.