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

Question 1

Which TWO actions can help reduce `PAGEIOLATCH_UP` waits on an Azure SQL Database?

Accepted Answer

Add a nonclustered index to reduce update I/O.. PAGEIOLATCH_UP waits occur when a task is waiting for a data page to be read from disk into the buffer pool while holding an update latch. These waits are typically caused by I/O contention on data pages during update operations. Option A helps by reducing the number of data pages that need to be modified and read during an update, for instance by allowing index seeks instead of scans. Option C helps by increasing the overall I/O throughput of the database through a higher service tier, which reduces the time needed for page reads. In contrast, reducing MAXDOP (B) mainly affects CPU parallelism, not I/O, and adding memory (D) might help only if the buffer pool is too small to cache pages, but it does not directly address the I/O latency causing PAGEIOLATCH_UP.

Answer

Reduce MAXDOP to 1.

Answer

Increase the buffer pool size by adding more memory.

Question 2

You are managing an Azure SQL Database that runs a critical line-of-business application. Users report that a specific query is running slower than usual. You identify that the query is performing a clustered index scan on a large table with over 10 million rows. The table has a clustered index on an identity column and a nonclustered index on a frequently filtered column. You need to minimize the query execution time without adding additional indexes. What should you do?

Accepted Answer

Update all statistics on the table.. The query is performing a clustered index scan, which means SQL Server is reading all rows in the table. Outdated statistics can cause the optimizer to choose a scan instead of a more efficient seek. Updating all statistics on the table (option B) provides the optimizer with fresh distribution information, potentially allowing it to choose a better execution plan that avoids the scan, thereby reducing query execution time without adding indexes.

Answer

Increase the service tier of the Azure SQL Database to provide more resources.

Answer

Rebuild the clustered index to reduce fragmentation.

Answer

Update the statistics on the nonclustered index only.

Question 3

You are configuring Azure SQL Database automatic tuning. You want to enable the feature that automatically creates and drops indexes based on workload patterns. Which option should you enable?

Accepted Answer

Create Index and Drop Index. Azure SQL Database's automatic tuning includes the 'Create Index' and 'Drop Index' options, which together automatically create indexes to improve query performance and drop unused or duplicate indexes to reduce overhead based on workload patterns. This feature analyzes query execution plans and index usage statistics to dynamically adjust indexing without manual intervention.

Answer

CREATE INDEX

Answer

Force Parameterization

Answer

Force Last Good Plan

Question 4

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.. Disabling the dropIndex option prevents automatic index drops while keeping forcePlan and createIndex enabled. Option A is wrong because manually creating an index and marking it as required does not prevent future automatic drops. Option B is wrong because Query Store does not prevent index drops; it only tracks usage. Option D is wrong because disabling all automatic tuning removes all benefits, including forcePlan and createIndex.

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 5

What effect does the command in the exhibit have on the database?

Accepted Answer

Enables auto-pause with a delay of 1 minute.. Setting AUTO_PAUSE_DELAY = 0 is not a valid value in Azure SQL Database serverless. The minimum allowed delay is 1 minute. Therefore, the database interprets a value of 0 as the minimum, effectively setting the auto-pause delay to 1 minute.

Answer

Configures the database to pause after 1 second of inactivity.

Answer

Disables auto-pause for the database.

Answer

Sets auto-pause delay to 1 hour.

Question 6

You have an Azure SQL Managed Instance used for an e-commerce platform. During a flash sale, you experience a deadlock that causes transaction rollbacks. You need to minimize deadlock occurrences in the future. What should you implement?

Accepted Answer

Enable READ COMMITTED SNAPSHOT isolation level.. Enabling READ COMMITTED SNAPSHOT isolation reduces deadlocks by using row versioning. Option B is wrong because deadlock graph captures events but doesn't prevent them. Option C is wrong because automatic tuning does not handle deadlocks. Option D is wrong because increasing instance size does not prevent deadlocks.

Answer

Configure deadlock graph in Extended Events.

Answer

Enable automatic tuning to force last good plan.

Answer

Increase the instance vCores to improve concurrency.

Question 7

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 General Purpose 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 8

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 tracks query performance and helps identify top resource-consuming queries. Option B is wrong because Azure SQL Analytics (preview) is a paid solution. Option C is wrong because dynamic management views like sys.dm_exec_query_stats and sys.dm_exec_sql_text require manual querying and are not as user-friendly. Option D is wrong because SQL Server Profiler is deprecated and not supported in Azure SQL Database.

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 9

A production Azure SQL Database is experiencing high CPU usage during peak hours. The database uses the S3 service tier. You need to reduce CPU usage without changing the service tier. Which action should you take?

Accepted Answer

Identify and create missing indexes.. High CPU usage in an S3 Azure SQL Database often stems from inefficient query plans caused by missing indexes. Creating appropriate indexes reduces the number of rows scanned and the CPU cycles needed for operations like key lookups and sorting, directly lowering CPU consumption without changing the service tier.

Answer

Increase the maximum number of concurrent workers.

Answer

Reduce MAXDOP to 1.

Answer

Increase MAXDOP to 8.

Question 10

You manage an Azure SQL Managed Instance with a large database. You notice that the automatic tuning recommendations are not being applied. You need to ensure that automatic tuning is enabled for the instance. Which PowerShell cmdlet should you run?

Accepted Answer

Set-AzSqlInstanceDatabase with -AutoTuningProperties. The correct cmdlet is Set-AzSqlInstanceDatabase because Azure SQL Managed Instance uses instance-level databases, and automatic tuning properties are configured at the database level within the instance. The -AutoTuningProperties parameter directly enables or configures automatic tuning recommendations for the specified database in the managed instance.

Answer

Set-AzSqlDatabase with -AutoTuningProperties

Answer

Set-AzSqlInstance with -DtcEnabled

Answer

Set-AzSqlServer with -AdministratorLogin