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

Question 1

You have an Azure SQL Database that is part of an elastic pool. You notice that the pool's eDTU consumption is consistently high, and some databases are experiencing resource contention. You need to ensure that a critical database always gets a minimum amount of resources. What should you configure?

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

Accepted Answer

Configure per-database min eDTU for the critical database. In an Azure SQL elastic pool, per-database min eDTU (or min vCore) guarantees a floor of resources that a specific database can always consume, even when other databases in the pool are competing for resources. Setting a min eDTU on the critical database ensures it is never starved during contention. This directly addresses the requirement that the critical database 'always gets a minimum amount of resources.'

Answer

Configure per-database max eDTU for the critical database

Answer

Increase the eDTU of the elastic pool

Answer

Move the critical database to a dedicated service tier

Question 2

You need to configure monitoring for an Azure SQL Database to meet the following requirements: 
- Alert when average DTU consumption exceeds 90% for 10 minutes.
- Track failed logins.
- Analyze query performance over the last 30 days.
Which THREE Azure services or features should you use? (Choose three.)

Accepted Answer

SQL auditing. Azure Monitor metric alerts (D) are the correct mechanism to trigger on a metric threshold such as average DTU consumption exceeding 90% over a 10-minute window, since DTU percentage is exposed as a platform metric and metric alerts support aggregation and time-window conditions. SQL auditing (A) records login events including failed logins to an audit log or Log Analytics, which directly satisfies the requirement to track failed logins. Query Store (C) is the built-in feature that persists query execution statistics and plans, allowing analysis of query performance over a retention period such as the last 30 days. Automatic tuning (B) is a feature that applies index and plan corrections, not a monitoring or alerting tool, so it does not meet any of the stated requirements. Azure SQL Assessment (E) evaluates configuration best practices and migration readiness, not runtime DTU alerting, login tracking, or query performance history.

Answer

Automatic tuning

Answer

Azure SQL Assessment

Question 3

You manage an Azure SQL Database that is experiencing higher than expected DTU consumption. You need to identify which queries are consuming the most resources. Which dynamic management view should you query?

Accepted Answer

Query sys.dm_exec_query_stats. sys.dm_exec_query_stats returns aggregate performance statistics for cached query plans, including total worker time, logical reads, and execution counts. This makes it the correct DMV to identify which queries consume the most resources, as it directly attributes resource usage to individual query statements. By ordering by total_worker_time or total_logical_reads, you can pinpoint the top resource-consuming queries.

Answer

Query sys.dm_exec_requests

Answer

Query sys.dm_os_wait_stats

Answer

Query sys.dm_db_resource_stats

Question 4

You are troubleshooting a performance degradation on an Azure SQL Database. You notice that the database is hitting the maximum DTU limit frequently. Which action should you take first to reduce DTU consumption?

Accepted Answer

Use Query Performance Insight to identify and optimize top resource-consuming queries. The first step to reduce DTU consumption is to identify the queries causing the high resource usage using Query Performance Insight. This tool provides detailed metrics on top resource-consuming queries, enabling targeted optimization. Scaling up or rebuilding indexes without identifying the root cause may not address the underlying inefficiency and could increase costs unnecessarily.

Answer

Increase the log rate limit

Answer

Scale up the database to a higher service tier

Answer

Rebuild all indexes in the database

Question 5

You are reviewing an Azure Resource Manager template snippet for configuring long-term backup retention for an Azure SQL Database. The deployment fails with an error indicating the storage account is not accessible. What is the most likely cause?

Accepted Answer

The SAS token has expired or is invalid. The error indicates that the storage account is not accessible. In the template, a SAS token is used to authenticate access to the storage container for long-term backup retention. The most common reason for this error is that the SAS token has expired or is invalid, as SAS tokens have an expiration date. Therefore, Option C is correct. Option A is incorrect because the server name mismatch would cause a different error. Option B is incorrect because the URI format may be valid, but the token is the issue. Option D is incorrect because cross-region storage is supported for long-term retention.

Answer

The server name in the template does not match the actual server

Answer

The storage container URI is incorrectly formatted

Answer

The database is not in the same region as the storage account

Question 6

You need to configure alerts for an Azure SQL Database to notify the operations team when the database exceeds 80% DTU consumption for more than 10 minutes. What should you use?

Accepted Answer

