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

Question 1

Your SQL Server is experiencing deadlocks. You enable trace flag 1222 to capture deadlock graphs in the error log. Where can you retrieve the deadlock information?

Accepted Answer

The SQL Server error log, viewable using sp_readerrorlog.. Trace flag 1222 writes deadlock information to the SQL Server error log. This log can be read using sp_readerrorlog, xp_readerrorlog, or the Log File Viewer in SQL Server Management Studio. Option D refers to this error log, though retrieving it via sp_readerrorlog is the direct method; the original mention of sys.messages and sys.fn_get_audit_file is incorrect and replaced.

Answer

sys.dm_exec_sessions

Answer

sys.query_store_query_text

Answer

sys.dm_exec_requests

Question 2

You manage an Azure SQL Database that uses automatic tuning. The database has the FORCE_LAST_GOOD_PLAN option enabled. A critical query suddenly starts performing poorly after a plan change. What is the expected behavior?

Accepted Answer

The database will automatically revert to the last good plan and log the event in sys.dm_db_tuning_recommendations.. When FORCE_LAST_GOOD_PLAN is enabled and automatic tuning detects a plan regression, Azure SQL Database automatically reverts to the last known good plan. The event is logged in sys.dm_db_tuning_recommendations for audit and analysis. This behavior is part of the automatic plan correction feature, which does not require manual intervention.

Answer

The query will continue with the poor plan until you manually force a plan.

Answer

You will receive a recommendation to revert the plan change.

Answer

The database will automatically revert to the last good plan without notification.

Question 3

You need to monitor the storage space usage of an Azure SQL Database over time. Which tool should you use?

Accepted Answer

Azure SQL Analytics (Azure Monitor). Azure SQL Analytics in Azure Monitor provides historical storage metrics. Option A is wrong because Intelligent Insights is for proactive diagnostics, not storage monitoring. Option C is wrong because Query Store focuses on query performance. Option D is wrong because SSMS does not provide historical monitoring.

Answer

Intelligent Insights

Answer

Query Store

Answer

SQL Server Management Studio (SSMS)

Question 4

Which TWO metrics from sys.dm_db_resource_stats should you monitor to identify a disk IO bottleneck in an Azure SQL Database?

Accepted Answer

avg_data_io_percent. Options C and E are correct. avg_data_io_percent measures data IO usage relative to the limit, and avg_log_write_percent measures log IO usage—both indicate disk IO bottlenecks. Option A (avg_cpu_percent) is CPU usage. Option B (max_size_percent) is storage space. Option D (avg_memory_usage_percent) is memory.

Answer

avg_cpu_percent

Answer

max_size_percent

Answer

avg_memory_usage_percent

Question 5

Which THREE actions can help reduce the frequency of parameter-sensitive plan (PSP) problems in Azure SQL Database? (Choose three.)

Accepted Answer

Use the Optimize for ad hoc workloads setting (or OPTIMIZE FOR UNKNOWN).. Options A, C, and D are correct. Option A (Optimize for ad hoc workloads or OPTIMIZE FOR UNKNOWN) helps by using average distribution instead of sniffing parameter values, reducing parameter-sensitive plan (PSP) problems. Option C (RECOMPILE hint) forces a new plan per execution, avoiding stale plans caused by parameter sniffing. Option D (forced parameterization) creates multiple cached plans for different parameter values, directly addressing PSP. Option B (disabling parameter sniffing) is not a recommended approach and can lead to suboptimal plans. Option E (Query Store) is a monitoring tool that captures query data but does not directly reduce PSP frequency.

Answer

Disable parameter sniffing by using the DISABLE_PARAMETER_SNIFFING hint.

Answer

Enable Query Store and use the Performance Dashboard.

Question 6

Which TWO database properties can be configured to optimize performance for a read-heavy workload in Azure SQL Database?

Accepted Answer

