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

Question 1

You are administering an Azure SQL Managed Instance that hosts a busy OLTP database. Users report that during peak hours, queries that typically run in milliseconds now take seconds. You suspect that the issue is related to tempdb contention. Which action should you take to resolve the tempdb contention?

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

Accepted Answer

Increase the number of tempdb data files.. Tempdb contention in Azure SQL Managed Instance is commonly caused by allocation bottlenecks when many sessions create temporary objects. Adding more tempdb data files spreads the allocation metadata across multiple files, reducing contention. Other actions like moving to faster storage or changing isolation levels do not address the root cause of allocation contention.

Answer

Move tempdb to a faster storage tier.

Answer

Set the database compatibility level to the latest version.

Answer

Enable Read Committed Snapshot Isolation (RCSI).

Question 2

You administer an Azure SQL Managed Instance that hosts a mission-critical OLTP database. The instance has 16 vCores and uses the Business Critical service tier. Users report periodic stalls during index maintenance. You observe high PAGEIOLATCH_SH waits and want to reduce their impact without changing the service tier. What should you do first?

Accepted Answer

Reduce the frequency or scope of index maintenance operations.. PAGEIOLATCH_SH waits indicate that sessions are waiting for data pages to be read from storage. Aggressive or overly frequent index maintenance forces large scans that flush the buffer pool and generate heavy physical reads. Reducing the frequency or scope of index rebuilds and reorganizations decreases this I/O pressure, directly mitigating the waits without altering the service tier or making invasive schema changes.

Answer

Change the database to the General Purpose service tier.

Answer

Enable In-Memory OLTP for the tables involved in index maintenance.

Answer

Increase the max server memory setting for the instance.

Question 3

You manage an Azure SQL Database that is part of a business-critical application. You need to configure an alert that triggers when the database's CPU usage exceeds 80% for 10 minutes. The alert must notify an operations team via email. You want to minimize administrative effort. What should you do?

Accepted Answer

Create an alert rule in Azure Monitor with a metric signal for CPU percentage and an action group that sends email.. Azure Monitor is the native monitoring solution for Azure SQL Database. It allows you to create metric alerts on CPU percentage with a threshold and time window, and action groups can send emails. This requires no custom code and is the least administrative effort. SQL Agent is unavailable, automatic tuning is not for alerting, and Query Store lacks alerting features.

Answer

Enable automatic tuning and configure the FORCE_LAST_GOOD_PLAN option.

Answer

Create a SQL Agent job that queries sys.dm_db_resource_stats and sends an email if CPU exceeds 80%.

Answer

Use Query Store to create a custom alert when CPU usage exceeds 80%.

Question 4

You administer an Azure SQL Database that hosts an order-entry application. The database is in the General Purpose tier and uses the default configuration. Users complain that some inserts and updates occasionally wait for several seconds. You observe that the database's log write throughput is frequently near its tier limit, and that many small transactions are committed one row at a time. You need to reduce log write pressure without changing the service tier. What should you do?

Accepted Answer

Batch multiple row modifications into a single transaction to reduce the number of log flush operations.. Log write throughput is consumed per committed transaction because each commit forces a log flush. Many small single-row commits generate far more flushes than a few batched transactions containing the same rows. Batching reduces the number of flushes and lowers log write pressure, which addresses the bottleneck without changing the service tier.

Answer

Enable read committed snapshot isolation so that readers do not block writers.

Answer

Increase the database's max size to allow the transaction log to grow larger before truncation.

Answer

Change the database to the Business Critical tier so that log writes use local SSD.

Question 5

You are tuning an Azure SQL Database that uses the General Purpose service tier. The database experiences performance issues during peak hours, and you notice a high number of PAGEIOLATCH_SH waits. You need to reduce these waits. What should you do?

Accepted Answer

Switch to the Business Critical service tier.. PAGEIOLATCH_SH waits indicate that queries are waiting for data pages to be read from disk. In the General Purpose tier, storage is remote and can introduce latency. The Business Critical tier uses local SSD, which significantly reduces I/O latency and thus these waits. Other actions like enabling Query Store or increasing max size do not address the underlying I/O bottleneck.