Create a metric alert in Azure Monitor. Azure Monitor metric alerts evaluate platform metrics such as DTU percentage over a defined time window and fire notifications via action groups. To alert when DTU consumption exceeds 80% for more than 10 minutes, you create a metric alert on the 'DTU percentage' metric with a threshold of 80 and an aggregation granularity/window of 10 minutes. This is the native, supported mechanism for Azure SQL Database resource alerts.

Answer

Configure a SQL Agent alert

Answer

Configure Azure SQL Auditing

Answer

Use Azure Advisor recommendations

Question 7

You have an Azure SQL Database that uses the Hyperscale service tier. You notice that the log rate is frequently throttled. Which configuration change can help reduce log rate throttling?

Accepted Answer

Increase the log rate limit by scaling up the service level objective. In Azure SQL Database Hyperscale, log rate is governed by the service level objective, and when the log generation rate exceeds the limit the workload is throttled. Scaling up to a higher service level objective raises the log rate limit, directly relieving the throttling. This is the documented remedy for log rate throttling in Hyperscale.

Answer

Increase max degree of parallelism

Answer

Reduce backup retention period

Answer

Add more compute replicas

Question 8

You are tuning a query in Azure SQL Database. Which TWO actions can reduce logical reads?

Accepted Answer

Create a nonclustered index on the columns used in WHERE clause. Option B is correct because a nonclustered index on the WHERE-clause columns gives the optimizer a narrow access path, so it can seek directly to matching rows instead of scanning the base table or clustered index, which lowers the number of 8 KB pages read and therefore logical reads. Option E is correct because updating statistics gives the optimizer accurate cardinality estimates, enabling better plan choices such as index seeks and appropriate join strategies, which reduces the pages touched per execution. Option A is not correct because forcing an index with a hint does not by itself reduce logical reads and can even increase them if the optimizer's original plan was better. Option C is not correct because wrapping the query in a stored procedure mainly aids plan reuse and parameterization, not the number of pages read per execution. Option D is not correct because increasing max memory affects buffer pool caching and physical I/O, not the logical read count, which is measured independently of whether pages come from memory or disk.

Answer

Add query hints to force index usage

Answer

Rewrite the query as a stored procedure

Answer

Increase the database max memory setting

Question 9

You are troubleshooting a performance issue on an Azure SQL Database. The database is experiencing high PAGELATCH_EX waits. Which TWO measures can help reduce these waits?

Accepted Answer

Partition the table to distribute inserts. Option C is correct because PAGELATCH_EX waits are commonly caused by last-page insert contention on monotonically increasing keys (e.g., IDENTITY), and partitioning the table across multiple partitions/files spreads those inserts across different pages, eliminating the hot last-page latch. Option D is correct because the OPTIMIZE_FOR_SEQUENTIAL_KEY index option, introduced in SQL Server 2019/Azure SQL Database, specifically mitigates last-page insert PAGELATCH_EX contention by managing the insert into the index's last page more efficiently. Option A is not applicable because hash or round-robin distribution is a dedicated SQL pool (formerly SQL DW) table design concept, not a remedy for PAGELATCH_EX in Azure SQL Database. Option B is wrong because increasing MAXDOP does not reduce page latch contention and can even worsen it by increasing concurrent insert pressure. Option E is wrong because enabling snapshot isolation addresses blocking/locking (LCK waits) and read-write contention, not PAGELATCH_EX waits on data pages.

Answer

Use a hash distribution or round-robin distribution in a table design

Answer

Increase MAXDOP for the queries

Answer

Enable snapshot isolation level

Question 10

Which TWO metrics in Azure SQL Database indicate that the database might need to be scaled up?

Accepted Answer

Log write percent consistently above 90%. High DTU/CPU consumption (E) and high log write percent (C) both indicate that the database is approaching or hitting resource limits, suggesting a need to scale up. Options A, B, and D are incorrect because consistently low metrics (data IO, session percent, memory) indicate underutilization, not a need to scale up.

Answer

Data IO percentage consistently below 20%

Answer

Session percent consistently below 10%

Answer

Memory consumption consistently below 30%

Question 11

You run the query in the exhibit on an Azure SQL Database. The result shows high wait_time_ms for PAGEIOLATCH_SH waits. What does this indicate?

Accepted Answer

