DP-300 Monitor, configure, and optimize database resources Practice Test 14 — 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?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Migrate to the vCore model with Hyperscale service tier. Migrate to the vCore model with Hyperscale service tier. The issue is high DTU usage with PAGELATCH_IO waits, indicating resource contention on I/O. Hyperscale architecture separates compute and storage, eliminating such bottlenecks and providing near-instant scaling for peak loads. Option A (increase storage) does not address DTU limits. Option B (increase to S4) adds more DTUs but retains the same architecture limitations that cause PAGELATCH_IO. Option C (read replica) only offloads read queries, not write-heavy OLTP.

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 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. Increasing the number of vCores (Option D) provides additional CPU and memory resources for the log writer, which can process transactions faster, thereby reducing log write latency. In the Business Critical tier, the log write path is CPU-intensive, and scaling up directly improves throughput. Option A is incorrect because Premium SSD v2 is not applicable to Azure SQL Database; storage is managed by the service. Option B is incorrect because disabling automatic tuning does not impact log write performance and may negatively affect overall query performance. Option C is incorrect because the number of secondary replicas is fixed at three in Business Critical, and reducing them is not possible; further, synchronous commit requires at least one secondary to ensure durability.

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

Question 3

You are responsible for a set of Azure SQL Databases that are used by different departments in your organization. The databases are deployed in an elastic pool with Standard tier (eDTU 200). Usage patterns show that the marketing database uses high CPU during the day, while the sales database uses high IO at night. You want to optimize costs while ensuring each database gets the resources it needs. What should you do?

Accepted Answer

Configure minimum and maximum eDTU per database in the pool. Configuring minimum and maximum eDTU per database within the elastic pool guarantees that each database gets the resources it needs during peak usage (e.g., high CPU for marketing during the day, high IO for sales at night) while still sharing the pool's overall eDTU, optimizing costs. Option B is incorrect because moving to a vCore-based pool does not inherently address resource contention; it's a different purchasing model. Option C is incorrect because adding more databases to the pool increases contention and may degrade performance. Option D is incorrect because moving each database to a standalone DTU tier would likely increase costs and eliminate the benefits of pooling.

Answer

Migrate the pool to a vCore-based elastic pool

Answer

Add more databases to the pool to spread the load

Answer

Move each database to a standalone DTU tier

Question 4

You administer a large Azure SQL Database that is used for a SaaS application. The database has a table with over 1 billion rows that is frequently queried by customer ID. The table currently has a clustered index on an identity column and a nonclustered index on customer ID. Queries that filter by customer ID are experiencing high IO and long execution times. You analyze the execution plan and see that the nonclustered index is used, but there are many key lookups. You need to optimize the query performance while minimizing storage overhead. What should you do?

Accepted Answer

Add all queried columns as included columns to the nonclustered index. Adding all queried columns as included columns to the existing nonclustered index on customer ID creates a covering index. This eliminates the need for key lookups, reducing IO and improving query performance for point lookups by customer ID. The storage overhead is minimal since included columns are stored only at the leaf level. Option A is wrong because a clustered columnstore index is designed for analytical workloads and can degrade point lookup performance. Option B is wrong because a filtered index on frequent values still may not cover all columns, leading to key lookups. Option C is wrong because partitioning does not eliminate key lookups and can add complexity without performance benefit for point queries.

Answer

Create a clustered columnstore index on the table

Answer

Create a filtered index on customer ID for frequent values

Answer

Partition the table by customer ID

Question 5

You are managing an Azure SQL Database in the General Purpose service tier with 100 DTUs. The database supports an e-commerce application. Over the past week, you notice that CPU usage frequently reaches 100% during peak hours, causing query timeouts. You have identified that the most expensive query is a SELECT statement that joins five tables and returns aggregated sales data. You need to reduce CPU pressure without changing the service tier or adding indexes. What should you do?

Accepted Answer

Use Query Store to identify and force a more efficient execution plan.. Using Query Store to identify and force a more efficient execution plan can reduce CPU usage without changing the service tier or adding indexes. Option A is incorrect because scaling to Business Critical changes the service tier, which is not allowed. Option B is incorrect because increasing DTUs changes the performance level and may increase cost, and it doesn't address the root cause of the expensive query. Option D is incorrect because adding indexes is explicitly prohibited by the requirement.

Answer

Scale up to the Business Critical tier.

Answer

Increase the DTUs to 200.

Answer

Add a nonclustered index on the join columns.

Question 6

You are the database administrator for a company that uses Azure SQL Managed Instance. The instance hosts a mission-critical database that experiences periodic performance degradation. You need to set up a proactive monitoring solution that sends alerts when the average DTU usage exceeds 80% over a 5-minute period. The solution should minimize cost. What should you do?

Accepted Answer

