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

Question 1

You are reviewing an ARM template for Azure SQL Database. The exhibit shows the database settings. You notice the database is not being automatically paused. What is the most likely explanation?

Accepted Answer

The database uses VBS enclaves which are incompatible with serverless. Auto-pause is only supported for General Purpose serverless databases, and the use of VBS enclave (preferredEnclaveType: "VBS") indicates Always Encrypted with secure enclaves, which is not supported with serverless. Option A is wrong because minCapacity 0.5 is valid for serverless. Option B is wrong because licenseType BasePrice does not affect auto-pause. Option C is wrong because autoPauseDelay 60 minutes is valid; the default is 60.

Answer

The minCapacity is set too low

Answer

The autoPauseDelay is set to 60 minutes

Answer

The licenseType is set to BasePrice

Question 2

You are responsible for a set of Azure SQL Databases that are used by different departments in your organization. The databases are deployed in an elastic pool with Standard tier (eDTU 200). Usage patterns show that the marketing database uses high CPU during the day, while the sales database uses high IO at night. You want to optimize costs while ensuring each database gets the resources it needs. What should you do?

Accepted Answer

Configure minimum and maximum eDTU per database in the pool. In an elastic pool, you can set per-database minimum and maximum eDTU limits to guarantee a floor of resources for each database while capping how much any single database can consume. This lets the marketing database get the CPU it needs during the day and the sales database get the IO it needs at night, without one starving the other, and it optimizes cost by keeping the shared pool model.

Answer

Migrate the pool to a vCore-based elastic pool

Answer

Add more databases to the pool to spread the load

Answer

Move each database to a standalone DTU tier

Question 3

You administer a large Azure SQL Database that is used for a SaaS application. The database has a table with over 1 billion rows that is frequently queried by customer ID. The table currently has a clustered index on an identity column and a nonclustered index on customer ID. Queries that filter by customer ID are experiencing high IO and long execution times. You analyze the execution plan and see that the nonclustered index is used, but there are many key lookups. You need to optimize the query performance while minimizing storage overhead. What should you do?

Accepted Answer

Add all queried columns as included columns to the nonclustered index. The nonclustered index on customer ID is being used, but the many key lookups indicate that the query needs columns not present in the index, forcing a lookup back to the clustered index for each row. Adding the queried columns as included columns to the nonclustered index makes it a covering index, eliminating the key lookups while adding only the necessary columns to the index leaf level — a much smaller storage footprint than a columnstore or partitioning solution.

Answer

Create a clustered columnstore index on the table

Answer

Create a filtered index on customer ID for frequent values

Answer

Partition the table by customer ID

Question 4

You are a DBA for a company that uses Azure SQL Database for its customer relationship management (CRM) system. The database is currently in the Standard tier (DTU S2) and is experiencing performance degradation during end-of-month reporting. Reports that aggregate large amounts of data take over 30 minutes to run. You notice that the database's DTU usage averages 80% during these reports, with high IO. You need to improve report performance without significantly increasing cost. The reports are read-only and can tolerate some staleness. What should you do?

Accepted Answer

Increase the service tier to S3 during the end-of-month period. Increasing the service tier to S3 during the end-of-month period provides more DTUs and IO resources, which directly addresses the performance degradation without a permanent cost increase. The cost is only incurred during the reporting period, making it a cost-effective short-term solution. Option D is incorrect because read-only replicas are not supported in the Standard (DTU) S2 tier; they are only available in Premium, Business Critical, and Hyperscale tiers. Therefore, this option is not feasible. Options B and C may help but are not as effective or risk affecting write performance.

Answer

Add nonclustered indexes to the tables used in reports

Answer

Convert the tables to clustered columnstore indexes

Answer

Create a read-only replica and direct reports to it

Question 5

You are a database administrator for a large financial services company. You manage an Azure SQL Database in the Business Critical tier with a failover group configured for disaster recovery. The database has a heavy OLTP workload. You notice that the secondary replica is experiencing high log write latency, impacting the primary's performance due to synchronous commit. You need to minimize the performance impact on the primary while maintaining disaster recovery capabilities. What should you do?

Accepted Answer

