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

Question 1

You manage an Azure SQL Database that uses the General Purpose service tier. You need to reduce storage costs by archiving old data that is not frequently accessed. The archived data must still be queryable occasionally. What should you do?

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

Accepted Answer

Implement table partitioning and move old partitions to a separate database with a lower service tier.. Implementing table partitioning allows you to efficiently manage large tables by splitting them into smaller, more manageable partitions. You can then move older partitions to a separate database with a lower service tier (e.g., from General Purpose to Basic), which reduces storage costs while keeping the archived data queryable when needed. Stretch Database (option A) is deprecated and no longer recommended. Elastic Query (option C) is used to query remote databases, not to archive data. Hyperscale (option D) is designed for high scalability and performance, not for cost reduction on infrequently accessed data.

Answer

Enable Stretch Database to transparently stretch old data to Azure Blob Storage.

Answer

Use Elastic Query to query historical data stored in Azure Blob Storage.

Answer

Migrate to the Hyperscale service tier to take advantage of tiered storage.

Question 2

You have an Azure SQL Database that is configured with automatic failover groups. During a planned failover, you notice that the failover takes longer than expected. You need to minimize downtime during future planned failovers. What should you do?

Accepted Answer

Pre-seed the secondary replica by initiating a manual sync before the planned failover.. Pre-seeding the secondary replica by initiating a manual sync before the planned failover ensures that the secondary is fully up to date, reducing the synchronization time during the actual failover and thereby minimizing downtime. Option A is incorrect because increasing the service tier of the secondary does not reduce failover time; it only improves performance. Option B is incorrect because removing the failover group would eliminate the failover capability, not improve planned failover speed. Option D is incorrect because a forced failover with data loss is for unplanned scenarios and would cause data loss, which is not recommended for planned failovers.

Answer

Increase the service tier of the secondary to match the primary.

Answer

Remove the failover group and use a different disaster recovery strategy.

Answer

Use a forced failover with data loss to speed up the process.

Question 3

You are managing an Azure SQL Database that experiences intermittent performance degradation. Query Store shows a significant increase in wait time for PAGEIOLATCH_SH. You need to identify the most likely cause. What should you investigate first?

Accepted Answer

Insufficient IOPS or throughput at the database level. PAGEIOLATCH_SH waits indicate I/O subsystem pressure, often due to insufficient IOPS or throughput. Option A is incorrect because out-of-date statistics cause cardinality estimation errors, not I/O waits. Option C is incorrect because missing indexes typically cause table scans but not necessarily PAGEIOLATCH waits. Option D is incorrect because blocking causes waits like LCK_M_*, not PAGEIOLATCH.

Answer

Out-of-date statistics

Answer

Missing indexes

Answer

Blocking from long-running transactions

Question 4

You have a SQL Managed Instance with a large database. You notice that the automatic tuning recommendations are not being applied. You need to ensure that automatic tuning is enabled and that recommendations are automatically executed. What should you do?

Accepted Answer

Set the server-level automatic_tuning option to INHERIT and set database-level FORCE_LAST_GOOD_PLAN to ON. To enable automatic tuning in Azure SQL Managed Instance, you must set the server-level automatic_tuning option to INHERIT (which allows the server to inherit the default Azure tuning behavior) and then set the database-level FORCE_LAST_GOOD_PLAN to ON. This configuration ensures that the automatic tuning system can both generate recommendations and automatically apply them (specifically the FORCE_LAST_GOOD_PLAN recommendation) without manual intervention.

Answer

Set the database-level automatic tuning option to INHERIT

Answer

Set the database-level query_store_desired_state to ON

Answer

Set the server-level automatic_tuning option to OFF

Question 5

You are monitoring an Azure SQL Database that is running a mission-critical workload. You notice that the DTU consumption is consistently above 90% during peak hours. You need to recommend a solution to reduce the DTU consumption. What should you recommend?

Accepted Answer

