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

Question 1

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 exhibit shows maxSizeBytes: 268435456000, which is 250 GB. Option D is correct. Option A is wrong because 4 TB is not the limit shown. Option B is wrong because 1 TB is incorrect. Option C is wrong because 500 GB is incorrect.

Answer

1 TB

Answer

500 GB

Answer

4 TB

Question 2

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 I/O latency for reading data pages from disk. Option B is correct. Option A is wrong because PAGEIOLATCH_SH is for I/O, not memory. Option C is wrong because it is not CPU-related. Option D is wrong because it is not blocking.

Answer

CPU bottleneck

Answer

Blocking between concurrent transactions

Answer

Memory pressure

Question 3

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 logical reads often indicate that queries are reading more data than necessary, possibly due to missing indexes or inefficient queries. Option A is correct. Option B is wrong because CPU time is measured by worker_time. Option C is wrong because high logical reads can increase memory usage. Option D is wrong because it does not indicate I/O directly.

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 4

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.. Option C is correct because enabling forced parameterization makes the database treat literal values as parameters, allowing query plans to be reused and reducing plan cache bloat. Option A is wrong because plan guides are for specific queries, not the entire stored procedure. Option B is wrong because it's an after-the-fact analysis tool. Option D is wrong because index tuning is a reactive measure, not a proactive plan stability solution.

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 5

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.. Option D is correct because increasing the number of tempdb data files to match the number of vCores (or slightly higher) reduces allocation contention. Option A is wrong because memory-optimized tempdb metadata is for system metadata, not data pages. Option B is wrong because it only adds more files, but optimal number is vCores. Option C is wrong 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 6

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.. Option A is correct because Azure SQL Database's automatic tuning can automatically implement index recommendations. Option B is wrong because automatic tuning for plan correction is separate. Option C is wrong because it only provides recommendations. Option D is wrong because Query Store is a monitoring tool, not an automatic tuning feature.

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 7

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.. Option B is correct because geo-replicated secondaries are readable and do not accept write traffic until failover; after failover, the new primary has no geo-secondary configured initially, so writes are not replicated asynchronously, but latency is likely due to the lack of a local secondary. Option A is wrong because service objective is same. Option C is wrong because geo-replication delay affects read scalability, not write latency. Option D is wrong because the secondary is readable but not writable before failover.

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 8

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.. Option C is correct because the 'context' should be 'memberName' as a string, but the value has a backslash which is not escaped in JSON; also the classifier name is not referenced in the context correctly. Option A is wrong because importance is correctly set. Option B is wrong because resource percentiles are valid. Option D is wrong because the classifier is not a built-in role.

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 9

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.. Option A is correct because high CPU consumption (worker time) with low I/O indicates CPU-bound queries, often due to inefficient joins, aggregations, or non-sargable predicates. Option B is wrong because low logical reads suggest not memory pressure. Option C is wrong because parameter sniffing would show varied plans. Option D is wrong because high worker time with low I/O is not typical for transaction log writes.

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 10

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.. Option D is correct because moving the database to a higher service tier or a separate pool isolates its resource usage and prevents impact on others. Option A is wrong because scaling the pool affects all databases. Option B is wrong because query store does not limit resources. Option C is wrong because reserved capacity is a billing benefit, not a resource isolation mechanism.

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 11

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.. Option C is correct because creating a role with EXECUTE permission on the schema grants execute on all stored procedures in that schema. Option A is wrong because granting SELECT on schema gives read access to tables. Option B is wrong because granting EXECUTE on individual procedures is more administrative work. Option D is wrong because granting CONTROL is excessive.

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 12

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.. Option A is correct because if the secondary replica is not able to apply log records quickly enough, the log send queue grows. This can happen if the secondary has insufficient resources or is under load. Option B is wrong because direct network latency is low. Option C is wrong because the primary has ample resources. Option D is wrong because log flush on the primary is not the issue.

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.

Question 13

Which TWO actions can you take to optimize query performance in Azure SQL Database without changing the application code? (Choose two.)

Accepted Answer

Create missing indexes identified by the Database Engine Tuning Advisor.. Options A and B are correct. Creating missing indexes and updating statistics are common performance optimization tasks that do not require code changes. Option C requires code changes. Option D is not a direct optimization. Option E is about hardware, but still no code change; however, it's not a typical DBA action for query performance.

Answer

Rewrite queries to use sargable predicates.

Answer

Upgrade to a higher service tier.

Answer

Enable row-level security on sensitive tables.

Question 14

Which THREE factors should you consider when configuring automatic tuning for an Azure SQL Database? (Choose three.)

Accepted Answer

Whether to allow automatic index dropping.. Options A, B, and C are correct. Automatic tuning options include CREATE INDEX, DROP INDEX, and FORCE LAST GOOD PLAN. Option D is not an automatic tuning option; it's a manual feature. Option E is not part of automatic tuning.

Answer

Whether to enable automatic plan guide creation.

Answer

Whether to enable automatic statistics update.

Question 15

Which TWO metrics in Azure SQL Database's Intelligent Insights can indicate a performance degradation due to increased resource consumption? (Choose two.)

Accepted Answer

Increased query duration.. Options A and B are correct. High DTU consumption and increased query duration are direct indicators of performance issues. Option C is a security metric. Option D is a connectivity metric. Option E is an availability metric.

Answer

High number of deadlocks.

Answer

Increased transaction log usage.

Answer

Failed authentication attempts.

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

Exhibit

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

Exhibit