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

Question 1

You need to optimize costs for SalesDB, which is used only during business hours (8 AM to 6 PM). The database currently runs 24/7. Which two changes should you make?

Accepted Answer

Enable serverless with auto-pause enabled.. Option D is correct because enabling auto-pause will pause the database during off-hours, reducing costs. Option A is wrong because reducing vCores might help but auto-pause is more effective. Option B is wrong because changing to Hyperscale may not reduce costs for this pattern. Option C is wrong because storage reduction is not as impactful.

Answer

Reduce storage to 512 GB.

Answer

Change tier to Hyperscale.

Answer

Reduce capacity to 2 vCores.

Question 2

You are a database administrator for a large retail company. The company uses an Azure SQL Database in the Business Critical tier (8 vCores, 480 GB storage) to run its core transaction processing system. The database has automatic tuning enabled, including FORCE_LAST_GOOD_PLAN and CREATE_INDEX. You notice that the database is experiencing high CPU usage (90% average) during peak hours, and the Query Store shows that a specific query (Query ID 123) has regressed. The automatic tuning feature has forced a plan for this query, but the performance is still poor. You need to resolve the CPU issue and ensure the query runs efficiently. What should you do first?

Accepted Answer

Use Query Store to compare the forced plan with the previous good plan and update statistics.. Option B is correct because reviewing the plan history in Query Store helps identify why the forced plan is not optimal, and perhaps the regression is due to parameter sniffing or outdated statistics. Option A is wrong because disabling automatic tuning may cause further regression. Option C is wrong because scaling up might be unnecessary if plan issue is fixable. Option D is wrong because modifying data types is a heavy change.

Answer

Disable automatic tuning and manually create a plan guide for the query.

Answer

Modify the query to use query hints like OPTIMIZE FOR UNKNOWN.

Answer

Scale up the database to 16 vCores to handle the CPU load.

Question 3

You are a database consultant for a financial services company that uses an Azure SQL Managed Instance (MI) in the General Purpose tier (16 vCores, 1024 GB storage) for a critical application. The MI hosts a database that processes large batch transactions every night. Recently, the batch jobs have been failing due to timeout errors. You notice that the log write throughput is hitting the service tier limit (50 MB/s for General Purpose). The business requires the batch to complete within the same time window. You cannot change the application code or move to Business Critical tier due to budget constraints. You need to ensure the batch jobs complete successfully. What should you recommend?

Accepted Answer

Modify the batch jobs to use bulk insert with TABLOCK and batch inserts into smaller transactions.. Option C is correct because reducing the log write rate by batching transactions or using minimally logged operations (e.g., bulk insert with TABLOCK) can keep throughput under the limit. Option A is wrong because increasing storage doesn't affect log throughput. Option B is wrong because moving to Business Critical is not allowed. Option D is wrong because enabling accelerated database recovery reduces version store I/O, not log throughput.

Answer

Migrate the database to Business Critical tier.

Answer

Increase the managed instance storage to 2048 GB to improve log throughput.

Answer

Enable accelerated database recovery to reduce log I/O.

Question 4

You manage a SQL Server on Azure VM. Users report slow queries during peak hours. You suspect memory pressure. Which metric should you monitor in Azure Monitor to confirm this?

Accepted Answer

Target Server Memory (KB). Option B is correct because Target Server Memory (KB) shows the amount of memory SQL Server is trying to acquire; consistently high values relative to Total Server Memory indicate memory pressure. Option A is wrong because Buffer Cache Hit Ratio indicates cache efficiency but not directly memory pressure. Option C is wrong because Page Life Expectancy is affected by memory but is a secondary indicator. Option D is wrong because Batch Requests/sec measures throughput, not memory.

Answer

Page Life Expectancy

Answer

Batch Requests/sec

Answer

Buffer Cache Hit Ratio

Question 5

Your Azure SQL Database is experiencing high DTU consumption. You need to identify the top resource-consuming queries. What should you do?

Accepted Answer

