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

Question 1

You have an Azure SQL Database that is experiencing performance degradation. You suspect that parameter sniffing is causing suboptimal execution plans. What should you do to mitigate this issue without changing application code?

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

Accepted Answer

Enable forced parameterization for the database.. Forced parameterization (option D) is the correct mitigation because it instructs the SQL Server query optimizer to treat all literal values in queries as parameters, which reduces the likelihood of parameter sniffing causing suboptimal plans for repeated queries. This is done at the database level without requiring any changes to application code, making it ideal for scenarios where code modification is not feasible.

Answer

Add the OPTION (RECOMPILE) query hint to all queries.

Answer

Clear the plan cache using DBCC FREEPROCCACHE.

Answer

Enable the 'Optimize for Ad Hoc Workloads' setting.

Question 2

You are monitoring an Azure SQL Database using Intelligent Insights. You receive an alert that resource usage has exceeded 90% for the past hour. What is the recommended first step to troubleshoot?

Accepted Answer

Review the Intelligent Insights root cause analysis report.. Intelligent Insights is Azure SQL Database's built-in diagnostics engine that continuously analyzes workload telemetry and produces a root cause analysis (RCA) report whenever it detects a performance anomaly such as sustained resource usage above 90%. The RCA report already identifies the specific problem category (for example, a blocking query, a missing index, or a plan regression) and points to the offending query or wait. Reviewing this report first is the fastest path to the actual cause, and it prevents the common mistake of blindly scaling the tier before knowing whether the bottleneck is CPU, IO, or a query-level issue.

Answer

Scale up the database to a higher service tier immediately.

Answer

Check sys.dm_os_wait_stats for high wait types.

Answer

Enable the Query Store and wait for data collection.

Question 3

You have an Azure SQL Database that uses the Hyperscale service tier. You notice that during peak hours, the log rate is throttled frequently. You need to reduce log write throttling. What should you do?

Accepted Answer

Increase the service level objective (SLO) to a higher vCore count.. In Azure SQL Database Hyperscale, log write throttling occurs when the log generation rate exceeds the throughput capacity of the current service level objective (SLO). Increasing the vCore count raises the log write throughput limit, directly alleviating throttling. This is the correct approach because Hyperscale log rate limits are tied to the SLO, and higher tiers provide higher log I/O bandwidth.

Answer

Enable accelerated database recovery to reduce log generation.

Answer

Reduce the size of individual transactions to commit more frequently.

Answer

Partition large tables to spread log writes across files.

Question 4

You run the KQL query shown in the exhibit. The query returns a list of query IDs. What is the purpose of this query?

Accepted Answer

Identify the top 10 queries by average duration.. The KQL query in the exhibit uses `summarize` with `avg(duration)` and then `top 10 by avg_duration`, which explicitly calculates the average duration per query_id and returns the top 10 queries with the highest average duration. This directly matches the purpose of identifying the top 10 queries by average duration.

Answer

Identify queries consuming the most CPU.

Answer

Identify queries with the highest total wait time.

Answer

Identify queries with the highest execution count.

Question 5

You are configuring Azure SQL Database automatic tuning. You want to enable the feature that automatically creates and drops indexes based on workload patterns. Which option should you enable?

Accepted Answer

Create Index and Drop Index. Azure SQL Database's automatic tuning includes the 'Create Index' and 'Drop Index' options, which together automatically create indexes to improve query performance and drop unused or duplicate indexes to reduce overhead based on workload patterns. This feature analyzes query execution plans and index usage statistics to dynamically adjust indexing without manual intervention.

Answer

CREATE INDEX

Answer

Force Parameterization

Answer

Force Last Good Plan

Question 6

You have an Azure SQL Database that is configured with geo-replication. You need to optimize read performance for reporting queries that run on the secondary replica. Which three actions should you take? (Choose three.)

Accepted Answer

Enable automatic tuning on the secondary.. Enabling automatic tuning on the secondary replica allows the database to automatically create, drop, and force index recommendations based on the workload running on that replica. This is particularly beneficial for reporting queries, as the secondary can independently optimize its own index strategy without affecting the primary. Automatic tuning on a readable secondary helps maintain query performance by adapting to the read-heavy workload patterns typical of reporting scenarios.

Answer

Configure the secondary as a readable secondary.

Answer

Force query plans on the primary replica to benefit the secondary.

Question 7

You are troubleshooting a performance issue in an Azure SQL Database. You need to identify the queries that are consuming the most CPU over the last hour. Which two methods can you use? (Choose two.)

Accepted Answer

sys.dm_exec_query_stats. sys.dm_exec_query_stats (Option A) returns aggregate performance statistics for cached query plans, including total CPU time (total_worker_time), which can be filtered by creation_time or last_execution_time to focus on the last hour. Query Store's top resource consuming queries report (Option E) provides a built-in, graphical view of queries ranked by CPU, duration, or other metrics over a configurable time window, making it ideal for identifying high-CPU queries in the last hour.

Answer

sys.dm_os_wait_stats

Answer

sys.dm_db_index_usage_stats

Answer

sys.dm_exec_requests

Question 8

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?

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 9

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.. For planned failovers in Azure SQL failover groups, pre-seeding the secondary by ensuring it is fully synchronized before initiating the failover minimizes downtime because the secondary is already caught up and can be promoted almost instantly. Initiating a manual sync (or verifying synchronization) reduces the delta that must be applied during the failover transition.

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 10

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 11

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 12

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 13

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). Query Store (E) is correct because it persistently captures query execution plans, runtime statistics, and wait categories inside the database, allowing you to analyze query performance regressions and trends over time via views such as sys.query_store_runtime_stats. Azure SQL Analytics (Azure Monitor) (D) is correct because it is a monitoring solution that collects and visualizes Azure SQL Database telemetry, including query performance metrics, in Log Analytics/ Azure Monitor so you can analyze performance historically across time. DMVs (A) are incorrect here because they expose current or cached diagnostic state and are not designed to retain query performance history over time. Intelligent Insights (B) is incorrect because it is an automated diagnostics service that surfaces detected issues rather than a tool for directly analyzing query performance trends over time. Extended Events (C) is incorrect because it is an event-tracing mechanism for capturing live activity, not a built-in historical query performance analysis tool.

Answer

Dynamic Management Views (DMVs)

Answer

Intelligent Insights

Answer

Extended Events

Question 14

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 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 is a lightweight DMV that exposes cumulative execution statistics including total_worker_time, total_elapsed_time, and total_logical_reads per cached plan, which lets you pinpoint the queries consuming the most resources and correlate them with wait types via sys.dm_os_wait_stats. It requires no schema changes and minimal overhead, making it the right first step for intermittent timeouts. Query Store is powerful but adds write overhead and is better suited to plan-regression analysis once you know which queries are problematic.

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.