Create an Azure Monitor metric alert on the 'dtu_consumption_percent' metric for the managed instance.. Azure Monitor metric alerts on the 'dtu_consumption_percent' metric provide a cost-effective, proactive monitoring solution for Azure SQL Managed Instance. Metric alerts are charged per rule, and there is no additional ingestion cost, making them minimal cost. Option B is incorrect because SQL Server Agent jobs require a SQL Server Agent service and are not designed for real-time metric monitoring; they also incur overhead. Option C is incorrect because Elastic Database Jobs are for executing T-SQL across databases, not for metric-based alerting. Option D is incorrect because streaming diagnostic logs to a Log Analytics workspace and creating a log alert rule incurs data ingestion costs, which is not minimal cost.

Answer

Create a SQL Server Agent job that checks sys.dm_db_resource_stats every 5 minutes and sends an email.

Answer

Use Elastic Database Jobs to run a query periodically and send alerts.

Answer

Stream diagnostic logs to a Log Analytics workspace and create a log alert rule.

Question 7

You are a database administrator for a large financial services company. You manage an Azure SQL Database in the Business Critical tier with a failover group configured for disaster recovery. The database has a heavy OLTP workload. You notice that the secondary replica is experiencing high log write latency, impacting the primary's performance due to synchronous commit. You need to minimize the performance impact on the primary while maintaining disaster recovery capabilities. What should you do?

Accepted Answer

Change the failover group to use asynchronous commit mode.. Changing the failover group to asynchronous commit mode decouples the primary's transaction commit from the secondary's log write. In synchronous mode, the primary waits for the secondary to confirm log hardening, which causes performance impact when the secondary has high log write latency. Asynchronous commit allows the primary to commit without waiting, thus minimizing performance impact while still maintaining disaster recovery capabilities (the secondary will apply changes eventually, though with potential data loss if a failover occurs before sync). Option A is incorrect because backup storage redundancy (LRS vs GRS) only affects backup storage, not live log write latency for replication. Option B is incorrect because adding another secondary does not reduce latency on the existing secondary; it could even increase overhead. Option D is incorrect because decreasing the secondary's service tier to General Purpose would reduce its I/O capacity and likely worsen log write latency.

Answer

Change the backup storage redundancy of the secondary replica to locally-redundant storage (LRS).

Answer

Add an additional secondary replica to distribute the log write load.

Answer

Decrease the service tier of the secondary replica to General Purpose.

Question 8

You are a database administrator for a SaaS company. You manage an Azure SQL Database that uses the Hyperscale service tier. The database supports a multi-tenant application with unpredictable workloads. You notice that the database's page server is experiencing high IOPS utilization, causing query performance degradation. You need to improve performance by optimizing the page server usage. What should you do?

Accepted Answer

Increase the number of secondary replicas to distribute read workload.. In Azure SQL Database Hyperscale, increasing the number of secondary replicas can help distribute read workloads. Secondary replicas have their own buffer pool caches, which can reduce page server IOPS utilization by serving read requests from cache rather than repeatedly fetching pages from the page server. Option B is incorrect because Accelerated Database Recovery (ADR) reduces undo log space requirements but does not affect page server IOPS. Option C is incorrect because changing to Business Critical tier moves away from the Hyperscale architecture and does not directly address page server performance. Option D is incorrect because increasing MAXDOP may increase parallelism and potentially lead to higher IOPS, not reduce it.

Answer

Enable accelerated database recovery (ADR) to reduce log IO.

Answer

Change the service tier to Business Critical to get faster IO.

Answer

Increase the MAXDOP setting for the database.

Question 9

You are a database administrator for an e-commerce company that uses Azure SQL Managed Instance. You have a database that experiences high blocking due to long-running transactions. You need to configure a solution that automatically terminates the blocking process after it has been waiting for more than 5 seconds. The solution should be set at the database level and should not require any application changes. What should you do?

Accepted Answer

Set the DEADLOCK_PRIORITY to LOW on the blocking session and set LOCK_TIMEOUT to 5000.. LOCK_TIMEOUT can be used to automatically terminate a query that has been waiting for a lock longer than a specified number of milliseconds. Setting LOCK_TIMEOUT to 5000 will kill the waiting (blocked) session after 5 seconds, which mitigates blocking. While LOCK_TIMEOUT is a session-level setting, it can be applied database-wide by using a logon trigger or by setting server-level defaults without modifying application code. Resource Governor does not have a MAX_GRANT_TIME setting for T-SQL queries, so option B is invalid. RCSI and snapshot isolation reduce blocking but do not terminate long-running transactions, and setting the isolation level for all sessions requires application changes or connection settings.

Answer

Enable read committed snapshot isolation (RCSI) on the database.

Answer

Create a resource governor workload group with a maximum grant time of 5 seconds.

Answer

Set the transaction isolation level to SNAPSHOT for all sessions.

Question 10