Use the Query Store reports in the Azure portal. Option C is correct because the Query Store is the primary tool in Azure SQL Database for tracking query performance, including CPU, IO, and duration. Option A is wrong because dynamic management views like sys.dm_exec_query_stats can be used but require more effort and may not persist history. Option B is wrong because Extended Events are for custom monitoring, not built-in query analysis. Option D is wrong because Azure SQL Database does not support SQL Server Profiler.

Answer

Use SQL Server Profiler

Answer

Create an Extended Events session to capture query events

Answer

Query sys.dm_exec_query_stats

Question 6

You have an Azure SQL Managed Instance and notice that automatic tuning is not enabled. You want to automatically force a plan that performed better than the existing plan. What should you enable?

Accepted Answer

Automatic plan correction (FORCE_LAST_GOOD_PLAN). Option A is correct because the FORCE_LAST_GOOD_PLAN option in automatic tuning automatically forces a plan when a regression is detected. Option B is wrong because CREATE INDEX automates index creation, not plan forcing. Option C is wrong because DROP INDEX is for unused indexes. Option D is wrong because Intelligent Insights provides analysis but does not automatically force plans.

Answer

Automatic index management (DROP INDEX)

Answer

Automatic index management (CREATE INDEX)

Answer

Intelligent Insights

Question 7

Your Azure SQL Database is configured with the Hyperscale service tier. You observe increased redo log latency. Which resource is most likely the bottleneck?

Accepted Answer

Log service throughput. Option B is correct because in Hyperscale, the log service is a separate component that handles redo; high latency there directly affects redo speed. Option A is wrong because compute node CPU affects query processing, not redo latency specifically. Option C is wrong because page servers handle data reads, not redo. Option D is wrong because storage IO is distributed and rarely the bottleneck for redo in Hyperscale.

Answer

Compute node CPU

Answer

Page server IO

Answer

Remote storage IOPS

Question 8

You are tuning a query in Azure SQL Database that uses a nonclustered columnstore index. The query is supposed to use batch mode execution but shows row mode. What is the most likely cause?

Accepted Answer

The query does not have enough memory grant. Option D is correct because batch mode requires sufficient memory grant; if the query is memory-constrained, it may fall back to row mode. Option A is wrong because compatibility level 130 or higher supports batch mode. Option B is wrong because columnstore indexes support batch mode by default. Option C is wrong because MAXDOP setting does not disable batch mode.

Answer

The database compatibility level is below 130

Answer

The index is defined with a filter predicate

Answer

The query hint MAXDOP 1 is used

Question 9

You need to configure Azure SQL Database to automatically scale up based on CPU usage. Which feature should you use?

Accepted Answer

Autoscale settings for the database. Option B is correct because autoscale settings on the DTU or vCore model allow automatic scaling based on metrics like CPU. Option A is wrong because elastic pools are for managing multiple databases, not auto-scaling a single database. Option C is wrong because elastic jobs are for scheduling tasks. Option D is wrong because automatic tuning optimizes performance but does not scale resources.

Answer

Elastic job

Answer

Elastic pool

Answer

Automatic tuning

Question 10

Which TWO metrics should you monitor in Azure SQL Database to detect a potential memory pressure issue?

Accepted Answer

Page Life Expectancy. Page Life Expectancy (PLE) is a key metric that indicates how long a data page remains in the buffer pool before being evicted. A consistently low PLE (e.g., below 300 seconds) signals that pages are being flushed too quickly due to memory pressure, often from insufficient buffer pool memory. Memory Grants Pending tracks the number of queries waiting for a memory grant to execute; a non-zero value indicates that the server cannot allocate enough memory to satisfy query workspace requirements, directly pointing to memory pressure.

Answer

Average CPU percent

Answer

Log IO

Answer

Data IO

Question 11

Which THREE actions can you take to optimize query performance in Azure SQL Database using Intelligent Query Processing?

Accepted Answer

