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

Question 1

Drag and drop the steps to configure transparent data encryption (TDE) for an Azure SQL Database using a customer-managed key in Azure Key Vault in the correct order.

Accepted Answer

Create Azure Key Vault and key, then grant Key Vault permissions to the Azure SQL Database server, then enable TDE with customer-managed key, then select the key and save.. First set up Key Vault with a key, grant permissions, then enable TDE with customer-managed key, select the key, and save.

Answer

Grant Key Vault permissions to the Azure SQL Database server, then create Azure Key Vault and key, then enable TDE with customer-managed key, then select the key and save.

Answer

Enable TDE with customer-managed key, then create Azure Key Vault and key, then grant Key Vault permissions to the Azure SQL Database server, then select the key and save.

Answer

Create Azure Key Vault and key, then enable TDE with customer-managed key, then grant Key Vault permissions to the Azure SQL Database server, then select the key and save.

Question 2

Match each Azure SQL Database backup type to its description.

Accepted Answer

Full backup: Contains all data in the database.. These are the three types of backups used for point-in-time restore in Azure SQL Database.

Answer

Full backup: Contains only changes since the last full backup.

Answer

Differential backup: Contains all committed transactions since the last full backup.

Question 3

Match each Azure SQL Database monitoring metric to its meaning.

Accepted Answer

DTU percentage: Measures the percentage of DTU limit used by the database.. These metrics are used to monitor resource usage and performance in Azure SQL Database.

Answer

DTU percentage: Measures the percentage of CPU limit used by the database.

Answer

CPU percentage: Measures the percentage of data I/O limit used by the database.

Answer

Data IO percentage: Measures the percentage of DTU limit used by the database.

Question 4

You have a SQL Managed Instance that hosts a critical OLTP database. You notice that the average query wait time has increased significantly over the past hour. You need to identify the top resource waits. What should you use?

Accepted Answer

sys.dm_os_wait_stats. C is correct because sys.dm_os_wait_stats is the dynamic management view that aggregates wait statistics across all sessions in the SQL Server instance, including SQL Managed Instance. It provides cumulative wait times categorized by wait type (e.g., PAGEIOLATCH, LCK_M_S), making it the appropriate tool to identify top resource waits when average query wait time increases.

Answer

sys.dm_exec_query_stats

Answer

Query Store Wait Stats in SSMS

Answer

sys.dm_db_index_usage_stats

Question 5

You manage an Azure SQL Database with the Premium service tier. You need to reduce storage costs by automatically deleting old audit logs that are older than 90 days. What should you configure?

Accepted Answer

Configure a lifecycle management policy on the Azure Storage account that stores the audit logs.. Azure SQL Database audit logs are stored in Azure Storage. A lifecycle management policy on the storage account can automatically delete blobs older than a specified number of days, such as 90 days, reducing storage costs. Option A is incorrect because Extended Events are not designed for automated deletion of audit logs. Option B is incorrect because backup retention is unrelated to audit logs. Option C is incorrect because the audit retention policy in Azure SQL Database's server audit settings only controls how long audit records are retained in the database, not the actual deletion of stored logs.

Answer

Create an Extended Events session that filters and deletes old audit events.

Answer

Reduce the backup retention period to 90 days.

Answer

Set the audit retention policy to 90 days in the server's audit settings.

Question 6

You are reviewing an Azure SQL Database server's vulnerability assessment settings. The exhibit shows the current configuration. A recent security audit requires that vulnerability assessment scans be enabled and that results be retained for at least 90 days. What should you do?

Accepted Answer

Change state to Enabled and set retentionDays to 90.. The vulnerability assessment must be enabled (state: Enabled) and retentionDays must be set to at least 90 to meet the audit requirement. The exhibit shows state as Disabled and retentionDays as 30. Option A is wrong because adding email addresses only affects notifications, not the scan state or retention. Option B is wrong because retentionDays of 90 with state Disabled means scans are not running. Option D is wrong because disabledAlerts control which alerts are suppressed; removing them does not enable the scan or change retention.

Answer

Add additional email addresses to ensure notification.

Answer

Change retentionDays to 90 and keep the state as Disabled.

Answer

Remove the disabledAlerts entries to enable all alerts.

Question 7

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.. 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 8

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 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 B is wrong because checking wait statistics is lower-level; Intelligent Insights already provides a diagnosis. Option C is wrong because Query Store may not be enabled by default and is not the first step.

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 9

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 10

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 11

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.. The exhibit shows that the private endpoint is configured with a network ACL that has a deny-all default action and an explicit allow rule for the specific subnet in VNet1. Since private endpoints use network policies (like NSG rules) to filter traffic, and the ACL already denies all traffic except the allowed subnet, no changes are needed. The configuration meets the requirement because the private endpoint's network ACLs are evaluated in order, and the explicit allow for the subnet overrides the default deny for all other traffic.

Answer

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

Answer

Set ignoreMissingVnetServiceEndpoint to true.

Answer

Change defaultAction to Allow.

Question 12

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. Query Store (Option B) captures a history of query execution plans and runtime statistics, allowing you to identify queries with high CPU, I/O, or duration. sys.dm_exec_query_stats (Option C) returns aggregate performance statistics for cached query plans, including total CPU time and logical reads, which directly points to resource-intensive queries. Both are valid sources for diagnosing high DTU consumption in Azure SQL Database.

Answer

sys.dm_os_wait_stats

Answer

sys.dm_db_index_usage_stats

Answer

sys.dm_io_virtual_file_stats

Question 13

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 14

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 15

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.. The current yearly retention is P3Y (3 years), which is insufficient for the 10-year compliance requirement. Increasing it to P10Y retains yearly backups for 10 years. Option B is incorrect because weekOfYear specifies which week's backup is retained for yearly retention, not the retention period. Option C is incorrect because monthly retention at P120M (120 months = 10 years) would retain 120 monthly backups, but the requirement is for yearly retention, and monthly retention doesn't cover full 10-year compliance on its own. Option D is incorrect because weekly retention at P10W (10 weeks) is far less than 10 years.

Answer

Change the weekOfYear to 10.

Answer

Increase the monthly retention to P120M.

Answer

Increase the weekly retention to P10W.