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

Question 1

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.. Creating missing indexes (option D) and updating statistics (option E) are performance optimization techniques that do not require changes to application code. Indexes recommended by the Database Engine Tuning Advisor can reduce query execution time by enabling efficient data access paths, while updating statistics helps the query optimizer generate better execution plans. Options A and B require code changes or direct query modifications, option C is a security feature that may impact performance but not optimize it, and upgrading the service tier (option B) is a scaling action that increases resources but is not a targeted query optimization.

Answer

Rewrite queries to use sargable predicates.

Answer

Upgrade to a higher service tier.

Answer

Enable row-level security on sensitive tables.

Question 2

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.. The correct answers are B, D, and E. Automatic tuning for Azure SQL Database includes the options 'CREATE INDEX' (option E), 'DROP INDEX' (option B), and 'FORCE LAST GOOD PLAN' (option D). Option A (automatically creating plan guides) is not part of automatic tuning; plan guides are a manual feature. Option C (automatic statistics update) is a separate maintenance operation and not an automatic tuning option. Therefore, when configuring automatic tuning, you should consider enabling or disabling index creation, index dropping, and forcing the last good plan to address query plan regressions.

Answer

Whether to enable automatic plan guide creation.

Answer

Whether to enable automatic statistics update.

Question 3

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 4

Which THREE actions can you take to monitor and optimize database resources in Azure SQL Database? (Choose three.)

Accepted Answer

Use Query Store to track query performance over time.. Options B, C, and D are correct. Query Store tracks query performance over time, dynamic management views help identify blocking and resource waits, and automatic tuning automatically implements performance improvements. Option A (Microsoft Defender for SQL) is a security feature for vulnerability detection, not directly for monitoring and optimizing database resources. Option E (Microsoft Sentinel) is a security information and event management (SIEM) tool, not specifically for database monitoring and optimization.

Answer

Enable Microsoft Defender for Azure SQL to detect vulnerabilities.

Answer

Configure Microsoft Sentinel to monitor database activity.

Question 5

You are analyzing the exhibit KQL query that queries Azure Diagnostics logs for Query Store runtime statistics. The query is intended to show average CPU time per hour for each database. However, the result shows no data for the last 24 hours, although Query Store is enabled on all databases. What is the most likely reason?

Accepted Answer

The diagnostic settings are not configured to send QueryStoreRuntimeStatistics to Log Analytics.. Query Store runtime statistics are sent to Azure Diagnostics only if diagnostic settings are configured to collect that category. Option A is wrong because Query Store is enabled. Option C is wrong because the query is correct. Option D is wrong because the KQL query syntax does not require 'project' before 'summarize'; placement does not affect results.

Answer

Query Store is not enabled on the databases.

Answer

The time range in the query is too narrow and excludes the last 24 hours.

Answer

The query syntax is incorrect and needs to use 'project' before 'summarize'.

Question 6

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.. Enabling Accelerated Database Recovery (ADR) reduces the log space required for long-running transactions by using a persistent version store (PVS) to avoid writing undo logs. This helps mitigate log growth during batch operations, reducing the frequency of log autogrowth events and improving overall log write performance. Option A is incorrect because transactional replication adds overhead and does not directly optimize log performance. Option B is incorrect because page compression reduces data storage but does not significantly reduce log writes (log records are row-level). Option D is incorrect because Azure SQL Database manages log file sizing automatically; you cannot configure initial size or auto-growth increments manually.

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 7

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).. Scaling up to a higher service tier (e.g., from S2 to S3) increases DTU limits, providing more CPU, memory, and IO resources to handle peak demand, thus preventing timeouts without manual intervention. Option A is incorrect because Query Store helps monitor and troubleshoot performance but does not automatically resolve resource shortages. Option B is incorrect because read scale-out offloads read-only queries to a read-only replica, but the issue is DTU consumption reaching 100%, which affects all queries, not just reads. Option C is incorrect because automatic tuning can fix plan regression but does not add resources; it only optimizes query plans.

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 8

You are monitoring an Azure SQL Database and notice high PAGELATCH waits. What is the most likely cause?

Accepted Answer

Concurrent inserts into a table with a clustered index causing last-page contention.. PAGELATCH waits indicate contention on data pages, often due to concurrent inserts to the same page, such as the last page of a clustered index. Option B is wrong because buffer pool insufficient leads to PAGEIOLATCH waits, not PAGELATCH. Option C is wrong because high CPU usage due to inefficient queries causes CPU pressure, not directly PAGELATCH waits. Option D is wrong because long-running transactions blocking other queries results in lock waits, not page latch contention.

Answer

Insufficient buffer pool size leading to frequent reads from disk.

Answer

High CPU usage due to inefficient queries.

Answer

Long-running transactions blocking other queries.