Change the failover group to use asynchronous commit mode.. Changing the failover group to asynchronous commit mode decouples the primary's transaction commit from the secondary's log write. In synchronous mode, the primary waits for the secondary to confirm log hardening, which causes performance impact when the secondary has high log write latency. Asynchronous commit allows the primary to commit without waiting, thus minimizing performance impact while still maintaining disaster recovery capabilities (the secondary will apply changes eventually, though with potential data loss if a failover occurs before sync). Option A is incorrect because backup storage redundancy (LRS vs GRS) only affects backup storage, not live log write latency for replication. Option B is incorrect because adding another secondary does not reduce latency on the existing secondary; it could even increase overhead. Option D is incorrect because decreasing the secondary's service tier to General Purpose would reduce its I/O capacity and likely worsen log write latency.

Answer

Change the backup storage redundancy of the secondary replica to locally-redundant storage (LRS).

Answer

Add an additional secondary replica to distribute the log write load.

Answer

Decrease the service tier of the secondary replica to General Purpose.

Question 6

You manage an Azure SQL Database that uses the General Purpose tier. You need to monitor the performance of the database and identify the top resource-consuming queries. You want to use a built-in feature that requires no additional cost. What should you use?

Accepted Answer

Query Store.. Query Store is a built-in, no-cost feature that automatically captures query execution history and helps identify top resource-consuming queries. Option B is wrong because Azure SQL Analytics (preview) is a monitoring solution that requires configuring diagnostic logs and Log Analytics, adding setup and potential cost, rather than being a simple built-in database feature. Option C is wrong because SQL Server Profiler is deprecated and not supported for Azure SQL Database. Option D is wrong because sys.dm_exec_query_stats and sys.dm_exec_sql_text are DMVs that require manual querying and do not provide the built-in, user-friendly query performance tracking of Query Store.

Answer

Azure SQL Analytics (preview) in Azure Monitor.

Answer

SQL Server Profiler.

Answer

sys.dm_exec_query_stats and sys.dm_exec_sql_text DMVs.

Question 7

You are optimizing an Azure SQL Database that runs a reporting workload. The database is in the General Purpose tier. You notice that many queries are performing table scans on large tables. Which TWO actions would most likely improve query performance without increasing costs?

Accepted Answer

Update statistics on the tables.. Option A is correct because stale or missing statistics prevent the query optimizer from accurately estimating cardinality, often forcing scans instead of seeks; running UPDATE STATISTICS (or relying on auto-update statistics) gives the optimizer better row-count estimates and can produce more efficient plans at no extra cost. Option E is correct because creating nonclustered indexes on the columns referenced in WHERE clauses gives the optimizer a covering or seekable access path, converting full table scans on large tables into index seeks or scans of a much smaller structure, which directly improves reporting query performance without changing the service tier. Option B is not appropriate because upgrading to Business Critical increases cost, violating the 'without increasing costs' constraint. Option C is not appropriate because raising MAXDOP to 8 changes parallelism for the whole workload and does not address the root cause of scans, and it can even hurt performance or increase resource usage. Option D is not appropriate because enabling automatic tuning may create or drop indexes and force plans, but it is not a guaranteed, immediate fix for the observed scans and does not by itself ensure the specific WHERE-clause columns are indexed.

Answer

Upgrade to Business Critical tier.

Answer

Increase MAXDOP to 8.

Answer

Enable automatic tuning.

Question 8

You are monitoring an Azure SQL Database. You need to identify which built-in tools can provide real-time performance data without additional cost. Which THREE should you select?

Accepted Answer

Azure Monitor Metrics. Azure Monitor Metrics is a built-in, no-cost feature that collects and stores platform metrics from Azure SQL Database at near-real-time intervals (typically every minute). It provides performance counters such as DTU/CPU usage, data IO, and log write percentages without requiring additional configuration or licensing, making it a correct choice for real-time performance data.

Answer

Performance Insights

Answer

SQL Server Profiler

Question 9

You are managing an Azure SQL Database that is used by a real-time analytics application. The database uses the Hyperscale service tier. You notice that the transaction log rate is consistently high, causing performance degradation. You need to reduce the log generation rate without compromising data durability. What should you do?

Accepted Answer

Increase the service tier to Business Critical.. Increasing the service tier to Business Critical does not reduce the transaction log generation rate; it only improves log write throughput. The log generation rate is workload‑dependent and is not changed by moving to a higher tier. None of the provided options achieve the goal of reducing the log generation rate while maintaining durability. To reduce the rate, you would need to optimize the application (e.g., batching, reducing transactions), which is not offered among the choices.

Answer

Enable compression on transaction log backups.

Answer

Create additional nonclustered indexes on frequently updated tables.

Answer

Increase the backup retention period.

Question 10