Enable adaptive joins. Option B, C, D are correct. Approximate Count Distinct is part of Intelligent Query Processing that improves performance of COUNT(DISTINCT) queries. Adaptive Joins switch join strategies dynamically. Interleaved Execution for multi-statement table-valued functions helps avoid suboptimal plans. Option A is wrong because Columnstore indexes are not part of Intelligent Query Processing. Option E is wrong because Query Store is a feature but not part of Intelligent Query Processing.

Answer

Enable Query Store

Answer

Enable columnstore indexes

Question 12

Which TWO methods can you use to monitor Azure SQL Database wait statistics?

Accepted Answer

Query sys.dm_os_wait_stats. Option A and B are correct. sys.dm_os_wait_stats is a DMV that shows cumulative wait statistics. Query Store also captures wait statistics for queries. Option C is wrong because sys.dm_exec_query_stats shows query performance but not waits. Option D is wrong because Azure Monitor metrics do not include wait statistics. Option E is wrong because Dynamic Management Views is a category, not a specific method.

Answer

Azure Monitor Alerts

Answer

Query sys.dm_exec_query_stats

Answer

Dynamic Management Views

Question 13

You are reviewing an ARM template for Azure SQL Database. The exhibit shows the database settings. You notice the database is not being automatically paused. What is the most likely explanation?

Accepted Answer

The database uses VBS enclaves which are incompatible with serverless. Option D is correct because auto-pause is only supported for General Purpose serverless databases, and the use of VBS enclave (preferredEnclaveType: "VBS") indicates Always Encrypted with secure enclaves, which is not supported with serverless. Option A is wrong because minCapacity 0.5 is valid for serverless. Option B is wrong because licenseType BasePrice does not affect auto-pause. Option C is wrong because autoPauseDelay 60 minutes is valid; the default is 60.

Answer

The minCapacity is set too low

Answer

The autoPauseDelay is set to 60 minutes

Answer

The licenseType is set to BasePrice

Question 14

You are a database administrator for a medium-sized e-commerce company. The company runs its online transaction processing (OLTP) workload on an Azure SQL Database in the General Purpose service tier (DTU-based, S3). The database is used for order processing, inventory management, and customer data. Recently, during peak shopping hours (10 AM to 2 PM), users have reported that order entry forms take several seconds to submit, and inventory queries are timing out. Monitoring shows that DTU usage regularly hits 100% during these hours, with high PAGELATCH_IO waits. You need to resolve the performance issue with minimal cost increase. What should you do?

Accepted Answer

Migrate to the vCore model with Hyperscale service tier. Option B is correct because switching to the vCore model with Hyperscale tier provides better scalability and eliminates resource contention by separating compute and storage, reducing PAGELATCH_IO waits. Option A is wrong because scaling to S4 only adds more DTU but does not address the architecture limitation causing PAGELATCH_IO. Option C is wrong because adding a read-only replica does not help write-heavy OLTP. Option D is wrong because increasing storage does not affect DTU limits.

Answer

Increase the max storage size to 1 TB

Answer

Increase the service tier to S4 during peak hours

Answer

Create a read-only replica and offload reporting queries

Question 15

You manage a critical Azure SQL Database that supports a financial application. The database is configured with the Business Critical service tier (8 vCores) and uses Always On availability groups for high availability. Recently, the application team noticed that some transactions are experiencing high latency during writes. You investigate and find that the log write latency is averaging 10 ms, and there are occasional HADR_SYNC_COMMIT waits. The application requires durable writes with synchronous commit. You need to reduce write latency without compromising data durability. What should you do?

Accepted Answer

Increase the number of vCores to 16. Option C is correct because increasing the number of vCores improves the log write throughput and reduces latency, as the log writer can use more CPU and memory. Option A is wrong because reducing the number of replicas is not possible in Business Critical (always 4). Option B is wrong because Premium SSD v2 may not be available in all regions and is not a direct fix for log write latency. Option D is wrong because turning off automatic tuning does not affect log write latency.

Answer

Switch to Premium SSD v2 storage

Answer

Disable automatic tuning to reduce overhead

Answer

Reduce the number of secondary replicas to minimize synchronization overhead

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

Exhibit

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

Exhibit