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

Question 1

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 JSON shows 'isAzureMonitorTargetEnabled' is true, indicating audit logs are sent to Azure Monitor. Option B is incorrect because 'state' is 'Enabled', meaning auditing is enabled. Option A is incorrect because 'isStorageSecondaryKeyInUse' is false. Option C is incorrect because 'retentionDays' is 90, not 30.

Answer

Audit logs are written using the secondary storage key

Answer

Auditing is currently disabled

Answer

Audit logs are retained for 30 days

Question 2

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 3

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. RetentionDays set to 0 disables retention, meaning audit logs will not be retained in storage. Option A is wrong because state Enabled is fine. Option B is wrong because emailAddresses is provided. Option D is wrong because emailAccountAdmins true is fine.

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 4

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 5

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 6

You need to configure monitoring for an Azure SQL Database to meet the following requirements: 
- Alert when average DTU consumption exceeds 90% for 10 minutes.
- Track failed logins.
- Analyze query performance over the last 30 days.
Which THREE Azure services or features should you use? (Choose three.)

Accepted Answer

SQL auditing. (SQL auditing) is correct because it logs failed login attempts. Option C (Query Store) is correct as it retains query performance data for historical analysis (e.g., 30 days). Option D (Azure Monitor metric alerts) is correct because it can monitor metrics like DTU and trigger alerts when thresholds are exceeded. Option B (Automatic tuning) is wrong because it optimizes query execution plans, not monitoring. Option E (Azure SQL Assessment) is wrong; it provides best-practice recommendations, not monitoring.

Answer

Automatic tuning

Answer

Azure SQL Assessment

Question 7

You manage an Azure SQL Database that is experiencing higher than expected DTU consumption. You need to identify which queries are consuming the most resources. Which dynamic management view should you query?

Accepted Answer

Query sys.dm_exec_query_stats. sys.dm_exec_query_stats returns aggregate performance statistics for cached query plans, including CPU time, logical reads, and duration, making it ideal for identifying queries that consume the most resources in Azure SQL Database. Option A (sys.dm_exec_requests) shows only currently executing requests, not historical data. Option B (sys.dm_os_wait_stats) provides server-level wait statistics, not per-query resource usage. Option D (sys.dm_db_resource_stats) shows database-level DTU consumption per minute, not per-query details.

Answer

Query sys.dm_exec_requests

Answer

Query sys.dm_os_wait_stats

Answer

Query sys.dm_db_resource_stats

Question 8

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) in Azure Monitor provides a consolidated monitoring solution for Azure SQL databases and managed instances, including detailed storage usage per database. Option A is wrong because Query Performance Insight focuses on query-level metrics like query duration and CPU usage, not per-database storage. Option B is wrong because Automatic tuning is a feature that automatically indexes and optimizes query plans, not a monitoring tool. Option C is wrong because Intelligent Insights provides proactive performance diagnostics and anomaly detection, but does not directly per-database storage monitoring. Therefore, only Azure SQL Analytics meets the requirement.

Answer

Query Performance Insight

Answer

Automatic tuning

Answer

Intelligent Insights

Question 9

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 10

You are troubleshooting a performance degradation on an Azure SQL Database. You notice that the database is hitting the maximum DTU limit frequently. Which action should you take first to reduce DTU consumption?

Accepted Answer

Use Query Performance Insight to identify and optimize top resource-consuming queries. Query Performance Insight helps identify the top resource-consuming queries. Optimizing those queries is the most direct way to reduce DTU consumption. Option A is wrong because increasing the log rate limit does not reduce DTU consumption. Option B is wrong because scaling up increases cost and may not address the root cause. Option D is wrong because rebuilding all indexes can increase DTU temporarily.

Answer

Increase the log rate limit

Answer

Scale up the database to a higher service tier

Answer

Rebuild all indexes in the database

Question 11

You are reviewing an Azure Resource Manager template snippet for configuring long-term backup retention for an Azure SQL Database. The deployment fails with an error indicating the storage account is not accessible. What is the most likely cause?

Accepted Answer

The SAS token has expired or is invalid. The error indicates that the storage account is not accessible. In the template, a SAS token is used to authenticate access to the storage container for long-term backup retention. The most common reason for this error is that the SAS token has expired or is invalid, as SAS tokens have an expiration date. Therefore, Option C is correct. Option A is incorrect because the server name mismatch would cause a different error. Option B is incorrect because the URI format may be valid, but the token is the issue. Option D is incorrect because cross-region storage is supported for long-term retention.

Answer

The server name in the template does not match the actual server

Answer

The storage container URI is incorrectly formatted

Answer

The database is not in the same region as the storage account

Question 12

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. Scaling up to a higher service tier (e.g., S4) provides more CPU resources, which directly addresses the high CPU usage. Option D is correct. Option A is incorrect because enabling read scale-out does not increase CPU capacity. Option B is incorrect because automatic tuning may provide recommendations, but it is not the first action to take for immediate CPU pressure. Option C is incorrect because scaling down to a lower tier would reduce CPU resources, making the problem worse.

Answer

Enable read scale-out

Answer

Wait for Automatic tuning to provide recommendations

Answer

Scale down to a lower service tier

Question 13

You need to configure alerts for an Azure SQL Database to notify the operations team when the database exceeds 80% DTU consumption for more than 10 minutes. What should you use?

Accepted Answer

Create a metric alert in Azure Monitor. Azure Monitor can create metric alerts based on DTU consumption. Option D is correct. Option A is wrong because SQL Agent cannot send alerts based on DTU. Option B is wrong because Azure SQL Auditing is for compliance. Option C is wrong because Azure Advisor provides recommendations but not real-time alerts.

Answer

Configure a SQL Agent alert

Answer

Configure Azure SQL Auditing

Answer

Use Azure Advisor recommendations

Question 14

You have an Azure SQL Database that uses the Hyperscale service tier. You notice that the log rate is frequently throttled. Which configuration change can help reduce log rate throttling?

Accepted Answer

Increase the log rate limit by scaling up the service level objective. Increasing the log rate limit by scaling up the service level objective (SLO) allows more transactions per second, reducing log rate throttling. Option B is correct. Option A is wrong because increasing max degree of parallelism does not directly affect log rate; it may even increase log generation. Option C is wrong because backup retention period does not influence log rate. Option D is wrong because adding compute replicas does not increase the log rate limit; log rate is tied to the primary replica's SLO.

Answer

Increase max degree of parallelism

Answer

Reduce backup retention period

Answer

Add more compute replicas

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