DP-300 Practice Test 32 — 15 Questions

Question 1

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. RESOURCE_SEMAPHORE waits indicate memory grant pressure, typically caused by queries requesting large memory grants. Option D is correct. Option A is incorrect because blocking and deadlocks produce LCK_M_* waits, not RESOURCE_SEMAPHORE. Option B is incorrect because high CPU usage leads to SOS_SCHEDULER_YIELD waits. Option C is incorrect because disk I/O bottlenecks cause PAGEIOLATCH waits.

Answer

Blocking and deadlocks

Answer

High CPU usage

Answer

Disk I/O bottlenecks

Question 2

You are optimizing an Azure SQL Database that uses the Business Critical tier. Which TWO factors affect the maximum log rate?

Accepted Answer

Service level objective (SLO). The maximum log rate in Azure SQL Database Business Critical tier is determined by the service level objective (SLO) which includes a preset log rate limit, and the number of vCores as part of that SLO. The number of log files does not affect the log rate; log write throughput is governed by the storage performance tied to the SLO. Backup retention period and page compression level have no impact on log rate.

Answer

Backup retention period

Answer

Number of log files

Answer

Page compression level

Question 3

Refer to the exhibit. You retrieve the configuration of an Azure SQL Database. Based on the JSON output, which feature is enabled?

Accepted Answer

Intelligent Insights. The JSON output shows "isIntelligentInsightsOn": true, indicating that the Intelligent Insights feature is enabled for the Azure SQL Database. Option A is incorrect because "isLedgerOn" is false. Option B is incorrect because there is no mention of availability groups in the JSON. Option D is incorrect because even though "preferredEnclaveType" is set to VBS, Always Encrypted with secure enclaves requires explicit configuration on columns, and the setting alone does not mean the feature is enabled.

Answer

Ledger tables

Answer

Always On availability groups

Answer

Always Encrypted with secure enclaves

Question 4

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. Using smaller log writes reduces log write latency, which improves write performance in Hyperscale. Option B is correct: Increasing the log rate limit allows higher log throughput, directly enhancing write performance. Option E is correct: Enabling Accelerated Database Recovery reduces log amplification and improves log write efficiency, benefiting write performance. Option C is incorrect: The Hyperscale service tier is distinct from Business Critical; moving to Business Critical does not apply to Hyperscale. Option D is incorrect: Read scale-out improves read performance, not write performance.

Answer

Increase the service tier to Business Critical

Answer

Enable read scale-out

Question 5

You manage an Azure SQL Database with the Premium service tier. You need to reduce storage costs by automatically deleting old audit logs that are older than 90 days. What should you configure?

Accepted Answer

Configure a lifecycle management policy on the Azure Storage account that stores the audit logs.. Azure SQL Database audit logs are stored in Azure Storage. A lifecycle management policy on the storage account can automatically delete blobs older than a specified number of days, such as 90 days, reducing storage costs. Option A is incorrect because Extended Events are not designed for automated deletion of audit logs. Option B is incorrect because backup retention is unrelated to audit logs. Option C is incorrect because the audit retention policy in Azure SQL Database's server audit settings only controls how long audit records are retained in the database, not the actual deletion of stored logs.

Answer

Create an Extended Events session that filters and deletes old audit events.

Answer

Reduce the backup retention period to 90 days.

Answer

Set the audit retention policy to 90 days in the server's audit settings.

Question 6

Which TWO actions can help you identify and resolve performance bottlenecks related to I/O in an Azure SQL Database?

Accepted Answer

Query sys.dm_exec_requests and filter on wait_type like PAGEIOLATCH.. Options A and B are correct. A: Querying sys.dm_exec_requests with a filter on PAGEIOLATCH wait type identifies queries waiting for I/O, pinpointing I/O bottlenecks. B: Data compression reduces the size of data pages, decreasing I/O operations for large tables. C: Increasing the service tier adds compute resources (CPU/memory) but does not directly address I/O bottlenecks; it may only mask the issue. D: sys.dm_db_resource_stats provides average I/O per minute at the database level, which is useful for monitoring but not for per-query I/O bottleneck identification. E: sys.dm_os_performance_counters for CPU usage tracks CPU performance, not I/O.

Answer

Increase the database service tier to add more compute resources.

Answer

Use sys.dm_db_resource_stats to see average I/O per minute.

Answer

Monitor sys.dm_os_performance_counters for CPU usage.

Question 7

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. PAGEIOLATCH_SH waits indicate I/O bottlenecks caused by excessive page reads from disk. Adding appropriate indexes reduces the number of pages read by enabling more efficient data access (e.g., index seeks instead of table scans), directly reducing I/O. Option A is incorrect because increasing max memory does not address the underlying query inefficiency driving I/O. Option C is incorrect because page compression reduces storage but may increase CPU and does not primarily reduce I/O waits. Option D is incorrect because forcing parameterization improves plan reuse but does not target I/O reduction.

Answer

Increase the database max memory

Answer

Enable page compression on large tables

Answer

Force parameterization of queries

Question 8

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. `az sql db resume` is the command to resume a paused database. Option A is wrong because `az sql db restart` restarts an online database but does not resume a paused one. Option C is wrong because `az sql db start` is not a valid command for Azure SQL Database. Option D is wrong because `az sql db update` can modify properties but cannot resume a paused database; resuming requires a dedicated command.

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 9

You are troubleshooting a performance issue on an Azure SQL Database. Which THREE Dynamic Management Views (DMVs) should you query to identify the most resource-intensive queries?

Accepted Answer