Query Store. For a read-heavy workload in Azure SQL Database, enabling Query Store (option A) helps monitor query performance, identify regressions, and optimize query plans. Read scale-out (option E) offloads read-only queries to a readable secondary replica, reducing load on the primary and improving performance. Options B (Data masking) is a security feature, C (Long-term retention policy) is for backup retention, and D (Automatic tuning - CREATE INDEX) creates indexes but is not specifically for read-heavy workloads; it can help but is not a primary property for read offload.

Answer

Data masking

Answer

Long-term retention policy

Answer

Automatic tuning (CREATE INDEX)

Question 7

You are managing an Azure SQL Database that is experiencing intermittent performance degradation. Query Store shows that a specific query's execution plan changed, causing increased CPU usage. You need to ensure consistent performance without rewriting the application. What should you do?

Accepted Answer

Force the previous query plan using Query Store. Force the previous query plan using Query Store. This approach directly addresses the root cause by locking the query to a known good plan, ensuring consistent performance without application changes. Option A is incorrect because increasing the DTU/service tier may temporarily improve performance but does not fix the plan regression. Option B is incorrect because creating a missing index recommendation may help but does not guarantee the query will use the previous plan. Option C is incorrect because dropping and recreating the index is disruptive and may not force the plan to revert.

Answer

Increase the DTU/service tier of the database

Answer

Create a missing index recommendation

Answer

Drop and recreate the index used by the query

Question 8

Your company uses Azure SQL Database with active geo-replication. You notice that the secondary database in a different region has a high log write latency. Users report that the primary database performance is normal. What is the most likely cause?

Accepted Answer

Network latency between the primary and secondary regions. Active geo-replication asynchronously replicates transaction log records from the primary to the secondary database. High log write latency on the secondary is most commonly caused by network latency between the regions. Option A is incorrect because the secondary database's log IOPS are not directly the issue; the log is applied from the primary. Option C is incorrect because primary CPU usage does not directly impact secondary log write latency. Option D is incorrect because read workload on the secondary does not affect log write latency; the secondary applies log regardless of read activity.

Answer

Insufficient log IOPS on the secondary database

Answer

High CPU usage on the primary database

Answer

Excessive read workload on the secondary database

Question 9

You have an Azure SQL Managed Instance with a database that is used for reporting. The reporting queries are read-only and can tolerate some latency. You want to offload the reporting workload from the primary instance to a secondary read-only replica. Which feature should you use?

Accepted Answer

Readable secondary in a failover group. The correct feature is a readable secondary in a failover group. This allows read-only queries to be routed to the secondary replica, offloading the reporting workload while maintaining high availability. SSIS (Option A) is an ETL tool, not for offloading read queries. Transaction replication (Option C) requires complex configuration and is not native to Managed Instance. Option D is incorrect because an auto-failover group does not support a read-write secondary; the secondary is always read-only.

Answer

SQL Server Integration Services (SSIS)

Answer

Transaction replication

Answer

Auto-failover group with read-write secondary

Question 10

You are monitoring an Azure SQL Database using Intelligent Insights. You receive an alert that 'Query performance degradation' was detected. After reviewing the details, you find that a specific query now has a higher duration and is using a different execution plan. What is the recommended first step to troubleshoot?

Accepted Answer

Query the Query Store to compare the previous and current plans. Query Store captures execution plans and runtime statistics, allowing you to compare the previous plan with the current plan to identify plan regression. Option A is wrong because restarting the database is disruptive and doesn't address the root cause. Option B is wrong because increasing the service tier may not fix plan regression and is not the first step. Option D is wrong because Database Engine Tuning Advisor focuses on index and query design, not on comparing plan changes.

Answer

Restart the database to clear the plan cache

Answer

Increase the service tier of the database

Answer

Run the Database Engine Tuning Advisor

Question 11

You need to monitor the performance of an Azure SQL Database and set up alerts when the DTU consumption exceeds 80% for more than 5 minutes. Which Azure service should you use?

Accepted Answer

Azure Monitor metric alerts. Azure Monitor metric alerts can be configured on DTU percentage. Option B is wrong because Azure Advisor provides recommendations but not real-time alerts. Option C is wrong because Azure SQL Insights is for visualization, not alerting. Option D is wrong because Log Analytics workspaces store logs but do not natively provide metric alerts.

