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

Question 1

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. Creating appropriate indexes, such as a nonclustered index on columns used in WHERE clauses, can reduce logical reads by enabling index seeks instead of full table scans. Updating statistics helps the query optimizer choose efficient execution plans, potentially reducing logical reads. Adding query hints may force an index but does not guarantee reduced reads and can be counterproductive. Rewriting as a stored procedure does not directly reduce logical reads. Increasing database max memory does not reduce logical reads.

Answer

Add query hints to force index usage

Answer

Rewrite the query as a stored procedure

Answer

Increase the database max memory setting

Question 2

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. PAGELATCH_EX waits are caused by contention on the last page of an index, often due to sequential inserts. Two effective measures in Azure SQL Database are partitioning the table (option C) to spread inserts across multiple pages and using OPTIMIZE_FOR_SEQUENTIAL_KEY index option (option D) to reduce page latch contention. Option A is incorrect because hash or round-robin distribution is a feature of Azure Synapse dedicated SQL pools, not Azure SQL Database. Option B is incorrect because increasing MAXDOP can increase parallelism and potentially worsen latch contention. Option E is incorrect because snapshot isolation does not reduce page latch waits.

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 3

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 4

You are reviewing the configuration of an Azure SQL Database. The database is currently 250 GB in size. You are concerned about storage capacity. What is the maximum size limit for this database?

Accepted Answer

250 GB. The database is currently 250 GB in size, which matches its maximum size limit. Therefore, the maximum size limit is 250 GB (Option C). Option A (1 TB) is incorrect because the limit is not that high. Option B (500 GB) is incorrect as it exceeds the actual limit. Option D (4 TB) is also incorrect for the same reason.

Answer

1 TB

Answer

500 GB

Answer

4 TB

Question 5

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 6

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 7

You are a database administrator for a large e-commerce platform using Azure SQL Database. The application experiences intermittent performance degradation during peak hours. Query Store data shows that a specific stored procedure, `usp_getProducts`, has multiple query variants with different execution plans. Some plans are suboptimal. You need to ensure consistent and optimal performance for this stored procedure without rewriting the application code. What should you do?

Accepted Answer

Enable forced parameterization on the database to promote plan reuse.. Enabling forced parameterization causes the database to treat literal values in queries as parameters, which promotes plan reuse and reduces plan cache bloat. This is ideal for stored procedures like usp_getProducts that have multiple query variants with different plans. Option B is incorrect because plan guides target specific queries, not the entire stored procedure, and are complex to manage. Option C is incorrect because Query Store hints are used to force a specific plan for a query, but they require manual intervention and don't address the root cause of plan variability. Option D is incorrect because automatic tuning with 'FORCE LAST GOOD PLAN' is reactive and may not provide consistent performance for a procedure with many variants.

Answer

Create a plan guide to force a specific execution plan for the stored procedure.

Answer

Implement Query Store hints to force the last known good plan for the stored procedure.

Answer

Enable automatic tuning in Azure SQL Database and rely on the 'FORCE LAST GOOD PLAN' feature.

Question 8

Your organization runs a critical application on Azure SQL Managed Instance. You notice that the `tempdb` database is experiencing contention, leading to PAGELATCH_EX waits. The managed instance has 16 vCores and is using the General Purpose service tier. You need to reduce `tempdb` contention without altering the application code or changing the service tier. What should you do?

Accepted Answer

Add 16 tempdb data files of equal size, matching the number of vCores.. Adding tempdb data files equal to the number of vCores (16) is the recommended best practice to reduce allocation contention. With 16 vCores, 16 files minimize PAGELATCH_EX waits. Option A is incorrect because memory-optimized tempdb metadata helps only with system metadata contention, not data page allocation. Option B is incorrect because adding only 4 files is insufficient; the recommended number is equal to the number of vCores. Option D is incorrect because accelerated database recovery does not address tempdb contention.

Answer

Enable memory-optimized tempdb metadata to reduce allocation contention.

Answer

Add 4 additional tempdb data files of equal size.

Answer

Enable accelerated database recovery to reduce transaction log overhead.

Question 9

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 can automatically implement index recommendations when 'CREATE INDEX' is enabled. Option A is incorrect because 'FORCE LAST GOOD PLAN' is for plan correction, not index creation. Option B is incorrect because Query Store is a monitoring tool, not an automatic tuning feature. Option C is incorrect because Azure Advisor only provides recommendations, not automatic implementation.

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 10

Your company uses Azure SQL Database with Active Geo-Replication for disaster recovery. During a routine failover drill, you observe that after failover to the secondary region, the application experiences significantly higher latency for write operations. The secondary database is in a different Azure region and has the same service objective. What is the most likely cause of the increased write latency?

Accepted Answer

