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

Question 1

You manage an Azure SQL Database with the General Purpose service tier. The database experiences performance degradation during peak hours. You enable automatic tuning and want to ensure that the database automatically corrects plan regressions caused by parameter sniffing. Which automatic tuning option should you enable?

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

Accepted Answer

FORCE LAST GOOD PLAN. Enabling FORCE LAST GOOD PLAN automatic tuning allows Azure SQL Database to automatically detect and mitigate plan regressions caused by parameter sniffing. When a query plan regresses, the feature forces the last known good plan, restoring performance without manual intervention. This is the precise mechanism for the described issue.

Answer

DROP INDEX

Answer

CREATE INDEX

Answer

AUTO_UPDATE_STATISTICS

Question 2

You are responsible for an Azure SQL Database that hosts a reporting application. Users complain that queries are slow during business hours. You run a query against sys.dm_db_resource_stats and see that the average CPU percentage is consistently above 90%, while other metrics are low. You need to identify the queries contributing most to CPU usage. What should you use?

Accepted Answer

Query Store's Top Resource Consuming Queries report.. Query Store's Top Resource Consuming Queries report aggregates query performance data and ranks queries by resource usage, including CPU time. It retains historical data, so even if plans are evicted, the information remains. This makes it the best tool to identify which queries are responsible for sustained high CPU in the database.

Answer

Extended Events session capturing sql_statement_completed events.

Answer

Azure SQL Database Intelligent Insights.

Answer

Dynamic management view sys.dm_exec_query_stats.

Question 3

You are configuring alerts for an Azure SQL Database. You need to create an alert that fires when the database's DTU consumption exceeds 80% for a sustained period. Which Azure Monitor metric should you use?

Accepted Answer

DTU percentage. The DTU percentage metric provides the percentage of DTU consumption against the database's limit. Setting an alert on this metric with a threshold of 80% will notify when DTU usage exceeds that level, directly meeting the requirement. Other metrics like CPU or storage do not capture the full DTU picture.

Answer

Sessions count

Answer

Storage percent

Answer

CPU percent

Question 4

You are troubleshooting a performance issue in an Azure SQL Database. The database is in the Hyperscale service tier. You observe that read queries are slow, and you suspect that a specific query plan is causing excessive physical reads. You want to identify the query and its plan, and then force a better plan if available. Which tool should you use to capture and analyze the plan, and then force a plan?

Accepted Answer

Query Store, using the Top Resource Consuming Queries report and plan forcing.. Query Store is the appropriate tool because it captures query plans and performance data over time, even in the Hyperscale tier. It enables you to identify the query with excessive physical reads, review its plan history, and force a better plan. Other tools either lack historical data or do not support plan forcing.

Answer

Extended Events session with sql_batch_completed and query_post_execution_showplan events.

Answer

Dynamic management views sys.dm_exec_requests and sys.dm_exec_sql_text.

Answer

Azure SQL Database automatic tuning with CREATE_INDEX.

Question 5

You are managing an Azure SQL Database that has automatic tuning enabled. You notice that a recent index creation recommended by automatic tuning has caused a performance regression for some queries. You need to revert the change and prevent automatic tuning from applying similar recommendations in the future. What should you do?

Accepted Answer

Use the automatic tuning option to revert the last change and then disable the CREATE INDEX tuning option.. Automatic tuning in Azure SQL Database allows you to revert individual tuning actions. To address the regression, you should revert the index creation and then disable the CREATE INDEX tuning option to prevent future automatic index creation. Disabling all automatic tuning or using manual workarounds like DDL triggers are less precise and can have unintended side effects.

Answer

Disable automatic tuning on the database.

Answer

Manually drop the index and create a database-level DDL trigger to block index creation.

Answer

Set the automatic tuning option to inherit from the server and disable it at the server level.

Question 6

You are managing an Azure SQL Database that experiences intermittent performance degradation. Query Store shows a significant increase in waits of type RESOURCE_SEMAPHORE. Which action should you take to resolve the issue?

