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

Question 1

Which THREE factors should you consider when choosing between a Gen5 and a Premium-series hardware configuration for an Azure SQL Database? (Select three.)

Accepted Answer

Memory-to-vCore ratio.. The correct answers are B, C, and D. Gen5 and Premium-series hardware configurations for Azure SQL Database differ in memory-to-vCore ratio (B), IOPS and throughput limits (C), and compute generation/CPU architecture (D). Option A is incorrect because the number of vCores available is not a differentiating factor; both series offer a range of vCore counts. Option E is incorrect because both Gen5 and Premium-series support the Business Critical service tier.

Answer

Number of vCores available.

Answer

Support for Business Critical tier.

Question 2

Which TWO metrics are available in Azure Monitor for an Azure SQL Database that can be used to set autoscale rules? (Select two.)

Accepted Answer

CPU percentage. Options A and D are correct because CPU percentage and DTU percentage are standard metrics for autoscaling Azure SQL Database. Option B is wrong because Log write throughput is not typically used for autoscale rules. Option C is wrong because Deadlock count is an event, not a continuous metric. Option E is wrong because Query Store size is not a metric source for autoscaling.

Answer

Log write throughput

Answer

Deadlock count

Answer

Query Store size

Question 3

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 change should you make?

Accepted Answer

Enable serverless with auto-pause enabled.. Enabling serverless with auto-pause allows SalesDB to automatically pause during off-hours (6 PM to 8 AM) when not in use, significantly reducing compute costs for a database used only during business hours. Option D is incorrect because reducing vCores alone does not stop charges during idle time, while auto-pause eliminates compute costs entirely when paused. Option A is incorrect because storage reduction does not address the core issue of paying for idle compute. Option B is incorrect because Hyperscale is designed for high-performance, large-scale workloads and is not cost-optimized for a schedule-based usage pattern.

Answer

Reduce storage to 512 GB.

Answer

Change tier to Hyperscale.

Answer

Reduce capacity to 2 vCores.

Question 4

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.. 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 and is not the first step. Option C is wrong because adding query hints like OPTIMIZE FOR UNKNOWN may not resolve the plan regression and could lead to suboptimal plans for other parameter values. Option D is wrong because scaling up the database increases resources temporarily but does not address the underlying plan regression issue.

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 5

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.. To resolve the timeout errors, you need to reduce the log write throughput to stay within the 50 MB/s limit of the General Purpose tier. Option B achieves this by modifying batch jobs to use bulk insert with TABLOCK, which enables minimal logging, and breaking transactions into smaller batches to reduce the log write rate per transaction. Option A is incorrect because moving to Business Critical is not possible due to budget constraints. Option C is incorrect because increasing storage does not affect the log write throughput limit; it only increases data and log file space. Option D is incorrect because accelerated database recovery reduces the amount of version store I/O during transaction rollback, not the log write throughput during normal operations.

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 6

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). 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 Page Life Expectancy is affected by memory but is a secondary indicator. Option B is wrong because Batch Requests/sec measures throughput, not memory. Option C is wrong because Buffer Cache Hit Ratio indicates cache efficiency but not directly memory pressure.

Answer

Page Life Expectancy

Answer

Batch Requests/sec

Answer

Buffer Cache Hit Ratio

Question 7

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. Query Store in Azure SQL Database provides built-in reports to identify top resource-consuming queries by CPU, IO, and duration, making it the easiest and most direct method. Option B (SQL Server Profiler) is not supported in Azure SQL Database. Option C (Extended Events) is more complex and not the simplest approach for this task. Option D (sys.dm_exec_query_stats) can be used but lacks persistent historical data and is more effort than using Query Store reports.

Answer

Use SQL Server Profiler

Answer

Create an Extended Events session to capture query events

Answer

Query sys.dm_exec_query_stats

Question 8

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). Automatic plan correction (FORCE_LAST_GOOD_PLAN) is the correct feature to automatically force a plan that performed better than the existing plan. Option A (DROP INDEX) and Option B (CREATE INDEX) are for index management, not plan forcing. Option D (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 9

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. 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 10

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. Batch mode execution requires a sufficient memory grant. If the query does not get enough memory, it falls back to row mode. Option B is incorrect: while compatibility level 130 or higher is required for batch mode, the question states the index is a nonclustered columnstore index, and the most common cause of row mode despite having such an index is insufficient memory. Option C is incorrect: a filter predicate on a columnstore index does not prevent batch mode. Option D is incorrect: MAXDOP 1 does not disable batch mode; batch mode can work with a single degree of parallelism.

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 11

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. Azure SQL Database can use autoscale settings (in the DTU or vCore model) to automatically adjust resources based on metrics like CPU usage. Option B (Elastic job) is for running scheduled tasks across databases, not scaling. Option C (Elastic pool) is for managing multiple databases with shared resources, but it does not automatically scale a single database. Option D (Automatic tuning) optimizes query performance, not resource scaling.

Answer

Elastic job

Answer

Elastic pool

Answer

Automatic tuning

Question 12

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 13

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

Accepted Answer

Enable adaptive joins. Adaptive joins (A), interleaved execution for multi-statement table-valued functions (B), and approximate count distinct (E) are all features of Intelligent Query Processing (IQP) in Azure SQL Database. They improve query performance by dynamically adjusting join strategies, avoiding suboptimal plans for MSTVFs, and efficiently handling COUNT(DISTINCT) queries. Query Store (C) is a monitoring and tuning feature but it is not part of IQP. Columnstore indexes (D) are a storage and indexing technique, not part of IQP.

Answer

Enable Query Store

Answer

Enable columnstore indexes

Question 14

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

Accepted Answer

Query sys.dm_os_wait_stats. Options A and E are correct. Querying sys.dm_os_wait_stats (A) directly provides cumulative wait statistics at the instance level. Query Store wait statistics reports (E) offer a query-focused view of wait statistics over time. Option B is incorrect because Azure Monitor Alerts are for setting notifications based on metrics or logs, not for monitoring wait statistics directly. Option C is incorrect because sys.dm_exec_query_stats shows execution statistics like CPU and duration, not wait types. Option D is incorrect because 'Dynamic Management Views' is a broad category, not a specific method; while some DMVs relate to waits, the generic term is not a valid answer.

Answer

Azure Monitor Alerts

Answer

Query sys.dm_exec_query_stats

Answer

Dynamic Management Views

Question 15

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. 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