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

Question 1

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 2

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

Accepted Answer

Query Store. Query Store (A) is correct because it captures query, plan, and runtime statistics that let you identify and fix regressions in a read-heavy workload, and it underpins features like Automatic tuning and Query Performance Insight. Read scale-out (E) is correct because it uses the built-in read-only replica (ApplicationIntent=ReadOnly in the connection string) to offload read queries from the primary, directly improving read-heavy performance. Data masking (B) is a security feature that hides sensitive data and does not improve performance. Long-term retention policy (C) is a backup/auditing capability for compliance, not a performance optimization. Automatic tuning (CREATE INDEX) (D) is a valid performance feature, but it is not one of the two properties the question asks for in this scenario, since it is not marked as correct.

Answer

Data masking

Answer

Long-term retention policy

Answer

Automatic tuning (CREATE INDEX)

Question 3

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. Readable secondary in a failover group. Azure SQL Managed Instance supports auto-failover groups, which allow you to create a readable secondary replica in a different Azure region. This secondary is kept in sync via asynchronous replication and can be used to offload read-only reporting workloads, reducing load on the primary instance. The readable secondary feature is specifically designed for read-scale out scenarios, and it aligns with the requirement that reporting queries are read-only and can tolerate some latency.

Answer

SQL Server Integration Services (SSIS)

Answer

Transaction replication

Answer

Auto-failover group with read-write secondary

Question 4

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 5

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 6

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

Question 7

You are reviewing the audit configuration of an Azure SQL Database using the above JSON from Azure Resource Manager. Based on the exhibit, which of the following is true?

Accepted Answer

Audit logs are sent to Azure Monitor. The Azure SQL Database auditing configuration in ARM JSON uses the 'isAzureMonitorTargetEnabled' property (or a diagnostic setting with a Log Analytics workspace) to send audit logs to Azure Monitor. When this is set to true, audit logs are routed to Azure Monitor rather than only to a storage account. Based on the exhibit showing that configuration, audit logs are sent to Azure Monitor.

Answer

Audit logs are written using the secondary storage key

Answer

Auditing is currently disabled

Answer

Audit logs are retained for 30 days

Question 8

You run the above KQL query in Azure Monitor Log Analytics to investigate performance issues in SalesDB. What is the primary purpose of this query?

Accepted Answer

Identify queries with high average duration. The query filters for queries with average duration > 1000 ms and orders by duration, identifying high-duration queries. Option B is wrong because plan changes are not detected. Option C is wrong because it does not sort by frequency. Option D is wrong because it does not compare across time intervals.

Answer

Identify queries that have had plan changes

Answer

Find the most frequently executed queries

Answer

Compare query performance over different time intervals

Question 9

You are reviewing a deployment template for an Azure SQL Database. The above snippet configures a security alert policy. What is a potential issue with this configuration?

Accepted Answer

The retentionDays is set to 0, which may cause logs to be deleted immediately. In an Azure SQL Database security alert policy, retentionDays set to 0 means alerts are retained for zero days, which effectively deletes them immediately and prevents any historical analysis or auditing. Best practice is to set a retention period (e.g., 30 days or more) to ensure alerts are available for investigation.

Answer

The state is not set to "Disabled"

Answer

The emailAccountAdmins property is set to true

Answer

The emailAddresses array is missing an entry

Question 10

You are tuning an Azure SQL Database that has a heavy write workload. You need to reduce the number of log writes. Which TWO actions should you take? (Choose two.)

Accepted Answer

Use delayed durability for transactions. Options D and E are correct. Option D: Using delayed durability (DELAYED_DURABILITY = ON for transactions) reduces the number of log flush operations, decreasing log writes. Option E: Increasing batch size in INSERT operations reduces the number of log records per row, thereby reducing total log writes. Option A is incorrect because changing the recovery model to SIMPLE is not supported in Azure SQL Database, and even if it were, it would not reduce log writes for heavy write workloads; it only affects transaction log management. Option B is incorrect because minimal logging for bulk operations is not fully supported in Azure SQL Database (only available for specific operations like BULK INSERT against a heap with certain conditions), and it is not a general action to reduce log writes for a heavy write workload. Option C is incorrect because rebuilding indexes during peak hours actually increases log writes due to index rebuild operations.

