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

Question 1

Your Azure SQL Managed Instance is configured with a long-term backup retention policy of 10 years. You need to reduce storage costs while still meeting a compliance requirement to retain monthly backups for 7 years. What should you do?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Configure a new long-term retention policy that retains monthly backups for 7 years.. Configuring a new long-term retention (LTR) policy that retains monthly backups for 7 years meets the compliance requirement while reducing storage costs compared to keeping weekly/daily backups for 10 years. LTR allows you to specify retention periods for weekly, monthly, and yearly backups; setting monthly backups to 7 years satisfies the requirement without extra cost. Option B is incorrect because disabling LTR entirely would lose the compliance capability. Option C is incorrect because point-in-time restore (PITR) retention is limited to 35 days, not 7 years. Option D is incorrect because migrating to Azure SQL Database does not inherently reduce costs and may introduce other issues.

Answer

Disable long-term backup retention and rely solely on point-in-time restore backups.

Answer

Set the point-in-time restore retention period to 7 years.

Answer

Migrate the database to Azure SQL Database and use geo-redundant storage.

Question 2

You are the database administrator for a hybrid environment with on-premises SQL Server 2022 and Azure SQL Database. All databases use the AdventureWorks schema. You notice that a critical stored procedure runs slower on Azure SQL Database than on-premises. Both have identical indexes and statistics. What is the most likely cause?

Accepted Answer

The version of the cardinality estimator differs between environments.. The cardinality estimator (CE) version can differ between on-premises SQL Server and Azure SQL Database, even with identical indexes and statistics. The CE version is determined by the database compatibility level, and Azure SQL Database may have a different default compatibility level than on-prem, leading to different query plans and performance. Option B is incorrect because DTU vs vCore is a purchasing model that does not inherently slow queries; it affects resource limits but not plan choices. Option C is incorrect because automatic tuning in Azure SQL Database typically helps performance, and index fragmentation levels would be similar with identical indexes. Option D is incorrect because intelligent query processing features are available on both platforms and generally beneficial, not detrimental.

Answer

Azure SQL Database uses a different resource governance model (DTU vs vCore).

Answer

The index fragmentation level is higher on Azure SQL Database due to automatic tuning.

Answer

Azure SQL Database has intelligent query processing features that are not available on-premises.

Question 3

You are monitoring an Azure SQL Database that hosts a financial application. You notice that the average DTU consumption is 20%, but occasionally spikes to 95% for 5-minute intervals. Users report slow response times during these spikes. You need to ensure consistent performance without over-provisioning resources. What should you do?

Accepted Answer

Identify and optimize the queries running during the spike periods, possibly rescheduling a heavy ETL job.. The issue is not a consistent resource shortage but periodic spikes caused by specific queries, likely from a heavy ETL job. By identifying and optimizing those queries or rescheduling the job, you can eliminate the spikes without permanently scaling up resources, which would waste cost and capacity. This aligns with the DP-300 focus on performance tuning and resource optimization rather than blind scaling.

Answer

Migrate the database to the Hyperscale service tier.

Answer

Scale the database to a higher service tier to absorb the spikes.

Answer

Enable Query Store and use the Regressed Queries feature to find slow queries.

Question 4

Your Azure SQL Database is configured with Active Geo-Replication to a secondary region for disaster recovery. During a routine failover drill, you notice that after failover, the application cannot connect to the new primary because the login credentials fail. The logins are contained in the master database. What is the most likely cause?

Accepted Answer

The SQL logins in the master database are not replicated to the secondary server.. When Active Geo-Replication is configured for Azure SQL Database, the secondary server is a separate logical server in a different region. The master database, which contains server-level logins, is not replicated as part of geo-replication; only the user databases are replicated. Therefore, after a failover, the new primary server does not have the server-level logins from the original primary, causing authentication failures for applications using those logins.

Answer

The DNS name of the secondary server changed after failover.

Answer

The firewall rules on the secondary server do not allow connections from the application IP.

Answer

The application uses contained database users, which are not replicated.

Question 5

You are managing an Azure SQL Database that has Automatic Tuning enabled. You receive an alert that a query plan regression was detected and a plan correction was automatically applied. You want to verify the performance improvement. What should you use?

Accepted Answer