Scale up to a higher service tier or increase DTUs. When DTU consumption consistently exceeds 90%, the database is resource-constrained, leading to performance degradation. Scaling up to a higher service tier or increasing DTUs directly provides more CPU, memory, and I/O resources, alleviating the bottleneck and reducing DTU utilization percentage. This is the standard corrective action for sustained high DTU usage in Azure SQL Database.

Answer

Scale down to a lower service tier

Answer

Enable geo-replication

Answer

Enable read scale-out

Question 6

You are managing an Azure SQL Database that uses Intelligent Insights. You receive an alert that there is a performance issue with a specific query. You need to analyze the root cause. What should you use?

Accepted Answer

Query Store to review query execution plans and wait statistics. Query Store is the correct tool because it captures historical execution plans, runtime statistics, and wait statistics for individual queries, allowing you to pinpoint the root cause of a performance regression. Intelligent Insights provides high-level diagnostics but not the granular per-query plan and wait data needed for deep analysis of a specific query issue.

Answer

Intelligent Insights report

Answer

Automatic Tuning recommendations

Answer

Azure Monitor metrics for the database

Question 7

You have an Azure SQL Database that uses the Hyperscale service tier. You notice that the log rate is consistently high. You need to reduce the log write latency. What should you do?

Accepted Answer

Increase the log rate limit in the Hyperscale service tier. In the Hyperscale service tier, log write latency is directly influenced by the log rate limit. Increasing this limit allocates more I/O bandwidth to the log write path, reducing latency under high log generation. This is the correct action because the log rate limit is a configurable parameter in Hyperscale that controls how fast log records can be written to the log service.

Answer

Enable data compression on the tables

Answer

Enable Accelerated Database Recovery

Answer

Change the service tier to General Purpose

Question 8

You are monitoring an Azure SQL Database and notice that the average CPU usage is 80% and the average data IO percentage is 70%. You need to identify the most likely cause of the high resource usage. What should you check first?

Accepted Answer

Use Query Store to identify top resource-consuming queries. High average CPU (80%) and data IO (70%) suggest that the database is under sustained load from inefficient or resource-intensive queries. Query Store captures query execution plans, runtime statistics, and resource consumption per query, making it the fastest way to pinpoint the top resource consumers. Checking Query Store first allows you to identify the specific queries driving CPU and IO, which is the most direct diagnostic step.

Answer

Check for long-running maintenance tasks

Answer

Check for connection pooling issues

Answer

Check for blocking and deadlocks

Question 9

You manage an Azure SQL Database that uses the General Purpose service tier. You need to reduce storage costs by archiving old data that is not frequently accessed. The archived data must still be queryable occasionally. What should you do?

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

Accepted Answer

Disable auto-pause for the serverless database. The slow first query after auto-resume is caused by the cold-start latency of the serverless compute tier, which includes provisioning resources and warming the buffer pool. Disabling auto-pause ensures the database remains online and the buffer pool stays populated, eliminating the cold-start delay for the first query.

Answer

Increase the maximum vCores

Answer

Create a SQL Agent job to ping the database every hour

Answer

Enable Query Store

Question 10

You are optimizing an Azure SQL Database that has a large table with a clustered columnstore index. You notice that the index has been fragmented over time. You need to improve query performance. What should you do?

Accepted Answer

Reorganize the columnstore index. REORGANIZE is the recommended maintenance operation for clustered columnstore indexes to defragment them by removing rows from deleted bitmaps without requiring a full rebuild. Option A (drop and recreate) is incorrect because it is disruptive and unnecessary for resolving fragmentation. Option C (update statistics) does not address index fragmentation directly. Option D (rebuild) is more resource-intensive and typically reserved for indexes with high fragmentation, whereas reorganize is optimal for regular maintenance.

Answer

Drop and recreate the columnstore index

Answer

Update statistics on the table

Answer

Rebuild the columnstore index

Question 11

You have an Azure SQL Database that is experiencing high wait times on RESOURCE_SEMAPHORE waits. You need to identify the root cause. What should you check?