Answer

Azure Advisor

Answer

Azure SQL Insights (preview)

Answer

Log Analytics workspace

Question 12

You have an Azure SQL Database with automatic tuning enabled. You notice that a query that previously ran quickly is now running slower. Automatic tuning has implemented a plan correction. However, the new plan is performing worse. What should you do to revert to the previous behavior?

Accepted Answer

Revert the automatic tuning plan correction using the Azure portal or T-SQL. When automatic tuning implements a plan correction that performs worse, you can manually revert the plan correction using the Azure portal or T-SQL commands (e.g., `ALTER DATABASE ... SET AUTOMATIC_TUNING ...` or querying `sys.dm_db_tuning_recommendations` and using the `revert` action). Option A is incorrect because dropping and recreating the index does not revert the plan correction; it may force a new plan but is not the direct revert method. Option B is incorrect because automatic tuning does not automatically revert a plan correction that is performing worse; it may later adjust, but manual intervention is the reliable way. Option C is incorrect because disabling automatic tuning would only prevent future corrections but not revert the current one; the plan would remain until manually reverted or statistics change.

Answer

Drop and recreate the index used by the query

Answer

Wait for automatic tuning to revert the plan automatically

Answer

Disable automatic tuning for the database

Question 13

You are optimizing an Azure SQL Database that has a heavy workload of both reads and writes. The database has a clustered columnstore index on a large fact table. You notice that the index has high fragmentation and the performance of queries against this table is degrading. What should you do to improve performance?

Accepted Answer

Rebuild the clustered columnstore index. Rebuilding the clustered columnstore index reduces fragmentation and improves query performance. Rebuilding is the recommended method to defragment a columnstore index. Option B is incorrect because increasing the service tier addresses performance issues related to IOPS but does not resolve fragmentation. Option C is incorrect because dropping and recreating the index is more disruptive and unnecessary; a rebuild achieves the same result with less overhead. Option D is incorrect because reorganize is not supported for columnstore indexes; only rebuild is.

Answer

Increase the service tier to get more IOPS

Answer

Drop and recreate the clustered columnstore index

Answer

Reorganize the clustered columnstore index

Question 14

You have an Azure SQL Database that is part of an elastic pool. You notice that the pool's eDTU consumption is consistently high, and some databases are experiencing resource contention. You need to ensure that a critical database always gets a minimum amount of resources. What should you configure?

Accepted Answer

Configure per-database min eDTU for the critical database. Per-database min eDTU guarantees a minimum level of resources for a specific database within an elastic pool, ensuring that critical databases get the required resources even when the pool is under high utilization. Option A is incorrect because per-database max eDTU only limits the maximum resource usage, not guaranteeing any minimum. Option B is incorrect because increasing the pool's eDTU provides more total resources but does not guarantee that any single database receives a specific amount. Option C is incorrect because while moving to a dedicated service tier would guarantee resources, it is more expensive and unnecessary if the critical database only needs a minimum resource guarantee within the existing pool.

Answer

Configure per-database max eDTU for the critical database

Answer

Increase the eDTU of the elastic pool

Answer

Move the critical database to a dedicated service tier

Question 15

You are monitoring an Azure SQL Managed Instance using dynamic management views. You want to identify which queries are consuming the most IOPS. Which DMV should you query?

Accepted Answer

sys.dm_exec_query_stats. Sys.dm_exec_query_stats provides per-query execution statistics, including I/O metrics such as total_logical_reads and total_logical_writes, which can be used to identify queries consuming the most IOPS. Option A is incorrect because sys.dm_db_io_stats is not a valid DMV in Azure SQL Managed Instance and does not provide per-query I/O information. Option B is incorrect because sys.dm_os_performance_counters provides OS-level performance counters, not per-query I/O. Option D is incorrect because sys.dm_db_index_usage_stats tracks index usage statistics, not query I/O.

Answer

sys.dm_db_io_stats

Answer

sys.dm_os_performance_counters

Answer

sys.dm_db_index_usage_stats