Query the Query Store to compare query performance before and after the plan change.. Query Store provides detailed query performance data, including plan regressions and improvements. Option A is wrong because sys.dm_exec_query_stats gives current performance metrics but does not provide historical comparison. Option B is wrong because Azure Monitor alert details only notify that a regression occurred, not the performance improvement. Option D is wrong because the automatic tuning log shows actions taken but not detailed performance metrics for comparison.

Answer

Use sys.dm_exec_query_stats to view current performance.

Answer

Review the Azure Monitor alert details.

Answer

Check the automatic tuning log in the Azure portal.

Question 6

Your Azure SQL Database is experiencing high CPU usage. You suspect a specific query is causing the issue. You have enabled Query Store. How can you identify the query that has consumed the most cumulative CPU time over the last hour?

Accepted Answer

Use the Query Store Top Resource Consuming Queries report and filter by the last hour.. The Query Store Top Resource Consuming Queries report allows you to filter by time interval and shows cumulative CPU time, making it easy to identify the query consuming the most CPU over the last hour. Option A is wrong because sys.dm_exec_query_stats shows cached query plans and statistics since the last plan compilation, not cumulative over a specific time window. Option C is wrong because sys.dm_exec_requests shows currently running queries, not historical cumulative CPU usage. Option D is wrong because the Regressed Queries report shows queries whose performance has changed due to plan changes, not simply high CPU consumption.

Answer

Use sys.dm_exec_query_stats to sort by total_worker_time.

Answer

Use sys.dm_exec_requests to find queries with high CPU.

Answer

Use the Query Store Regressed Queries report.

Question 7

You are optimizing a data warehouse workload on Azure SQL Database. The workload involves large batch inserts and nightly aggregations. You notice that the transaction log is growing excessively during the batch inserts, causing performance degradation. You need to reduce log growth without affecting data consistency. What should you do?

Accepted Answer

Use bulk insert operations with TABLOCK hint to enable minimal logging.. Using minimally logged operations (e.g., bulk insert with TABLOCK) reduces log space for large imports under the full recovery model, but requires specific conditions. Option A is wrong because simple recovery model is not supported in Azure SQL Database (only FULL, BULK_LOGGED is not available). Option C is wrong because partitioning does not reduce log growth. Option D is wrong because increasing log size only accommodates growth, does not prevent it.

Answer

Change the database recovery model to Simple.

Answer

Create a partition function and scheme to spread the inserts.

Answer

Increase the maximum log size of the database.

Question 8

Your Azure SQL Database has a recurring job that rebuilds indexes weekly. After a recent change, the job is taking much longer to complete. You suspect that the index fragmentation is higher than usual. What is the most efficient way to check index fragmentation across the database?

Accepted Answer

Use the sys.dm_db_index_physical_stats dynamic management function.. Sys.dm_db_index_physical_stats. This DMV provides fragmentation details (e.g., avg_fragmentation_percent) for all indexes in a database with minimal overhead when using limited scanning. Option A (sys.indexes) does not include fragmentation statistics. Option C (SET SHOWPLAN_XML ON) shows query execution plans, not index fragmentation. Option D (sys.dm_db_missing_index_details) suggests new indexes to improve performance, but does not report existing index fragmentation.

Answer

Query sys.indexes to check the fragmentation percentage.

Answer

Use SET SHOWPLAN_XML ON and run sample queries.

Answer

Use the sys.dm_db_missing_index_details DMV.

Question 9

You are troubleshooting a performance issue on an Azure SQL Database. Which TWO actions should you prioritize to identify the root cause of high resource consumption?

Accepted Answer

Review the Query Store Top Resource Consuming Queries report.. To identify the root cause of high resource consumption, you should use diagnostic tools that analyze query performance. The Query Store's Top Resource Consuming Queries report (D) provides historical insight into which queries consumed the most resources. Additionally, querying sys.dm_exec_query_stats (E) allows you to find queries with high total_worker_time, indicating CPU-intensive queries. Options A (rebuilding indexes) and B (changing recovery model) are corrective actions, not diagnostic. Option C (scaling to a higher service tier) is a reactive mitigation that does not identify the root cause. Therefore, options D and E are the correct prioritized actions.

Answer

Rebuild all indexes to improve query performance.

Answer

Change the database recovery model to Simple.

Answer

Scale the database to a higher service tier to mitigate the issue.

Question 10

