DP-300 Monitor, configure, and optimize database resources Practice Test 6 — 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 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 2

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 3

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 4

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 5

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.. In Azure SQL Database workload management, the classifier's memberName must be a single user or group name without a domain backslash; including a backslash (e.g., 'DOMAIN\User1') causes the classifier to fail to match, so queries from User1 do not receive the intended importance. The correct format is just the username or group name.

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 6

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 consuming eDTUs from the elastic pool and affecting other databases. This minimizes cost and administrative overhead because you only pay for the resources that 'OrdersDB' needs, and you avoid over-provisioning the entire 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 7

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.

Question 8

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.. Option D is correct because Database Engine Tuning Advisor analyzes workload and recommends missing indexes; creating those indexes reduces table/index scans and speeds up query execution without any application code changes. Option E is correct because updating statistics on tables with outdated statistics gives the query optimizer accurate cardinality estimates, which typically produces better execution plans and faster queries, again with no code modification. Option A is incorrect because rewriting queries to use sargable predicates requires changing application code, which the scenario explicitly forbids. Option B is incorrect because upgrading to a higher service tier changes cost and resources but is not a query-performance optimization action in the sense of tuning the database, and it does not address inefficient plans or missing indexes. Option C is incorrect because enabling row-level security adds predicate-based access filtering and can actually add overhead, and it is a security feature rather than a query-performance optimization.

Answer

Rewrite queries to use sargable predicates.

Answer

Upgrade to a higher service tier.

Answer

Enable row-level security on sensitive tables.

Question 9

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.. Option B is correct because Azure SQL Database's automatic tuning includes the DROP INDEX feature, which lets the service automatically drop indexes that are no longer beneficial, and you must decide whether to allow this behavior. Option D is correct because the FORCE LAST GOOD PLAN feature automatically forces the last known good execution plan for queries that regress, and enabling it is a key automatic tuning configuration choice. Option E is correct because CREATE INDEX is one of the core automatic tuning features, automatically creating indexes that could improve query performance, so you must decide whether to allow it. Option A is not a standard automatic tuning option in Azure SQL Database, as plan guides are not automatically created by the automatic tuning feature. Option C is not part of automatic tuning configuration because automatic statistics updates are enabled by default in Azure SQL Database and are not a configurable automatic tuning toggle.

Answer

Whether to enable automatic plan guide creation.

Answer

Whether to enable automatic statistics update.

Question 10

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 D are correct. In Azure SQL Database's Intelligent Insights, increased query duration and high DTU consumption are key metrics that indicate performance degradation due to increased resource consumption. Increased query duration suggests that queries are taking longer, often due to resource contention. High DTU consumption reflects high usage of CPU, memory, and I/O, which can degrade performance. Option B (high number of deadlocks) indicates concurrency issues but does not directly measure resource consumption. Option C (increased transaction log usage) is related to write activity, not a primary performance metric in Intelligent Insights. Option E (failed authentication attempts) is a security metric, unrelated to resource consumption.

Answer

High number of deadlocks.

Answer

Increased transaction log usage.

Answer

Failed authentication attempts.

Question 11

You are a database administrator for a financial services company that runs a critical application on Azure SQL Database in the Business Critical service tier. The database is named 'TransactionsDB' and has a size of 500 GB. The application experiences periodic performance degradation during end-of-month batch processing. Analysis shows that the degradation coincides with high log write activity and increased latency for write transactions. You have already verified that the log rate is within the service tier limits. The batch process performs a large number of INSERT, UPDATE, and DELETE operations on multiple tables. You need to optimize the transaction log performance without changing the application code or the service tier. The database uses the full recovery model and has a log backup every 5 minutes. What should you do?

Accepted Answer