You are a database administrator for a company that uses Azure SQL Database. You need to configure a diagnostic setting to send database metrics to a Log Analytics workspace for long-term analysis. The solution should be cost-effective and include metrics like CPU percentage, data IO, and log IO. What should you do?

Accepted Answer

In the Azure portal, add a diagnostic setting for the database to stream 'AllMetrics' to a Log Analytics workspace.. Azure SQL Database diagnostic settings allow streaming platform metrics (CPU percentage, data IO, log IO) directly to a Log Analytics workspace by selecting the 'AllMetrics' category. This is the native, cost-effective mechanism for long-term metric retention and analysis without custom code. It requires no T-SQL jobs or preview features and integrates directly with Azure Monitor.

Answer

Enable Azure SQL Insights (preview) for the database.

Answer

Enable Query Store and configure it to export to Log Analytics.

Answer

Create a T-SQL job that periodically inserts sys.dm_db_resource_stats into a table in Log Analytics.

Question 11

Refer to the exhibit. An Azure SQL Database is experiencing performance degradation. Based on the Extended Events and wait statistics, which is the most likely root cause?

Accepted Answer

I/O subsystem bottleneck. The exhibit shows PAGEIOLATCH_SH and WRITELOG waits dominating the wait statistics, which are classic indicators of I/O subsystem bottlenecks. PAGEIOLATCH_SH waits occur when a session is waiting for a data page to be read from disk into the buffer pool, while WRITELOG waits indicate delays in writing to the transaction log. These waits are not caused by CPU or memory pressure, but by slow disk I/O, making option C the correct root cause.

Answer

Blocking due to lock contention

Answer

CPU pressure from high-complexity queries

Answer

Insufficient memory allocation for the database

Question 12

Refer to the exhibit. An automatic tuning recommendation to force the last good plan is active. What should the database administrator do next?

Accepted Answer

Monitor the query performance to confirm the forced plan resolves the regression. When an automatic tuning recommendation to force the last good plan is active, the correct next step is to monitor the query performance to confirm that the forced plan resolves the regression. This is because plan forcing is a corrective action that may or may not improve performance; validation through monitoring ensures the change is beneficial before taking further steps like creating or dropping indexes.

Answer

Immediately implement the DROP_INDEX recommendation to reduce overhead

Answer

Create the recommended index to improve performance

Answer

Revert the plan force because it is causing regression

Question 13

Refer to the exhibit. An Azure SQL Database is receiving Intelligent Insights degradation alerts. Which action should be taken first?

Accepted Answer

Implement automatic tuning recommendations. Intelligent Insights is an Azure SQL Database feature that uses built-in intelligence to detect and diagnose performance degradation, and it surfaces root cause analysis along with recommended actions. The first and most appropriate response is to apply the automatic tuning recommendations it provides, because Intelligent Insights alerts are diagnostic in nature and typically point to issues like missing indexes, parameter sniffing, or plan regressions that automatic tuning can resolve without changing the service tier or storage. Scaling resources or changing tiers does not address the underlying query-level cause and may be unnecessary or costly.

Answer

Increase the maximum storage size

Answer

Change the service tier to BusinessCritical

Answer

Scale up to Standard S3 (100 DTU)

Question 14

Refer to the exhibit. Which action should you take to improve performance?

Accepted Answer

Force plan 2 using the query store.. When Query Store shows that a specific plan (Plan 2) performs significantly better than the currently used plan, forcing that plan via Query Store is the targeted fix. Query Store's 'Force Plan' feature pins the optimizer to the known-good plan, immediately restoring performance without changing database tier or schema. This is the least invasive and most precise remediation for a plan-regression issue.

Answer

Enable automatic tuning for the query.

Answer

Increase the database's DTU/DTU level.

Answer

Drop and recreate the query's indexes.

Question 15

You manage an Azure SQL Database that runs an online transaction processing (OLTP) workload. The database is in the General Purpose service tier with 4 vCores. During month-end processing, you observe that the database is hitting its maximum allowed log write throughput, causing delays. You need to increase the maximum log write throughput without changing the service tier. What should you do?

Accepted Answer

Scale up to 8 vCores.. In the General Purpose service tier, log write throughput is directly proportional to the number of vCores. Scaling up vCores is the only way to increase log write throughput while remaining in the same service tier. The other options either change the service tier, affect storage size, or add replication overhead without solving the bottleneck.

Answer

Increase the max size of the database.

Answer

Enable the Business Critical service tier.

Answer

Configure active geo-replication.