DP-300 Practice Test 29 — 15 Questions

Question 1

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 2

You are reviewing the configuration of an Azure SQL Database. The database is currently 250 GB in size. You are concerned about storage capacity. What is the maximum size limit for this database?

Accepted Answer

250 GB. The exhibit shows maxSizeBytes: 268435456000, which is 250 GB. Option D is correct. Option A is wrong because 4 TB is not the limit shown. Option B is wrong because 1 TB is incorrect. Option C is wrong because 500 GB is incorrect.

Answer

1 TB

Answer

500 GB

Answer

4 TB

Question 3

You are deploying a new application on Azure SQL Database. The application requires that all connections use a specific login, 'AppUser', with the least privileges necessary. The login should only be able to execute stored procedures in the 'Sales' schema and should not have direct access to underlying tables. What should you do?

Accepted Answer

Create a database role, grant EXECUTE on the 'Sales' schema to the role, and add 'AppUser' to the role.. Option C is correct because creating a role with EXECUTE permission on the schema grants execute on all stored procedures in that schema. Option A is wrong because granting SELECT on schema gives read access to tables. Option B is wrong because granting EXECUTE on individual procedures is more administrative work. Option D is wrong because granting CONTROL is excessive.

Answer

Grant the SELECT permission on the 'Sales' schema to 'AppUser'.

Answer

Add 'AppUser' to the db_datareader role.

Answer

Grant the EXECUTE permission on each stored procedure individually to 'AppUser'.

Question 4

You are monitoring an Azure SQL Database and notice high PAGELATCH waits. What is the most likely cause?

Accepted Answer

Concurrent inserts into a table with a clustered index causing last-page contention.. Option A is correct because PAGELATCH waits indicate contention on data pages, often due to concurrent inserts to the same page. Option B is wrong because buffer pool insufficient is PAGEIOLATCH. Option C is wrong because blocking is related to lock waits. Option D is wrong because CPU pressure is not directly related to page latches.

Answer

Insufficient buffer pool size leading to frequent reads from disk.

Answer

High CPU usage due to inefficient queries.

Answer

Long-running transactions blocking other queries.

Question 5

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 6

You run the query in the exhibit on an Azure SQL Database. The result shows high wait_time_ms for PAGEIOLATCH_SH waits. What does this indicate?

Accepted Answer

I/O subsystem bottleneck for read operations. PAGEIOLATCH_SH waits indicate I/O latency for reading data pages from disk. Option B is correct. Option A is wrong because PAGEIOLATCH_SH is for I/O, not memory. Option C is wrong because it is not CPU-related. Option D is wrong because it is not blocking.

Answer

CPU bottleneck

Answer

Blocking between concurrent transactions

Answer

Memory pressure

Question 7

You manage an Azure SQL Database that is part of an Always On availability group in Azure SQL Managed Instance. You notice that the secondary replica is experiencing high log send queue size. The primary replica has ample CPU and I/O resources. The network latency between replicas is low. What is the most likely cause of the high log send queue?

Accepted Answer

The secondary replica's log apply rate is slower than the log generation rate on the primary.. Option A is correct because if the secondary replica is not able to apply log records quickly enough, the log send queue grows. This can happen if the secondary has insufficient resources or is under load. Option B is wrong because direct network latency is low. Option C is wrong because the primary has ample resources. Option D is wrong because log flush on the primary is not the issue.

Answer

The secondary replica is configured with asynchronous commit.

Answer

The primary replica is experiencing log flush delays.

Answer

The network bandwidth between replicas is insufficient.

Question 8

The query returns a list of query hashes with high average duration. You need to identify which queries are most likely causing CPU pressure. What additional metric should you include?

Accepted Answer

Include avg_cpu_time to measure CPU usage.. Option C is correct because CPU time directly measures CPU usage per query, helping correlate high duration with CPU pressure. Option A is wrong because logical reads indicate I/O, not necessarily CPU. Option B is wrong because execution count without CPU may miss resource intensity. Option D is wrong because wait stats indicate blocking, not CPU.

Answer

Include wait_stats to see blocking.

Answer

Include count_executions to see frequency.

Answer

Include avg_logical_reads to see I/O consumption.

Question 9

You are monitoring an Azure SQL Database using sys.dm_db_wait_stats. You see a high percentage of WRITELOG waits. What is the most likely cause?

Accepted Answer

The transaction log is on a slow I/O subsystem.. Option A is correct because WRITELOG waits indicate that transaction log writes are slower than expected, often due to log I/O latency. Option B is wrong because tempdb contention shows PAGELATCH waits. Option C is wrong because blocking shows LCK_M_* waits. Option D is wrong because CPU pressure shows SOS_SCHEDULER_YIELD.

Answer

Tempdb has allocation contention.

Answer

Queries are blocked by locks.

Answer

CPU is under pressure.

Question 10

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 11

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 12

You have an Azure SQL Database that is used by a reporting application. Reports run slowly during the day. You notice that the database is at the S1 tier. You need to improve report performance without changing application code. What should you do?

Accepted Answer

Scale up the database to a higher service tier.. Option B is correct because scaling up to a higher service tier (e.g., S2 or S3) provides more DTUs, improving report performance. Option A is wrong because index maintenance helps but may not be sufficient if resource is the bottleneck. Option C is wrong because read scale-out is only available on Premium/Business Critical. Option D is wrong because partitioning adds complexity and is not a guaranteed fix.

Answer

Rebuild fragmented indexes.

Answer

Implement table partitioning.

Answer

Enable read scale-out to offload reports.

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 manage an Azure SQL Database that uses the General Purpose tier. You need to monitor the performance of the database and identify the top resource-consuming queries. You want to use a built-in feature that requires no additional cost. What should you use?

Accepted Answer

Query Store.. Option D is correct because Query Store is a built-in, no-cost feature that tracks query performance. Option A is wrong because Azure SQL Analytics is a paid solution. Option B is wrong because dynamic management views require manual querying. Option C is wrong because SQL Server Profiler is deprecated and not supported in Azure SQL Database.

Answer

Azure SQL Analytics (preview) in Azure Monitor.

Answer

SQL Server Profiler.

Answer

sys.dm_exec_query_stats and sys.dm_exec_sql_text DMVs.

Question 15

You manage an Azure SQL Database that has automatic tuning enabled. You receive an alert that the database is experiencing plan regression. The automatic tuning has forced a plan, but performance is still poor. What should you do first?

Accepted Answer

Review the Query Store to identify the root cause of regression.. Option B is correct because Query Store can show plan history and regression details, helping analyze why the forced plan is not optimal. Option A is wrong because reverting may cause further regression. Option C is wrong because manual plan guide may be too drastic. Option D is wrong because scaling up doesn't address plan quality.

Answer

Disable automatic tuning and create a plan guide.

Answer

Manually revert to the previous plan using Query Store.

Answer

Scale up the database to reduce resource pressure.