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

Question 1

You are managing an Azure SQL Database that uses Intelligent Insights. You receive an alert that there is a performance issue with a specific query. You need to analyze the root cause. What should you use?

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

Accepted Answer

Query Store to review query execution plans and wait statistics. Query Store is the correct tool because it captures historical execution plans, runtime statistics, and wait statistics for individual queries, allowing you to pinpoint the root cause of a performance regression. Intelligent Insights provides high-level diagnostics but not the granular per-query plan and wait data needed for deep analysis of a specific query issue.

Answer

Intelligent Insights report

Answer

Automatic Tuning recommendations

Answer

Azure Monitor metrics for the database

Question 2

You are monitoring an Azure SQL Database and notice that the average CPU usage is 80% and the average data IO percentage is 70%. You need to identify the most likely cause of the high resource usage. What should you check first?

Accepted Answer

Use Query Store to identify top resource-consuming queries. High average CPU (80%) and data IO (70%) suggest that the database is under sustained load from inefficient or resource-intensive queries. Query Store captures query execution plans, runtime statistics, and resource consumption per query, making it the fastest way to pinpoint the top resource consumers. Checking Query Store first allows you to identify the specific queries driving CPU and IO, which is the most direct diagnostic step.

Answer

Check for long-running maintenance tasks

Answer

Check for connection pooling issues

Answer

Check for blocking and deadlocks

Question 3

You manage an Azure SQL Database that uses a Serverless compute tier. You notice that during idle periods, the database auto-pauses and then auto-resumes when a connection is made. However, users report that the first query after a pause is slow. You need to improve the performance of the first query. What should you do?

Accepted Answer

Disable auto-pause for the serverless database. The slow first query after auto-resume is caused by the cold-start latency of the serverless compute tier, which includes provisioning resources and warming the buffer pool. Disabling auto-pause ensures the database remains online and the buffer pool stays populated, eliminating the cold-start delay for the first query.

Answer

Increase the maximum vCores

Answer

Create a SQL Agent job to ping the database every hour

Answer

Enable Query Store

Question 4

You have an Azure SQL Database that is experiencing high wait times on RESOURCE_SEMAPHORE waits. You need to identify the root cause. What should you check?

Accepted Answer

Queries with large memory grants. RESOURCE_SEMAPHORE waits indicate memory grant pressure, typically caused by queries requesting large memory grants. Option D is correct. Option A is incorrect because blocking and deadlocks produce LCK_M_* waits, not RESOURCE_SEMAPHORE. Option B is incorrect because high CPU usage leads to SOS_SCHEDULER_YIELD waits. Option C is incorrect because disk I/O bottlenecks cause PAGEIOLATCH waits.

Answer

Blocking and deadlocks

Answer

High CPU usage

Answer

Disk I/O bottlenecks

Question 5

You are monitoring an Azure SQL Database. You need to identify which two metrics are most important for detecting a memory pressure issue. Which TWO should you select?

Accepted Answer

Memory grants pending. Memory grants pending (B) is a key indicator of memory pressure because it counts the number of queries waiting for a workspace memory grant; a sustained nonzero value means SQL Server cannot satisfy concurrent memory requests, directly signaling memory contention. Page life expectancy (C) measures how long pages stay in the buffer pool, and a low or declining PLE indicates that data pages are being evicted too quickly, which is a classic symptom of buffer pool memory pressure. Together these two metrics isolate memory-related stress rather than I/O or CPU behavior. Log IO percentage (A) and Data IO percentage (E) reflect storage throughput/latency, and CPU percentage (D) reflects processor utilization, so none of them directly diagnose memory pressure.

Answer

Log IO percentage

Answer

CPU percentage

Answer

Data IO percentage

Question 6

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?

Accepted Answer

Configure a new long-term retention policy that retains monthly backups for 7 years.. Azure SQL Managed Instance long-term retention (LTR) policies are defined per-database and specify separate weekly, monthly, and yearly retention periods; you can set the monthly retention to 7 years while removing or shortening the other tiers. This directly satisfies the compliance requirement (monthly backups kept 7 years) while eliminating the storage cost of the unnecessary 10-year policy. LTR backups are stored in Azure Blob storage and billed separately from the instance, so right-sizing the policy is the correct cost-optimization action.

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 7

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 8

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 9

You are managing an Azure SQL Database that uses Intelligent Insights. You receive an alert that there is a performance issue with a specific query. You need to analyze the root cause. What should you use?

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

Accepted Answer

Query the Query Store to compare query performance before and after the plan change.. Query Store is the built-in feature that captures query plan history and runtime statistics over time, so it can directly compare a query's performance before and after an automatic plan correction was applied. Automatic tuning relies on Query Store as its data source, and the 'Automatic Tuning' recommendation history is also surfaced through Query Store views such as sys.query_store_plan and sys.query_store_runtime_stats. This makes Query Store the authoritative place to verify the improvement.

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 10

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 11

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 12

Refer to the exhibit. You have configured the automatic tuning policy as shown. After a week, you notice that an index has been dropped automatically, causing a critical query to run slowly. What should you do to prevent this in the future while still benefiting from automatic tuning?

Accepted Answer

Set the dropIndex option state to Disabled in the tuning policy.. The dropIndex option in the automatic tuning policy controls whether SQL Server/Azure SQL can automatically drop indexes it deems unused. Setting dropIndex to Disabled preserves the createIndex and forceLastGoodPlan tuning actions while preventing the risky automatic drop that broke the critical query. This is the targeted fix that keeps automatic tuning benefits without the destructive behavior.

Answer

Manually create the dropped index and mark it as a required index.

Answer

Enable Query Store to track index usage.

Answer

Disable automatic tuning entirely.

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

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 in Azure SQL Database and SQL Managed Instance automatically identifies and fixes plan regressions by forcing the last known good plan, and can also create/drop indexes. It directly addresses the requirement to automatically identify and fix regressed query plans without manual intervention.

Answer

Query Store

Answer

Intelligent Insights

Answer

Database Advisor for SQL Database

Question 15

You are configuring performance monitoring for Azure SQL Managed Instance. You need to collect and analyze query performance data with minimal overhead. Which solution should you use?

Accepted Answer

Query Store. Query Store is built-in and designed for low overhead query performance monitoring. Option B is wrong because Azure Monitor metrics provide resource-level metrics, not query-level details. Option C is wrong because Extended Events can have higher overhead and is more for custom event collection. Option D is wrong because SQL Server Profiler is deprecated and has high overhead.

Answer

Azure Monitor metrics

Answer

Extended Events

Answer

SQL Server Profiler