sys.dm_exec_requests. The correct answers are A, C, and E. Option A (sys.dm_exec_requests) shows currently executing requests, which helps identify active resource-intensive queries. Option C (sys.dm_exec_query_plan) provides the execution plan for cached queries, enabling analysis of query performance. Option E (sys.dm_exec_query_stats) returns aggregated performance statistics (e.g., CPU time, logical reads) for cached query plans, making it ideal for identifying resource-intensive queries across all executions. Option B (sys.dm_os_wait_stats) is incorrect because it shows instance-level wait statistics, not query-specific resource usage. Option D (sys.dm_exec_sessions) is incorrect as it provides session-level information, not query-level resource metrics.

Answer

sys.dm_os_wait_stats

Answer

sys.dm_exec_sessions

Question 10

You are designing a secure environment for Azure SQL Database. Which TWO of the following are recommended practices for network security?

Accepted Answer

Use Azure Private Link to connect to the database from a virtual network.. Options C and D are correct. Azure Private Link (C) ensures traffic between the virtual network and Azure SQL Database stays within the Microsoft backbone network, avoiding the public internet. Disabling public network access (D) at the server level blocks all internet traffic, enhancing security. Option A is incorrect because enabling 'Allow Azure services...' is a broad rule that permits traffic from any Azure service, which may be insecure. Option B is incorrect because VNet service endpoints are legacy and less secure compared to Private Link. Option E is incorrect because allowing a large IP range increases the attack surface.

Answer

Enable the 'Allow Azure services and resources to access this server' firewall setting.

Answer

Use VNet service endpoints instead of Private Link to reduce costs.

Answer

Add firewall rules that allow all IP addresses from your organization's IP range.

Question 11

You are monitoring an Azure SQL Database using Intelligent Insights. You receive an alert indicating 'Degradation in performance due to increased log write wait time'. What is the most likely cause of this issue?

Accepted Answer

The log rate limit has been reached due to high transaction throughput. High log write wait times typically indicate that the transaction log throughput is a bottleneck, often due to the log rate limit. Option A is wrong because high CPU utilization would cause other wait types like SOS_SCHEDULER_YIELD, not WRITELOG. Option B is wrong because long-running blocking transactions cause wait types like LCK_M_*, not increased log write wait time. Option D is wrong because insufficient storage space for data files causes different symptoms, such as write errors or data file growth issues, but not specifically log write wait.

Answer

High CPU utilization on the database server

Answer

Long-running blocking transactions

Answer

Insufficient storage space for data files

Question 12

You are monitoring an Azure SQL Database using the sys.dm_db_resource_stats DMV. The avg_log_write_percent column shows 95% for the last hour. What does this indicate, and what should you do?

Accepted Answer

The database is nearing its log write IOPS limit; consider scaling up or optimizing log writes.. The avg_log_write_percent metric in sys.dm_db_resource_stats measures the percentage of the log write IOPS limit used. At 95%, the database is nearing its log write IOPS limit, which can cause transaction delays and throttling. The appropriate response is to scale up the service tier (e.g., to a higher DTU or vCore level) or optimize log writes to reduce IOPS consumption. Option A is incorrect because the metric does not indicate running out of log space; log space is separate. Option B is incorrect because storage scale (size) does not directly affect log write IOPS. Option D is incorrect because this metric is specifically about log I/O, not CPU.

Answer

The database is out of transaction log space; increase the max log size.

Answer

The database storage is running out; scale up storage.

Answer

The CPU is overloaded; scale up CPU.

Question 13

Your Azure SQL Managed Instance is experiencing high PAGELATCH_SH waits. You need to reduce this contention. What should you implement?

Accepted Answer

Add more data files to the filegroup. Adding more data files to the filegroup spreads out page allocations, reducing contention for allocation structures and thus decreasing PAGELATCH_SH waits. Option A is incorrect because scaling up may provide more resources but does not directly address the page latch contention caused by allocation bottlenecks. Option B is incorrect because delayed durability only affects transaction log write behavior, not page latches. Option C is incorrect because a readable secondary replica does not alleviate latch contention on the primary; it is designed for read workload offloading, not contention reduction.

Answer

Scale up the managed instance to a higher service tier

Answer

Enable delayed durability

Answer

Configure a readable secondary replica

Question 14

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. REORGANIZE is the recommended maintenance operation for clustered columnstore indexes to defragment them by removing rows from deleted bitmaps without requiring a full rebuild. Option A (drop and recreate) is incorrect because it is disruptive and unnecessary for resolving fragmentation. Option C (update statistics) does not address index fragmentation directly. Option D (rebuild) is more resource-intensive and typically reserved for indexes with high fragmentation, whereas reorganize is optimal for regular maintenance.

Answer

Drop and recreate the columnstore index

Answer

Update statistics on the table

Answer

Rebuild the columnstore index

Question 15

You are troubleshooting a transaction log growth issue on an Azure SQL Database. Which THREE conditions can cause the transaction log to grow unexpectedly?

Accepted Answer

Replication that has not delivered transactions to the subscriber. Options A, C, and D are correct. A long-running uncommitted transaction (C) prevents log truncation, causing growth. Replication with undelivered transactions (A) holds log space until delivery completes. Excessive updates due to a missing index (D) generate many log records, increasing log size. Option B is incorrect because memory-optimized tables use a different logging mechanism (e.g., native redo) that does not inherently cause more log growth than disk-based tables. Option E is incorrect because page compression reduces the size of data pages, which can decrease log record size for changes, not cause growth.

Answer

A memory-optimized table with a large number of rows

Answer

Page compression enabled on tables