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

Question 1

You manage an Azure SQL Database that is critical for a financial application. The database has a read-heavy workload, and you need to monitor and diagnose performance issues. You want to enable a feature that automatically captures detailed information about query plans and runtime statistics for later analysis. Which feature should you enable?

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

Accepted Answer

Query Store. Query Store is the built-in feature that automatically captures query plans and runtime statistics, storing them in the database for historical analysis. It is designed for performance troubleshooting and is a prerequisite for automatic tuning. For a read-heavy workload, it provides the necessary insights to identify and resolve performance regressions.

Answer

Extended Events

Answer

Automatic tuning

Answer

Dynamic management views (DMVs)

Question 2

You are configuring alerts for an Azure SQL Database. You need to be notified when the database reaches 90% of its allocated storage. Which Azure Monitor alert signal should you use?

Accepted Answer

storage_percent. Azure SQL Database exposes the storage_percent metric, which directly reports the percentage of allocated storage used. Setting an alert on this metric at 90% will notify you when storage utilization reaches the threshold. Other metrics like storage (absolute size) or dtu_consumption_percent (compute) do not provide the required percentage of storage usage.

Answer

storage

Answer

physical_data_read_percent

Answer

dtu_consumption_percent

Question 3

You manage an Azure SQL Database that supports a reporting workload. Users report that a complex aggregation query returns different elapsed times throughout the day, but the logical reads remain consistent. You need to determine whether the query is experiencing CPU pressure or waiting on resources. Which Query Store view should you use to analyze wait statistics for the query?

Accepted Answer

sys.query_store_wait_stats. To analyze wait statistics for a specific query in Query Store, you must use the sys.query_store_wait_stats view. It aggregates wait times by query and plan, allowing you to see the wait categories and durations. This directly addresses the need to differentiate between CPU pressure and resource waits. The other views provide runtime stats, plan details, or query text, but none include wait information.

Answer

sys.query_store_plan

Answer

sys.query_store_runtime_stats

Answer

sys.query_store_query_text

Question 4

You manage an Azure SQL Database that runs a reporting workload. Users report that queries are slow only when they filter on a specific customer region, and the slowness began after a large data load. You run Query Store and identify a plan that regressed. You want the database engine to automatically detect and revert to the last known good plan for that query. What should you configure?

Accepted Answer

Enable automatic tuning with the FORCE_LAST_GOOD_PLAN option.. Automatic tuning with FORCE_LAST_GOOD_PLAN is the correct choice because it directly addresses plan regression by automatically reverting to a previously good plan when performance degrades. The other options either collect data without acting, create indexes, or change compatibility levels, none of which provide the automatic regression correction required.

Answer

Enable automatic tuning with the CREATE_INDEX option.

Answer

Set the database compatibility level to the latest version.

Answer

Configure Query Store to use AUTO plan capture mode.

Question 5

You are optimizing an Azure SQL Database that uses the Hyperscale service tier. You need to reduce the time it takes to perform a database restore. Which TWO factors directly affect the restore time? (Choose two.)

Accepted Answer

The number of transaction log records that need to be replayed.. In Hyperscale, restore performance is primarily determined by the amount of transaction log that must be replayed and the number of page servers that need to be attached. Data file size and backup redundancy do not directly affect restore time, and the SLO influences resources but not the fundamental restore process.

Answer

The backup storage redundancy option.

Answer

The service level objective (SLO) of the database.

Answer

The size of the database's data files.

Question 6

You are managing an Azure SQL Database that supports a reporting application. Users report that queries are slow during business hours. You suspect that the database is experiencing CPU pressure. Which metric should you monitor to confirm this?

Accepted Answer

CPU percentage. CPU percentage is the direct metric for CPU utilization in Azure SQL Database. Unlike DTU percentage, which aggregates multiple resources, CPU percentage isolates processor usage. When CPU percentage is consistently high, it confirms CPU pressure as the cause of slow queries. The other metrics measure I/O or log throughput, which are unrelated to CPU bottlenecks.

Answer

DTU percentage

Answer

Log write percentage

Answer

Data IO percentage

Question 7

You are tuning an Azure SQL Database that uses the General Purpose service tier. The database experiences high transaction log write waits during peak hours, and you observe that the log rate is frequently near its limit. You need to increase the maximum log rate for the database. What should you do?

Accepted Answer

Change the database to the Business Critical service tier.. The maximum log rate in Azure SQL Database is determined by the service tier and hardware generation. General Purpose has a lower log rate limit than Business Critical, which uses local SSD and offers higher throughput. To increase the log rate, you must move to Business Critical. Scaling compute within General Purpose or increasing storage does not raise the log rate limit. ADR improves recovery but not log throughput.

Answer

Enable accelerated database recovery (ADR) on the database.

Answer

Scale up the database to a higher compute size within the General Purpose tier.

Answer

Increase the maximum storage size of the database.

Question 8

You manage an Azure SQL Database that runs a reporting workload. Users report that queries against a large fact table sometimes take much longer than usual, and you suspect that a plan regression occurred after a recent statistics update. You need to identify which queries have a plan that changed and performed worse, without deploying any external monitoring tools. What should you use?

Accepted Answer

Query Store's Regressed Queries view. Query Store persists query text, plans, and runtime statistics inside the database and includes a Regressed Queries view that compares plan performance across time windows. When a statistics update causes a plan change that degrades a reporting query, that view surfaces the affected queries with their historical plans and metrics, letting you force a known-good plan or take other corrective action without external monitoring.