Enable accelerated database recovery to reduce the log space required for long-running transactions.. Accelerated Database Recovery (ADR) in Azure SQL Database significantly reduces the time required for recovery and improves transaction log performance by allowing the database to quickly recover from long-running transactions. It does this by persisting transaction state and enabling faster rollback and undo operations, which reduces the log space required and alleviates log write latency. Since the log rate is within limits but performance degradation occurs due to high log write activity, enabling ADR is the appropriate optimization without changing application code or service tier.

Answer

Implement transactional replication to offload write operations to a secondary database.

Answer

Enable page compression on all tables to reduce the amount of data written to the log.

Answer

Increase the initial size of the transaction log file to 200 GB and set the auto-growth increment to a fixed size of 1 GB.

Question 12

You manage an Azure SQL Database that runs a customer-facing application. You notice that during peak hours, the DTU consumption reaches 100% and queries experience timeouts. You need to ensure consistent performance without manual intervention. What should you implement?

Accepted Answer

Scale up the database to a higher service tier (e.g., from S2 to S3).. The scenario describes sustained 100% DTU consumption during peak hours with query timeouts, which indicates the database has hit its resource ceiling. Scaling up to a higher service tier (e.g., S2 to S3) increases the available DTUs, providing more CPU, IOPS, and memory to handle the peak workload without manual intervention. This directly addresses the resource bottleneck causing timeouts.

Answer

Enable Query Store to identify costly queries.

Answer

Implement read scale-out to offload read queries.

Answer

Configure automatic tuning to force plan regression fixes.

Question 13

You are designing a database monitoring solution for a critical Azure SQL Database. You need to retain performance data for 90 days and receive alerts when DTU usage exceeds 80%. Which combination of tools should you use?

Accepted Answer

Azure SQL Analytics (now integrated in Azure Monitor) and configure alerts.. Azure SQL Analytics (now integrated into Azure Monitor) is designed to collect and retain performance telemetry for Azure SQL Database, including DTU usage, and supports configurable retention periods (e.g., 90 days) via Log Analytics workspace. It also integrates with Azure Monitor alerts, allowing you to trigger alerts when DTU consumption exceeds a threshold like 80%. This combination directly satisfies both the retention and alerting requirements without custom development.

Answer

Query Store with retention set to 90 days.

Answer

DMVs queried by a custom script.

Answer

Intelligent Insights and configure email notifications.

Question 14

You have an Azure SQL Database that is used by a reporting application. Reports run slowly during the day. You notice that the database is at the S1 tier. You need to improve report performance without changing application code. What should you do?

Accepted Answer

Scale up the database to a higher service tier.. The S1 tier is a DTU-based service tier with limited resources, and scaling up to a higher tier (e.g., S2, S3, or Premium) increases CPU, memory, and IOPS available to the database. This directly improves report performance without requiring application code changes. Index rebuilding and partitioning are optimization techniques but do not address the fundamental resource constraints of the S1 tier.

Answer

Rebuild fragmented indexes.

Answer

Implement table partitioning.

Answer

Enable read scale-out to offload reports.

Question 15

Which TWO actions can reduce storage costs for an Azure SQL Database? (Select two.)

Accepted Answer

Enable row or page compression on large tables.. Option B is correct because enabling row or page compression on large tables reduces the physical storage footprint of the data, directly lowering the amount of billable storage consumed by the Azure SQL Database. Option E is correct because archiving historical data to Azure Blob Storage using external tables (via PolyBase/external data sources) moves cold data out of the database's billable storage into cheaper Blob Storage, reducing the database's storage size and cost. Option A is incorrect because increasing the service tier to get more storage raises, not reduces, cost. Option C is incorrect because reducing backup retention to 1 day affects backup storage retention costs but is not one of the two intended storage-cost reduction actions for the database data itself, and Azure SQL Database has a minimum backup retention of 7 days for most configurations. Option D is incorrect because automatic tuning optimizes query plans for performance, not storage cost.

Answer

Increase the service tier to get more storage.

Answer

Reduce backup retention to 1 day.

Answer

Enable automatic tuning to optimize query plans.