Accepted Answer

Identify and optimize queries with large memory grants. RESOURCE_SEMAPHORE waits are caused by queries waiting for memory grants. The most effective resolution is to identify queries with large memory grants and optimize them to reduce their memory requirements. This directly reduces contention and improves performance, rather than merely adding more resources.

Answer

Increase the database's DTU or vCore limit

Answer

Increase the database's max memory

Answer

Enable Query Store to capture the queries causing the waits

Question 7

You are monitoring an Azure SQL Database using Azure Monitor metrics. You need to configure alerts to notify the operations team when the database is approaching resource limits. Which two metrics should you use to detect potential CPU and I/O bottlenecks? (Choose two.)

Accepted Answer

cpu_percent. To detect CPU and I/O bottlenecks, the cpu_percent and physical_data_read_percent metrics are the most direct indicators. cpu_percent shows CPU utilization, while physical_data_read_percent reflects I/O read pressure. These metrics are available in Azure Monitor and can be used to trigger alerts when thresholds are exceeded, enabling proactive management.

Answer

sessions_percent

Answer

log_write_percent

Answer

workers_percent

Question 8

You are managing an Azure SQL Database that uses the Business Critical service tier. You need to ensure that the database can handle a sudden increase in transaction log write throughput without experiencing log write waits. Which factor should you primarily consider?

Accepted Answer

The number of vCores allocated to the database.. In the Business Critical service tier, the maximum transaction log write throughput is directly proportional to the number of vCores allocated to the database. To handle increased log write throughput, you should scale up the number of vCores. Other factors such as database size, read replicas, and backup storage redundancy do not affect the log write rate limit.

Answer

The configured backup storage redundancy.

Answer

The number of read replicas configured.

Answer

The size of the database in gigabytes.

Question 9

You are responsible for an Azure SQL Database that hosts a financial application. The database is in the General Purpose service tier. You need to ensure that the database automatically scales compute resources based on workload demand without manual intervention. What should you configure?

Accepted Answer

Enable serverless compute tier for the database.. The serverless compute tier in Azure SQL Database automatically scales compute based on workload and can pause during inactivity. It is designed for single databases with intermittent or unpredictable usage. Unlike manual scaling or elastic pools, serverless requires no manual intervention to adjust resources. Automatic tuning optimizes queries but does not scale compute. Therefore, enabling serverless compute tier is the correct choice to achieve automatic scaling without manual intervention.

Answer

Create an elastic pool and add the database to it.

Answer

Enable automatic tuning for the database.

Answer

Configure auto-scaling in the Azure portal for the database.

Question 10

You manage an Azure SQL Database that hosts a reporting workload. Users report that a monthly aggregation query sometimes completes in 2 seconds, but other times takes over 60 seconds, even though the underlying data volume is unchanged. Query Store shows the query has two distinct plans, and the faster plan is not always chosen. You need to force the faster plan for this query. What should you do?

Accepted Answer

Use Query Store to force the specific fast plan for the query.. The scenario describes plan variability for a specific query, and Query Store already shows two plans. The most direct and supported way to stabilize performance is to force the faster plan via Query Store. Automatic tuning may help but is not targeted, plan guides with USE PLAN are unsupported in Azure SQL Database, and changing cardinality estimation is a broad change that does not guarantee the desired plan.

Answer

Create a plan guide using sp_create_plan_guide with the OPTION (USE PLAN) hint.

Answer

Enable automatic tuning with the FORCE_LAST_GOOD_PLAN option.

Answer

Enable the LEGACY_CARDINALITY_ESTIMATION database scoped configuration.

Question 11

You are a database administrator for an Azure SQL Managed Instance that hosts a critical OLTP database. You notice that the instance is experiencing high PAGELATCH_EX waits on tempdb allocation pages. You need to reduce this contention without changing the service tier. What should you do?

Accepted Answer