Answer

Enable Query Store.

Answer

Increase the database's max size.

Answer

Scale up the database to a higher compute size.

Question 6

You manage an Azure SQL Database that runs an online transaction processing (OLTP) workload. Users report that transactions are slow during business hours. You query sys.dm_os_wait_stats and notice a high number of PAGEIOLATCH_SH waits. You need to reduce these waits without changing the application. What should you do?

Accepted Answer

Increase the database's service tier to add more memory and IOPS.. PAGEIOLATCH_SH waits occur when SQL Server waits for a data page to be read from disk into the buffer pool. The most direct way to reduce these waits without changing the application is to increase the service tier, which provides more memory for the buffer pool and higher storage IOPS. This reduces the need to read pages from disk and speeds up the reads that do occur.

Answer

Configure Query Store to capture wait statistics for the database.

Answer

Enable Accelerated Database Recovery (ADR) on the database.

Answer

Enable Read Scale-Out and redirect read-only queries to the secondary replica.

Question 7

You manage an Azure SQL Database that runs a reporting workload. Users report that month-end reports are slow. You query sys.dm_db_resource_stats and observe that the average log write percentage is consistently high, but CPU and data IO are low. You need to reduce the impact of log write throughput on the workload. What should you do first?

Accepted Answer

Scale up the database to a higher service tier or compute size.. The sys.dm_db_resource_stats DMV shows resource usage as a percentage of the limit for the current service tier and compute size. A consistently high log write percentage with low CPU and data IO indicates the workload is constrained by the log write throughput limit. Scaling up to a higher service tier or compute size increases that limit, directly alleviating the bottleneck. Other actions like increasing max size or read scale-out do not affect log write throughput.

Answer

Enable read scale-out and redirect reporting queries to the secondary replica.

Answer

Change the database to the Hyperscale service tier.

Answer

Increase the database's max size.

Question 8

You are responsible for an Azure SQL Database that hosts a reporting workload. The database runs a large number of ad-hoc queries that consume significant CPU. You need to identify the top CPU-consuming queries to optimize them. Which feature should you use?

Accepted Answer

Query Store. Query Store is designed to capture and persist query performance data, including CPU time. It provides built-in reports to identify top resource consumers, making it the best choice for finding high-CPU queries. Other features like auditing or automatic tuning do not offer the same level of detailed query-level CPU analysis.

Answer

Automatic tuning

Answer

Azure SQL Auditing

Answer

Dynamic management views (DMVs)

Question 9

You manage an Azure SQL Database that experiences high PAGELATCH_EX waits on tempdb during peak transaction processing. You need to reduce these waits. Which two actions should you perform? (Choose two.)

Accepted Answer

Use memory-optimized tempdb metadata.. PAGELATCH_EX waits on tempdb indicate contention on allocation pages during concurrent temp object creation. Adding more tempdb data files spreads the allocation load, and enabling memory-optimized tempdb metadata removes latch contention on metadata pages. These two actions directly target the cause. Other options like ADR or scaling tiers do not address the allocation bottleneck.

Answer

Enable Accelerated Database Recovery (ADR).

Answer

Switch to the Business Critical service tier.

Answer

Increase the database's max size.

Question 10

You are configuring monitoring for an Azure SQL Database that uses the vCore purchasing model. The database is in the General Purpose service tier. You need to receive an alert when the database's CPU consumption exceeds 90 percent for 10 minutes. What should you create?

Accepted Answer

A metric alert rule in Azure Monitor on the CPU percent metric.. Azure Monitor metric alerts are the correct mechanism for alerting on a specific performance metric such as CPU percent. They allow you to set a threshold, an aggregation window, and an evaluation frequency. For a requirement to alert when CPU exceeds 90 percent for 10 minutes, a metric alert rule on the CPU percent metric with a 10-minute aggregation window is the direct and supported solution.

Answer

A smart detection alert in Application Insights.

Answer

An activity log alert rule on the database's administrative operations.

Answer

A log search alert rule based on the AzureDiagnostics table.

Question 11