I/O subsystem bottleneck for read operations. PAGEIOLATCH_SH waits indicate that a query is waiting for a data page to be read from disk into the buffer pool, which is an I/O operation. High wait_time_ms for this wait type typically points to an I/O subsystem bottleneck for read operations, making option A correct. Option B (CPU bottleneck) is incorrect because PAGEIOLATCH_SH is related to I/O, not CPU. Option C (blocking) is incorrect because blocking is associated with LOCK waits, not PAGEIOLATCH_SH. Option D (memory pressure) is incorrect; while memory pressure can increase physical I/O, the wait type itself specifically indicates I/O latency for reading pages from disk.

Answer

CPU bottleneck

Answer

Blocking between concurrent transactions

Answer

Memory pressure

Question 12

You are analyzing query performance in an Azure SQL Database. The query in the exhibit returns a list of queries ordered by total_logical_reads. What does high total_logical_reads typically indicate?

Accepted Answer

The query is reading many pages from the buffer pool, possibly due to missing indexes. High total_logical_reads typically indicates that the query is reading many pages from the buffer pool, which often points to missing or inefficient indexes. Option D is correct. Option A is incorrect because high logical reads relate to buffer pool access, not necessarily I/O latency (which is indicated by high physical reads). Option B is incorrect because CPU time is measured by worker_time, not logical reads. Option C is incorrect because while logical reads can increase memory usage, the primary indicator of memory usage is the memory grant, not logical reads.

Answer

The query is experiencing I/O latency

Answer

The query is using a lot of CPU time

Answer

The query is using a lot of memory

Question 13

You are monitoring an Azure SQL Database using Intelligent Insights. The built-in intelligence detects a performance issue and suggests a specific index to create. The database is running the Business Critical service tier. You want to automatically implement this recommendation without manual intervention. What should you configure?

Accepted Answer

Enable automatic tuning for 'CREATE INDEX' in the Azure portal.. Azure SQL Database's automatic tuning feature can automatically apply index recommendations generated by the built-in intelligence engine. Enabling the 'CREATE INDEX' automatic tuning option allows Azure to create recommended indexes without manual intervention, directly addressing the requirement. This option is supported on Business Critical tier and integrates with Intelligent Insights and Query Store to validate the recommendation before applying it.

Answer

Enable automatic tuning for 'FORCE LAST GOOD PLAN' in the Azure portal.

Answer

Set up Query Store to capture the recommended index execution.

Answer

Configure Azure Advisor to email you the recommendation.

Question 14

You are monitoring an Azure SQL Database using dynamic management views (DMVs). You run a query against `sys.dm_exec_query_stats` to find the top 10 queries by total worker time. Several queries show high worker time but low logical reads. The database is not experiencing any blocking or deadlocks. What is the most likely cause of the high worker time?

Accepted Answer

The queries are CPU-bound due to inefficient query plans.. High worker time (CPU time) with low logical reads indicates that the queries are CPU-bound rather than I/O-bound. Inefficient query plans, such as those with large hash joins, sorts, or non-sargable predicates, can cause excessive CPU consumption without generating many logical reads. Option A is incorrect because parameter sniffing typically leads to varying plan quality, but the consistent high worker time across multiple queries suggests a systematic plan efficiency issue, not necessarily parameter sniffing. Option B is incorrect because memory pressure would cause increased I/O activity (lazy writes) which is not observed with low logical reads. Option C is incorrect because transaction log writes are I/O operations and would not cause high worker time with low I/O.

Answer

The queries suffer from parameter sniffing leading to suboptimal plans.

Answer

The queries are experiencing memory pressure causing excessive lazy writes.

Answer

The queries are waiting on transaction log writes.

Question 15

You are deploying a new application on Azure SQL Database. The application requires that all connections use a specific login, 'AppUser', with the least privileges necessary. The login should only be able to execute stored procedures in the 'Sales' schema and should not have direct access to underlying tables. What should you do?

Accepted Answer

Create a database role, grant EXECUTE on the 'Sales' schema to the role, and add 'AppUser' to the role.. Creating a database role, granting EXECUTE on the 'Sales' schema to that role, and adding 'AppUser' to the role follows the principle of least privilege. This allows 'AppUser' to execute stored procedures in the Sales schema without granting direct table access, because stored procedures execute with ownership chaining or elevated permissions. This is the most maintainable and secure approach.

Answer

Grant the SELECT permission on the 'Sales' schema to 'AppUser'.

Answer

Add 'AppUser' to the db_datareader role.

Answer

Grant the EXECUTE permission on each stored procedure individually to 'AppUser'.