You are optimizing an Azure SQL Database that runs a heavy reporting workload. The database uses the Business Critical service tier. Which THREE configuration changes can improve query performance for reporting queries without significantly impacting OLTP operations?

Accepted Answer

Create nonclustered columnstore indexes on large reporting tables.. Creating nonclustered columnstore indexes (option B) improves aggregation and reporting query performance by using columnar storage and batch processing. Configuring a read-scale replica (option C) offloads reporting queries to a secondary replica, reducing contention on the primary for OLTP operations. Enabling result set caching (option E) caches query results in the Premium/Business Critical tiers, reducing repeated reads for static reporting data. Option A (increasing MAXDOP to 8) can lead to parallel query contention and negatively impact OLTP performance, especially on smaller instances. Option D (disabling automatic tuning) removes beneficial plan corrections and is not recommended for improving performance.

Answer

Increase MAXDOP for the database to 8.

Answer

Disable automatic tuning to prevent plan changes.

Question 11

You are monitoring an Azure SQL Database that is experiencing high DTU usage. Which TWO metrics should you examine to determine whether the bottleneck is CPU or I/O?

Accepted Answer

Log write bytes per second.. Average CPU percentage directly measures CPU utilization, indicating a CPU bottleneck when high. Log write bytes per second measures the throughput of transaction log writes, which is a key indicator of I/O activity and can reveal an I/O bottleneck. Therefore, both A and B are correct metrics to diagnose whether the high DTU usage is caused by CPU or I/O.

Answer

Number of deadlocks per second.

Answer

Used storage space in GB.

Answer

Number of active sessions.

Question 12

Refer to the exhibit. You are analyzing query performance using sys.dm_exec_query_stats. Based on the output, which query is the best candidate for optimization to reduce overall CPU usage?

Accepted Answer

Query 2 because it has the highest average CPU per execution.. Query 2 is the best candidate for optimization because it has the highest average CPU per execution, indicating that each run of this query consumes significantly more CPU resources than the others. Reducing the CPU cost per execution for this query will yield the greatest per-execution savings, making it the most efficient target for reducing overall CPU usage.

Answer

Query 1 because it has the highest total_cpu_time.

Answer

All queries should be optimized equally.

Answer

Query 3 because it runs most frequently.

Question 13

Refer to the exhibit. You executed the Azure CLI command to list databases. You need to resume db3 to make it available for connections. Which command should you use?

Accepted Answer

az sql db resume --resource-group rg1 --server server1 --name db3. `az sql db resume` is the command to resume a paused database. Option A is wrong because `az sql db restart` restarts an online database but does not resume a paused one. Option C is wrong because `az sql db start` is not a valid command for Azure SQL Database. Option D is wrong because `az sql db update` can modify properties but cannot resume a paused database; resuming requires a dedicated command.

Answer

az sql db restart --resource-group rg1 --server server1 --name db3

Answer

az sql db start --resource-group rg1 --server server1 --name db3

Answer

az sql db update --resource-group rg1 --server server1 --name db3 --set status=Online

Question 14

Your organization runs a critical e-commerce application on Azure SQL Database. You notice that during peak hours, query performance degrades significantly. You have enabled Intelligent Insights and Query Store. Which diagnostic metric would you analyze first to identify the root cause of the performance degradation?

Accepted Answer

DTU/CPU consumption. DTU/CPU consumption directly correlates with query performance during peak load. With Intelligent Insights and Query Store enabled, analyzing DTU/CPU consumption helps identify resource contention as the primary cause of degradation. Option A (Query store wait statistics) is useful for detailed analysis but is not the first metric to check. Option B (Deadlock count) is not a typical cause of sustained degradation. Option D (Failed connections) indicates availability issues, not performance.

Answer

Query store wait statistics

Answer

Deadlock count

Answer

Failed connections

Question 15

You are a database administrator for a large financial services company. You need to ensure that all queries that read sensitive customer data use an optimized execution plan. What feature should you enable to automatically identify and fix regressed query plans?

Accepted Answer

Automatic Tuning. Automatic Tuning can automatically identify and fix regressed query plans. Option A is wrong because Query Store captures performance data but does not automatically fix plans. Option C is wrong because Intelligent Insights provides monitoring, not automatic fixing. Option D is wrong because Database Advisor for SQL Database gives recommendations but requires manual action.

Answer

Query Store

Answer

Intelligent Insights

Answer

Database Advisor for SQL Database