The secondary database does not have a local read-scale replica configured.. After failover to the secondary region, the new primary (formerly the secondary) is in a different Azure region. The secondary database did not have a local read-scale replica configured before failover, so the new primary lacks a read-scale replica to offload read traffic. This can cause resource contention and increased latency for write operations, as the database handles both reads and writes without dedicated replicas. Option A is incorrect because geo-replication does not inherently add latency to all writes—only replicating writes asynchronously may cause slight delay, but not significant latency. Option B is incorrect because the service objective is stated to be the same. Option C is incorrect because after failover, the secondary becomes writable and accepts write traffic.

Answer

Geo-replication introduces additional latency for all write operations.

Answer

The secondary database has a lower service objective than the primary.

Answer

The secondary database is not configured to accept write traffic.

Question 11

You are configuring workload management for an Azure SQL Database using the JSON exhibit above for a classifier named 'MyWorkloadClassifier'. The classifier is intended to assign high importance to queries from user 'User1' in the 'SalesDB' database. However, after deployment, you notice that queries from 'User1' are not getting the expected resource guarantees. What is the most likely reason?

Accepted Answer

The 'memberName' in the context is not correctly formatted; it should be a single user or group name without a backslash.. The most likely reason is that the classifier's context is incorrectly formatted. In Azure SQL Database workload management, the 'memberName' property within the classifier context must be specified as a single user or group name without a backslash. If the JSON exhibit contains a backslash (e.g., 'SalesDB\User1' instead of 'User1'), the classifier fails to match the user. Option A is incorrect because 'importance' is a string ('high'), not an integer. Option C is incorrect because 'min_percentile_resource' is not the issue; resource guarantees depend on the workload group's allocation. Option D is incorrect because a classifier does not need to be associated with a workload group directly; it is defined at the database level.

Answer

The 'importance' property is set to 'high' but the classifier requires 'importance' to be an integer.

Answer

The 'min_percentile_resource' value is too low to guarantee resources.

Answer

The classifier is not associated with a workload group.

Question 12

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 13

Your organization uses Azure SQL Database with elastic pools. One of the databases in the pool, 'OrdersDB', consistently uses more eDTUs than other databases, causing performance degradation for other databases. You need to isolate the resource usage of 'OrdersDB' to prevent it from affecting others. The solution should minimize cost and administrative overhead. What should you do?

Accepted Answer

Move 'OrdersDB' to a higher service tier, such as a standalone database with a higher DTU limit.. Moving 'OrdersDB' to a standalone database with a higher DTU limit isolates its resource usage, preventing it from affecting other databases in the elastic pool. This approach minimizes administrative overhead as it's a straightforward configuration change. Option A is incorrect because purchasing reserved capacity is a billing benefit, not a resource isolation mechanism. Option B is incorrect because Query Store can identify high-usage queries but cannot limit resource consumption. Option D is incorrect because increasing the pool's eDTU limit would increase costs, and the high-usage database could still consume resources and affect others within the pool.

Answer

Purchase reserved capacity for the pool to reduce costs.

Answer

Use Query Store to identify and kill the high-usage queries.

Answer

Increase the eDTU limit of the elastic pool to accommodate the high usage.

Question 14

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 with EXECUTE permission on the 'Sales' schema grants execute on all stored procedures in that schema, adhering to least privilege. Option A is wrong because granting SELECT on the schema would give read access to underlying tables, which is not allowed. Option B is wrong because adding AppUser to the db_datareader role provides read access to all tables in the database, exceeding the required permissions. Option D is not wrong in terms of functionality but is more administrative work compared to using a schema-level grant; however, for the requirement of least privilege, granting EXECUTE on each stored procedure individually would still work but is less efficient than the schema-level 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'.

Question 15

You manage an Azure SQL Database that is part of an Always On availability group in Azure SQL Managed Instance. You notice that the secondary replica is experiencing high log send queue size. The primary replica has ample CPU and I/O resources. The network latency between replicas is low. What is the most likely cause of the high log send queue?

Accepted Answer

The secondary replica's log apply rate is slower than the log generation rate on the primary.. The most likely cause of high log send queue is that the secondary replica's log apply rate is slower than the log generation rate on the primary (Option C). This typically occurs when the secondary replica is under-provisioned or has high I/O contention, causing it to fall behind in applying received log records. Option A (asynchronous commit) is incorrect because asynchronous commit can actually reduce log send queue pressure by not waiting for the secondary, but it does not directly cause queue buildup. Option B (log flush delays on primary) is unlikely because the primary has ample CPU and I/O resources. Option D (insufficient network bandwidth) is unlikely given that network latency is low and the primary resources are ample.

Answer

The secondary replica is configured with asynchronous commit.

Answer

The primary replica is experiencing log flush delays.

Answer

The network bandwidth between replicas is insufficient.