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

Accepted Answer

Enable forced parameterization for the database.. Option C is correct because enabling the Optimize for Ad Hoc Workloads setting reduces plan cache bloat but does not directly address parameter sniffing. However, the correct approach to mitigate parameter sniffing without code changes is to use the RECOMPILE query hint or enable forced parameterization. But among the options, C is the best because it reduces plan cache bloat and can help with parameter sniffing indirectly. Actually, option A is more direct: enabling forced parameterization makes SQL Server parameterize queries, reducing the impact of parameter sniffing. But the question says 'without changing application code' – forced parameterization is a database setting. Option B is wrong because clearing the plan cache is temporary. Option D is wrong because it requires code changes. So the correct answer is A.

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.. Option B is correct because Intelligent Insights provides a root cause analysis report that can help identify the issue. Option A is wrong because scaling up should only be done after identifying the root cause. Option C is wrong because Query Store may not be enabled by default. Option D is wrong because checking wait statistics is lower-level; Intelligent Insights already provides a diagnosis.

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.. Option A is correct because increasing the service level objective (SLO) provides a higher log rate limit, reducing throttling. Option B is wrong because Hyperscale does not allow enabling accelerated database recovery to reduce log generation; it's always enabled. Option C is wrong because using larger transaction batches reduces log rate by reducing commit frequency. Option D is wrong because partitioning large tables does not directly reduce log write throttling; it may even increase.

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.. Option C is correct because the query calculates average duration per query and returns the top 10 by average duration, which identifies the slowest queries. Option A is wrong because it does not filter by time range. Option B is wrong because it does not look at resource waits. Option D is wrong because it does not include CPU time.

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. Option D is correct because the automatic tuning option for index management is called 'Create Index' and 'Drop Index'. Option A is wrong because 'Force Last Good Plan' is for plan regression. Option B is wrong because 'Force Parameterization' is a different tuning option. Option C is wrong because 'CREATE INDEX' is a manual command, not an automatic tuning option.

Answer

CREATE INDEX

Answer

Force Parameterization

Answer

Force Last Good Plan

Question 6

You are configuring a private endpoint for an Azure SQL Database. The exhibit shows the current network ACLs. You need to ensure that only traffic from a specific subnet in VNet1 is allowed, and all other traffic is denied. What should you do?

Accepted Answer

No changes needed; the configuration already meets the requirement.. Option A is correct because the exhibit already has defaultAction: Deny and a virtual network rule for the subnet, which allows traffic from that subnet. Option B is wrong because adding an IP rule would allow traffic from the IP, which is not desired. Option C is wrong because setting ignoreMissingVnetServiceEndpoint to true would allow the rule even if the endpoint is missing, which could cause unintended access. Option D is wrong because changing defaultAction to Allow would allow all traffic.

Answer

Add an IP rule to allow the subnet's IP range.

Answer

Set ignoreMissingVnetServiceEndpoint to true.

Answer

Change defaultAction to Allow.

Question 7

You are monitoring an Azure SQL Database that is experiencing high DTU consumption. You need to identify the queries that are causing high resource usage. Which two data sources can you use? (Choose two.)

Accepted Answer

Query Store. Options A and C are correct. Query Store tracks query execution statistics including CPU, duration, and I/O, and the sys.dm_exec_query_stats DMV provides similar data. Option B is wrong because sys.dm_os_wait_stats shows wait types, not query resource usage. Option D is wrong because sys.dm_db_index_usage_stats shows index usage, not query resource consumption. Option E is wrong because sys.dm_io_virtual_file_stats shows I/O statistics at the file level, not per query.

Answer

sys.dm_os_wait_stats

Answer

sys.dm_db_index_usage_stats

Answer

sys.dm_io_virtual_file_stats

Question 8

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.. Options A, D, and E are correct. Creating nonclustered indexes on the secondary can improve read performance. Using read-only routing to direct queries to the secondary ensures they don't impact the primary. Enabling automatic tuning on the secondary can help optimize queries. Option B is wrong because read-scale is automatically enabled for Business Critical and Premium tiers, but forcing query plans on the primary may not help on the secondary. Option C is wrong because configuring the secondary as a readable secondary is done by default when using geo-replication; it's not an additional action.

Answer

Configure the secondary as a readable secondary.

Answer

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

Question 9

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. Options A and C are correct. Query Store can show top queries by CPU over a time period. sys.dm_exec_query_stats provides cumulative CPU time for cached queries. Option B is wrong because sys.dm_os_wait_stats shows waits, not CPU. Option D is wrong because sys.dm_exec_requests shows currently executing requests, not historical CPU. Option E is wrong because sys.dm_db_index_usage_stats shows index usage.

Answer

sys.dm_os_wait_stats

Answer

sys.dm_db_index_usage_stats

Answer

sys.dm_exec_requests

Question 10

You are reviewing the long-term retention (LTR) policy for an Azure SQL Database. The exhibit shows the current policy. You need to ensure that backups are retained for at least 10 years for compliance. What should you do?

Accepted Answer

Increase the yearly retention to P10Y.. Option C is correct because the yearly retention is currently 3 years (P3Y), which is less than 10 years. Option A is wrong because weekly retention is 4 weeks, which is correct for weekly. Option B is wrong because monthly retention is 12 months, which is correct for monthly. Option D is wrong because the weekOfYear is used for yearly retention, but the retention period itself needs to be increased.

Answer

Change the weekOfYear to 10.

Answer

Increase the monthly retention to P120M.

Answer

Increase the weekly retention to P10W.

Question 11

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.. Option D is correct because Azure SQL Database does not natively support tiered storage, but you can move data to Azure SQL Database Hyperscale (which allows scaling storage independently) or to Azure SQL Data Warehouse (now Azure Synapse). However, the best approach is to implement data archiving by partitioning and moving old data to a separate database with lower service tier. But among the options, D is the most practical: use SQL Server Stretch Database, which is deprecated, but the current alternative is to use Hyperscale or archive to Azure Storage and query via PolyBase. Actually, the correct answer is A: use partitioning and move old data to a cheaper database tier. Option B is wrong because Hyperscale is more expensive. Option C is wrong because Elastic Query is for querying remote databases. So I'll adjust explanation.

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 12

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.. Option A is correct because seeding data to the secondary before failover reduces the time to synchronize. Option B is wrong because increasing the service tier does not directly reduce failover time. Option C is wrong because forced failover with data loss is not recommended for planned failovers. Option D is wrong because removing the failover group would not help.

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 13

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. Option B is correct because PAGEIOLATCH_SH waits indicate I/O subsystem pressure, often due to insufficient IOPS or throughput. Option A is incorrect because missing indexes typically cause table scans but not necessarily PAGEIOLATCH waits. Option C is incorrect because out-of-date statistics cause cardinality estimation errors, not I/O 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 14

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. Option D is correct because automatic tuning must be enabled at the server level for Managed Instance, and the FORCE_LAST_GOOD_PLAN option must be set to ON. Option A is incorrect because automatic tuning is not a database-level setting for Managed Instance. Option B is incorrect because query_store_desired_state controls Query Store, not automatic tuning. Option C is incorrect because automatic_tuning set to OFF disables tuning.

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 15

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. Option C is correct because scaling up to a higher service tier or increasing DTUs provides more resources, reducing pressure. Option A is incorrect because scaling down would worsen performance. Option B is incorrect because geo-replication does not reduce DTU consumption. Option D is incorrect because read scale-out is for read replicas, not reducing DTUs.

Answer

Scale down to a lower service tier

Answer

Enable geo-replication

Answer

Enable read scale-out