Accepted Answer

Queries with large memory grants. RESOURCE_SEMAPHORE waits indicate memory grant pressure, typically caused by queries requesting large memory grants. Option D is correct. Option A is incorrect because blocking and deadlocks produce LCK_M_* waits, not RESOURCE_SEMAPHORE. Option B is incorrect because high CPU usage leads to SOS_SCHEDULER_YIELD waits. Option C is incorrect because disk I/O bottlenecks cause PAGEIOLATCH waits.

Answer

Blocking and deadlocks

Answer

High CPU usage

Answer

Disk I/O bottlenecks

Question 12

You are configuring performance monitoring for an Azure SQL Database. You need to identify which two tools can be used to analyze query performance over time. Which TWO should you select?

Accepted Answer

Azure SQL Analytics (Azure Monitor). The correct answers are D and E. Query Store (E) captures and retains query execution metrics and plans over time, enabling historical performance analysis. Azure SQL Analytics (D), now part of Azure Monitor, provides a comprehensive view of query performance across multiple databases with historical data. Option A (DMVs) displays current state and recent activity but lacks long-term history. Option B (Intelligent Insights) offers automated diagnostics and summaries but not detailed query-level history. Option C (Extended Events) is designed for real-time event capture, not historical analysis.

Answer

Dynamic Management Views (DMVs)

Answer

Intelligent Insights

Answer

Extended Events

Question 13

You are optimizing an Azure SQL Database that uses the Hyperscale service tier. You need to identify which three actions can improve write performance. Which THREE should you select?

Accepted Answer

Use smaller log writes. Using smaller log writes reduces log write latency, which improves write performance in Hyperscale. Option B is correct: Increasing the log rate limit allows higher log throughput, directly enhancing write performance. Option E is correct: Enabling Accelerated Database Recovery reduces log amplification and improves log write efficiency, benefiting write performance. Option C is incorrect: The Hyperscale service tier is distinct from Business Critical; moving to Business Critical does not apply to Hyperscale. Option D is incorrect: Read scale-out improves read performance, not write performance.

Answer

Increase the service tier to Business Critical

Answer

Enable read scale-out

Question 14

You are monitoring an Azure SQL Database. You need to identify which two metrics are most important for detecting a memory pressure issue. Which TWO should you select?

Accepted Answer

Memory grants pending. Memory grants pending indicates queries that are waiting for memory grants, which is a direct sign of memory pressure. Page life expectancy (PLE) measures how long pages remain in the buffer pool; a low PLE suggests that pages are being evicted frequently due to memory pressure. Therefore, both B and C are the best metrics to detect memory pressure. CPU percentage (D) indicates CPU pressure, and Data IO percentage (E) indicates I/O pressure, not memory pressure. Log IO percentage (A) relates to log write activity, not memory. Hence, the correct choices are B and C.

Answer

Log IO percentage

Answer

CPU percentage

Answer

Data IO percentage

Question 15

You are monitoring a critical production Azure SQL Database that is experiencing intermittent query timeouts. The database is configured with the General Purpose service tier. You need to identify the root cause of the timeouts with minimal overhead. What should you review first?

Accepted Answer

Use sys.dm_exec_query_stats to identify queries with high wait statistics.. Sys.dm_exec_query_stats provides aggregated query performance data, including wait statistics, with minimal overhead. High wait stats often point to resource contention (e.g., CPU, IO) that can cause timeouts. Option A is incorrect: automatic tuning is for addressing plan regression after it's identified, not for initial troubleshooting. Option B is incorrect: increasing the service tier is a reactive scaling measure, not a diagnostic step. Option D is incorrect: Query Store must be enabled to use the Regressed Queries report, and it may not be the first step if not already enabled; sys.dm_exec_query_stats is a lightweight dynamic management view available by default.

Answer

Implement automatic tuning to force plan regression fixes.

Answer

Increase the database service tier to Business Critical.

Answer

Enable Query Store and review the Regressed Queries report.