Question 9

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 part of Azure Monitor) provides long-term retention and alerting capabilities. Option A is wrong because Query Store default retention is limited. Option B is wrong because DMVs only show current state and are not designed for long-term retention and alerting. Option D is wrong because Intelligent Insights provides analysis but not configurable retention.

Answer

Query Store with retention set to 90 days.

Answer

DMVs queried by a custom script.

Answer

Intelligent Insights and configure email notifications.

Question 10

The query returns a list of query hashes with high average duration. You need to identify which queries are most likely causing CPU pressure. What additional metric should you include?

Accepted Answer

Include avg_cpu_time to measure CPU usage.. Avg_cpu_time directly measures CPU usage per query, helping correlate high duration with CPU pressure. Option A is incorrect because wait_stats show wait types (e.g., blocking), not CPU consumption. Option B is incorrect because execution count alone does not indicate CPU usage; a query could run frequently but use little CPU. Option C is incorrect because avg_logical_reads measure I/O, not CPU pressure.

Answer

Include wait_stats to see blocking.

Answer

Include count_executions to see frequency.

Answer

Include avg_logical_reads to see I/O consumption.

Question 11

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.. Scaling up to a higher service tier (e.g., S2 or S3) provides more DTUs, which directly addresses the resource bottleneck and improves report performance without changing application code. Option A is incorrect because while rebuilding fragmented indexes can help query performance, it may not be sufficient if the underlying issue is insufficient resources. Option C is incorrect because table partitioning can improve manageability and partition elimination but does not guarantee performance improvement for reports and often requires query changes. Option D is incorrect because read scale-out is only available on the Premium and Business Critical service tiers, not on the S1 tier.

Answer

Rebuild fragmented indexes.

Answer

Implement table partitioning.

Answer

Enable read scale-out to offload reports.

Question 12

You manage an Azure SQL Database that has automatic tuning enabled. You receive an alert that the database is experiencing plan regression. The automatic tuning has forced a plan, but performance is still poor. What should you do first?

Accepted Answer

Review the Query Store to identify the root cause of regression.. Query Store can show plan history and regression details, helping analyze why the forced plan is not optimal. Option A is wrong because disabling automatic tuning and creating a plan guide may be premature without first identifying the root cause. Option C is wrong because manually reverting to a previous plan using Query Store should not be the first action; you need to understand the regression first. Option D is wrong because scaling up the database does not directly address plan quality or regression.

Answer

Disable automatic tuning and create a plan guide.

Answer

Manually revert to the previous plan using Query Store.

Answer

Scale up the database to reduce resource pressure.

Question 13

You have an Azure SQL Database in the General Purpose tier. You notice that the log write throughput is consistently above the service tier limit, causing transaction throttling. You need to resolve this without moving to Business Critical. What should you do?

Accepted Answer

Batch transactions and reduce log writes.. Batching transactions reduces the number of log write operations, lowering the log write throughput below the service tier limit and avoiding throttling. Option A is incorrect because increasing the max log size does not affect the log write rate; it only provides more storage. Option C is incorrect because accelerated database recovery (ADR) improves recovery time and may reduce log I/O for crash recovery, but it does not directly reduce the sustained log write throughput from transaction processing. Option D is incorrect because the requirement explicitly states not to move to Business Critical.

Answer

Increase the max log size using ALTER DATABASE.

Answer

Enable accelerated database recovery to reduce log I/O.

Answer

Move to Business Critical tier.

Question 14

You are monitoring an Azure SQL Database using sys.dm_db_wait_stats. You see a high percentage of WRITELOG waits. What is the most likely cause?

Accepted Answer

The transaction log is on a slow I/O subsystem.. A high percentage of WRITELOG waits indicates that transaction log writes are slow, often due to I/O latency on the log drive. Therefore, the most likely cause is a slow I/O subsystem for the transaction log (Option B). Option A is incorrect because tempdb allocation contention manifests as PAGELATCH waits. Option C is incorrect because blocking is represented by LCK_M_* waits. Option D is incorrect because CPU pressure shows SOS_SCHEDULER_YIELD waits.

Answer

Tempdb has allocation contention.

Answer

Queries are blocked by locks.

Answer

CPU is under pressure.

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.. To reduce storage costs for an Azure SQL Database, you can enable row or page compression on large tables (option B) to decrease data size. Archiving historical data to Azure Blob Storage using external tables (option E) moves cold data out of the database, reducing storage consumption. Option A is incorrect because increasing the service tier typically increases storage costs. Option C is wrong because reducing backup retention does not affect storage used for data files, only backup storage. Option D is incorrect because automatic tuning optimizes query performance, not storage costs. Therefore, the correct answers are B and E.

Answer

Increase the service tier to get more storage.

Answer

Reduce backup retention to 1 day.

Answer

Enable automatic tuning to optimize query plans.