Answer

Change the database recovery model to SIMPLE

Answer

Use minimal logging for bulk operations

Answer

Rebuild indexes during peak hours

Question 11

You are monitoring an Azure SQL Database and notice that the 'tempdb' database is experiencing contention. Which THREE actions can reduce tempdb contention? (Choose three.)

Accepted Answer

Add multiple tempdb data files. Adding multiple tempdb data files reduces allocation contention on system pages like PFS (Page Free Space), GAM (Global Allocation Map), and SGAM (Shared Global Allocation Map). When multiple sessions concurrently allocate pages in tempdb, a single data file becomes a bottleneck; additional files spread the allocation load across separate file system objects, reducing latch contention.

Answer

Increase the size of tempdb

Answer

Change the collation of tempdb

Question 12

You have an Azure SQL Managed Instance with a large number of databases. You need to monitor the storage space used by each database to proactively manage capacity. Which tool should you use?

Accepted Answer

Azure SQL Analytics (preview). Azure SQL Analytics (preview) is the Azure Monitor-based solution specifically designed to monitor the performance and health of Azure SQL Database and Azure SQL Managed Instance at scale, including storage space consumption per database. It collects metrics such as storage used, DTU/vCore usage, and query statistics across many databases into a single Log Analytics workspace, enabling proactive capacity management. Query Performance Insight, by contrast, focuses on query-level performance tuning rather than storage capacity.

Answer

Query Performance Insight

Answer

Automatic tuning

Answer

Intelligent Insights

Question 13

You need to configure automatic tuning for an Azure SQL Database to automatically identify and fix performance issues. Which two tuning options can be enabled?

Accepted Answer

FORCE LAST GOOD PLAN. Automatic tuning in Azure SQL Database includes two main options: FORCE LAST GOOD PLAN and CREATE INDEX. FORCE LAST GOOD PLAN automatically identifies and forces the last known good query plan when a regression is detected, while CREATE INDEX automatically creates indexes to improve query performance based on workload patterns. Both options help identify and fix performance issues without manual intervention.

Answer

DROP INDEX

Answer

FORCE LAST GOOD PLAN and DROP INDEX

Question 14

You are monitoring an Azure SQL Database and notice that the average CPU usage is consistently above 90%. The database is using the S3 service tier. What should you do first to resolve this performance issue?

Accepted Answer

Scale up to a higher service tier. The S3 service tier is a DTU-based purchasing model tier with fixed compute resources. When average CPU consistently exceeds 90%, the database is compute-bound and the immediate remediation is to increase the allocated compute capacity by scaling up to a higher service tier (e.g., S4, S6, or moving to vCore-based tiers). Scaling up provides more CPU, memory, and IOPS, directly addressing the resource bottleneck. This is the first and most direct action before considering architectural changes like read scale-out or waiting for automatic tuning recommendations.

Answer

Enable read scale-out

Answer

Wait for Automatic tuning to provide recommendations

Answer

Scale down to a lower service tier

Question 15

You need to monitor the performance of a set of Azure SQL Databases from a single dashboard. Which Azure service should you use?

Accepted Answer

Azure SQL Analytics. Azure SQL Analytics provides a unified monitoring solution for multiple Azure SQL databases. Option C is correct. Option A (Azure Monitor Workbooks) is wrong because although it can be used, it requires manual configuration. Option B (Query Performance Insight) is wrong because it is per-database. Option D (Azure Data Studio) is wrong because it is a client tool.

Answer

Azure Monitor Workbooks

Answer

Query Performance Insight

Answer

Azure Data Studio