Add more tempdb data files.. PAGELATCH_EX waits on tempdb allocation pages are caused by multiple concurrent sessions trying to allocate space on the same pages. Adding more tempdb data files spreads the allocation across multiple files, reducing contention. This is a well-known tuning technique for tempdb. Other options do not address the root cause: ADR affects recovery, Resource Governor limits resources, and faster storage addresses I/O waits, not latch contention. Therefore, adding more tempdb data files is the correct solution.

Answer

Configure Resource Governor to limit tempdb usage.

Answer

Enable Accelerated Database Recovery (ADR) on the instance.

Answer

Move the tempdb files to a faster storage tier.

Question 12

You are optimizing an Azure SQL Database that uses the General Purpose service tier. The database has a high volume of small transactions and you observe wait statistics showing significant WRITELOG waits. You need to reduce WRITELOG waits for this database. What should you do?

Accepted Answer

Change the service tier to Business Critical.. WRITELOG waits indicate the transaction log is a bottleneck. In the General Purpose tier, the log resides on remote Azure storage, which has higher latency than local SSD. Moving to Business Critical provides local SSD for the log, reducing latency and increasing throughput, which directly mitigates WRITELOG waits. The other options do not address the root cause of log write latency.

Answer

Configure the database to use In-Memory OLTP.

Answer

Increase the database max size.

Answer

Enable Accelerated Database Recovery (ADR).

Question 13

You are managing an Azure SQL Database that uses the vCore purchasing model. You need to configure an alert that fires when the database's CPU usage exceeds 80% for 10 minutes. You want to minimize administrative effort. What should you do?

Accepted Answer

Create an alert rule in Azure Monitor based on the cpu_percent metric.. Azure Monitor metric alerts can directly monitor the cpu_percent metric for Azure SQL Database. You can set a threshold of 80% over a 10-minute aggregation window. This requires minimal configuration and no custom code. Other options either do not support the requirement (automatic tuning), are not available (SQL Agent), or involve more effort (Log Analytics). Therefore, creating an Azure Monitor alert rule is the correct and most efficient solution.

Answer

Configure a diagnostic setting to stream logs to Log Analytics and create a log alert.

Answer

Enable automatic tuning and configure it to send email notifications.

Answer

Create a SQL Agent job that checks sys.dm_db_resource_stats and sends an email.

Question 14

You manage an Azure SQL Database that runs a reporting workload. Users report that a complex stored procedure occasionally returns results in under 5 seconds but sometimes takes over 60 seconds. You have enabled Query Store with the default settings. You need to identify the plan that is causing the slow executions and force the faster plan. Which Query Store report should you use?

Accepted Answer

Queries With High Variation. The stored procedure alternates between fast and slow executions, which indicates multiple execution plans with varying performance. The Queries With High Variation report in Query Store is designed to surface exactly this pattern. It allows you to compare plans and force the efficient plan, stabilizing performance. The other reports focus on resource consumption, waits, or regressions, but not on plan-to-plan variability.

Answer

Top Resource Consuming Queries

Answer

Regressed Queries

Answer

Query Wait Statistics

Question 15

You support an Azure SQL Database that uses the General Purpose service tier. A nightly ETL process writes millions of rows, and during the load the database occasionally reports error 40501, 'The service is currently busy.' You need to reduce the chance that the ETL job is throttled while keeping the same service tier. What should you do?

Accepted Answer

Implement retry logic with exponential backoff in the ETL application to handle transient throttling.. Resource governance throttling in Azure SQL Database is transient by design; the recommended handling is to retry with backoff rather than change storage or recovery settings. Because the constraint is to remain on General Purpose, the ETL must tolerate brief throttling. Retry logic directly addresses the transient nature of error 40501.

Answer

Increase the database's max size so that the log file can grow without hitting the storage limit.

Answer

Enable Accelerated Database Recovery on the database to reduce log write volume during the bulk load.

Answer

Move the database to the Hyperscale service tier, which removes all resource governance limits.