You are optimizing an Azure SQL Database that runs a reporting workload. The database is in the General Purpose tier. You notice that many queries are performing table scans on large tables. Which TWO actions would most likely improve query performance without increasing costs?

Accepted Answer

Update statistics on the tables.. Updating statistics (A) helps the query optimizer generate more accurate execution plans, potentially avoiding table scans. Creating nonclustered indexes on columns used in WHERE clauses (E) can provide direct access paths, reducing the need for full table scans. Both actions improve performance without increasing costs. Option B (upgrading to Business Critical) increases cost and may not directly address table scans. Option C (increasing MAXDOP) can cause parallelism issues and is not guaranteed to reduce scans. Option D (enabling automatic tuning) is a feature that can suggest index and plan changes but is not as direct as updating statistics or creating indexes.

Answer

Upgrade to Business Critical tier.

Answer

Increase MAXDOP to 8.

Answer

Enable automatic tuning.

Question 11

You are monitoring an Azure SQL Database. You need to identify which built-in tools can provide real-time performance data without additional cost. Which THREE should you select?

Accepted Answer

Azure Monitor Metrics. Azure Monitor Metrics is a built-in, no-cost feature that collects and stores platform metrics from Azure SQL Database at near-real-time intervals (typically every minute). It provides performance counters such as DTU/CPU usage, data IO, and log write percentages without requiring additional configuration or licensing, making it a correct choice for real-time performance data.

Answer

Performance Insights

Answer

SQL Server Profiler

Question 12

You are configuring performance optimization for an Azure SQL Database that uses the Hyperscale service tier. The database has heavy read-write workloads. Which THREE actions should you take to optimize performance?

Accepted Answer

Create nonclustered columnstore indexes on large fact tables.. Options A, C, and D are correct. Hyperscale benefits from nonclustered indexes on read-intensive tables, accelerated database recovery reduces log IO, and read-scale replicas distribute read load. Option B is wrong because Hyperscale does not support resource governor. Option E is wrong because increasing service tier is not an optimization action.

Answer

Configure resource governor to limit CPU usage.

Answer

Increase the service tier to Business Critical.

Question 13

You are managing an Azure SQL Database that is used by a real-time analytics application. The database uses the Hyperscale service tier. You notice that the transaction log rate is consistently high, causing performance degradation. You need to reduce the log generation rate without compromising data durability. What should you do?

Accepted Answer

Increase the service tier to Business Critical.. Increasing the service tier to Business Critical does not reduce the transaction log generation rate; it only improves log write throughput. The log generation rate is workload‑dependent and is not changed by moving to a higher tier. None of the provided options achieve the goal of reducing the log generation rate while maintaining durability. To reduce the rate, you would need to optimize the application (e.g., batching, reducing transactions), which is not offered among the choices.

Answer

Enable compression on transaction log backups.

Answer

Create additional nonclustered indexes on frequently updated tables.

Answer

Increase the backup retention period.

Question 14

You are a database administrator for a company that uses Azure SQL Database. You need to configure a diagnostic setting to send database metrics to a Log Analytics workspace for long-term analysis. The solution should be cost-effective and include metrics like CPU percentage, data IO, and log IO. What should you do?

Accepted Answer

In the Azure portal, add a diagnostic setting for the database to stream 'AllMetrics' to a Log Analytics workspace.. Diagnostic settings in the Azure portal allow streaming of platform metrics (including 'AllMetrics' for CPU percentage, data IO, log IO) to a Log Analytics workspace cost-effectively. Option A is incorrect because Azure SQL Insights is a paid, advanced monitoring solution that is not necessary for basic metric streaming. Option B is incorrect because Query Store captures query performance data, not resource metrics, and cannot export directly to Log Analytics. Option D is incorrect because manually inserting from sys.dm_db_resource_stats via a T-SQL job is inefficient, not built-in, and lacks the integration of diagnostic settings.

Answer

Enable Azure SQL Insights (preview) for the database.

Answer

Enable Query Store and configure it to export to Log Analytics.

Answer

Create a T-SQL job that periodically inserts sys.dm_db_resource_stats into a table in Log Analytics.

Question 15

Refer to the exhibit. An Azure SQL Database is experiencing performance degradation. Based on the Extended Events and wait statistics, which is the most likely root cause?

Accepted Answer

I/O subsystem bottleneck. The exhibit shows PAGEIOLATCH_SH and WRITELOG waits dominating the wait statistics, which are classic indicators of I/O subsystem bottlenecks. PAGEIOLATCH_SH waits occur when a session is waiting for a data page to be read from disk into the buffer pool, while WRITELOG waits indicate delays in writing to the transaction log. These waits are not caused by CPU or memory pressure, but by slow disk I/O, making option C the correct root cause.

Answer

Blocking due to lock contention

Answer

CPU pressure from high-complexity queries

Answer

Insufficient memory allocation for the database