Answer

SQL Server Profiler traces collected to a file

Answer

Azure SQL Database automatic tuning's CREATE INDEX recommendations

Answer

sys.dm_db_index_physical_stats

Question 9

You manage an Azure SQL Managed Instance that hosts a database with a high volume of transactions. You notice that the transaction log is growing rapidly and is not being truncated. You need to identify the cause and resolve the issue. What should you do?

Accepted Answer

Identify and resolve long-running transactions or replication delays.. The correct action is to identify and resolve long-running transactions or replication delays. In the Full recovery model, log truncation is blocked by active transactions or replication. Resolving these allows the log to truncate, reclaiming space. Other options either compromise recovery (Simple model) or treat symptoms (shrink, increase size). Addressing the root cause is essential for a healthy transaction log.

Answer

Increase the maximum size of the transaction log file.

Answer

Change the database to the Simple recovery model.

Answer

Shrink the transaction log file to reclaim space.

Question 10

You are optimizing an Azure SQL Database that uses the vCore purchasing model. The database is experiencing high RESOURCE_SEMAPHORE waits. You need to identify two actions that can reduce these waits. (Choose two.)

Accepted Answer

Scale up to a higher service tier or compute size.. RESOURCE_SEMAPHORE waits indicate insufficient query workspace memory. Scaling up the compute size provides more memory for grants. Optimizing queries reduces the memory needed per query. Together, these actions lower the demand and increase the supply of memory grants, reducing waits. The other options either do not affect workspace memory or could worsen the situation.

Answer

Increase the memory-optimized filegroup size.

Answer

Enable read-scale out.

Answer

Increase the maximum degree of parallelism (MAXDOP).

Question 11

You are responsible for an Azure SQL Database that hosts a mission-critical application. You need to configure an alert that fires when the database's CPU usage exceeds 90% for more than 10 minutes. You want to use the built-in Azure Monitor metrics for Azure SQL Database. Which metric should you use?

Accepted Answer

cpu_percent. The cpu_percent metric directly measures the percentage of CPU utilized by the Azure SQL Database. It is the appropriate metric to use for an alert on CPU usage exceeding a threshold. Other metrics like dtu_consumption_percent, physical_data_read_percent, and log_write_percent measure different resources and would not accurately reflect CPU usage. Using cpu_percent ensures the alert fires only when CPU is the bottleneck.

Answer

dtu_consumption_percent

Answer

log_write_percent

Answer

physical_data_read_percent

Question 12

You are responsible for an Azure SQL Database that supports an order-processing application. The database is configured with the General Purpose service tier. During month-end processing, the application experiences slow response times. You need to determine whether the performance issue is caused by the database reaching its resource limits. Which metric should you monitor in Azure Monitor?

Accepted Answer

CPU percentage. In the vCore purchasing model, CPU percentage is the primary metric that indicates whether the database is approaching its compute limit. During month-end batch processing, sustained high CPU percentage points to insufficient vCores or inefficient queries, guiding you to scale up or optimize. DTU percentage is only relevant for DTU-based tiers, and other metrics do not directly show overall resource saturation.

Answer

Sessions count

Answer

Log write wait time

Answer

DTU percentage

Question 13

You are monitoring an Azure SQL Database that uses the vCore purchasing model. You need to set up alerts to notify you when the database approaches its resource limits. Which two metrics should you alert on to detect CPU and I/O pressure? (Choose two.)

Accepted Answer

physical_data_read_percent. The correct metrics are cpu_percent and physical_data_read_percent. cpu_percent directly measures CPU utilization, and physical_data_read_percent measures the percentage of physical data reads, indicating I/O pressure. These are core metrics in the vCore model for monitoring resource consumption. Other metrics like log_write_percent, sessions_percent, and workers_percent are less directly related to the overall CPU and I/O pressure.

Answer

log_write_percent

Answer

sessions_percent

Answer

workers_percent

Question 14

You manage an Azure SQL Database that uses the Business Critical service tier. A critical reporting query normally completes in under 5 seconds but occasionally takes over 60 seconds. You observe that during these slow executions, the query uses a different execution plan that performs a large number of physical reads. You need to ensure that the fast plan is used consistently for this query. What should you do?

Accepted Answer

Use Query Store to force the fast execution plan for the query.. Query Store captures all plans generated for a query and provides a mechanism to force a specific plan. When a query occasionally uses a suboptimal plan that causes high physical reads, you can identify the fast plan in Query Store and force it, ensuring consistent performance. This approach is native to Azure SQL Database and allows you to monitor and easily revert if needed.

Answer

Increase the database's compute size to reduce physical reads.

Answer

Enable automatic tuning FORCE_LAST_GOOD_PLAN on the database.

Answer

Create a plan guide using sp_create_plan_guide for the query.

Question 15

You have an Azure SQL Database that is experiencing performance issues. You suspect that a recent deployment introduced a regression in a stored procedure. You need to identify the query plan change and the specific query that is performing poorly. What should you use?

Accepted Answer

Query Store. Query Store is the correct tool because it retains historical query plans and runtime statistics, enabling you to detect plan changes and performance regressions. It can show when a stored procedure's plan changed and how its performance degraded. Other tools either lack historical data (DMVs), are unavailable (Profiler), or lack query-level detail (Azure Monitor metrics).

Answer

Azure Monitor metrics

Answer

SQL Server Profiler

Answer

Dynamic management views (DMVs)