You are tuning an Azure SQL Database that uses the General Purpose service tier. You notice that a specific query has a high average CPU time but a low average elapsed time. Query Store shows that the query plan uses a Hash Match (Aggregate) operator. You need to reduce the CPU consumption of this query. What should you do?

Accepted Answer

Rewrite the query to reduce the number of rows processed before aggregation.. A Hash Match (Aggregate) operator builds a hash table of group values and is CPU-intensive when processing many rows. The most effective way to reduce its CPU cost is to reduce the number of rows that reach the aggregate. This can be done by filtering earlier, pre-aggregating, or using indexed views. While indexes and statistics can influence plan choice, they do not directly reduce the CPU work of the hash aggregate if the row count remains high.

Answer

Update statistics on the involved tables.

Answer

Force a plan that uses a Stream Aggregate operator.

Answer

Create a covering index for the query's join and filter columns.

Question 12

You are monitoring an Azure SQL Database that uses the vCore purchasing model. You need to identify the top resource-consuming queries. You decide to use Query Store. Which two actions should you perform? (Choose two.)

Accepted Answer

Enable Query Store on the database.. To use Query Store for identifying top resource-consuming queries, you must first enable Query Store on the database. Then, you can use the built-in Top Resource Consuming Queries report, which aggregates query statistics and ranks them by resource usage. The other options are either not required or not the intended use of Query Store for this purpose.

Answer

Configure the MAX_STORAGE_SIZE_MB option for Query Store.

Answer

Enable the LEGACY_CARDINALITY_ESTIMATION database scoped configuration.

Answer

Create a custom Extended Events session to capture query metrics.

Question 13

You are responsible for an Azure SQL Managed Instance that hosts a database with a table named Orders. The table has a clustered index on OrderID and a nonclustered index on CustomerID. You notice that a frequently executed query that filters on CustomerID and returns a small number of rows is performing a clustered index scan. You need to improve the query performance. What should you do?

Accepted Answer

Create a covering nonclustered index on CustomerID that includes the columns required by the query.. When a query filters on a nonclustered index key but requires additional columns not in the index, the optimizer may choose a clustered index scan to avoid key lookups. Making the nonclustered index covering by including the required columns allows an index seek that returns all needed data without touching the base table. This is the most direct and reliable way to improve performance for this frequent query.

Answer

Update statistics on the CustomerID column.

Answer

Force the query to use the existing nonclustered index with a query hint.

Answer

Rebuild the nonclustered index on CustomerID.

Question 14

You are monitoring an Azure SQL Database using Azure Monitor metrics. You need to create an alert that fires when the database's CPU usage exceeds 90% for 10 minutes. Which metric should you use?

Accepted Answer

cpu_percent. Azure SQL Database exposes several Azure Monitor metrics, including cpu_percent, which directly measures CPU usage as a percentage. To alert when CPU exceeds 90% for 10 minutes, you create an alert rule on the cpu_percent metric with a threshold of 90 and an aggregation granularity of 10 minutes. Other metrics like dtu_consumption_percent or log_write_percent measure different resources and would not satisfy the specific CPU monitoring requirement.

Answer

dtu_consumption_percent

Answer

physical_data_read_percent

Answer

log_write_percent

Question 15

You are responsible for an Azure SQL Managed Instance that hosts a critical database. You need to configure alerts to notify the operations team when the average CPU usage of the instance exceeds 80% for 10 minutes. You want to use the built-in monitoring capabilities of Azure. What should you create?

Accepted Answer

An Azure Monitor alert rule based on the CPU percentage metric.. Azure Monitor metric alerts are the built-in mechanism for alerting on platform metrics like CPU percentage. You can specify the aggregation (average), the threshold (80%), and the evaluation period (10 minutes). This meets the requirement efficiently. The other options involve custom scripting or log-based approaches that are not necessary for a simple metric threshold alert.

Answer

A SQL Server Agent job that queries sys.dm_os_performance_counters and sends an email.

Answer

A Log Analytics workspace query with a scheduled alert.

Answer

An Azure Automation runbook that runs a T-SQL query and sends a notification.