DP-300 Monitor, configure, and optimize database resources — Timed 40 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

Question 11

You have an Azure SQL Database that uses the General Purpose service tier. You notice that the log write throughput is consistently near the limit. What should you do to improve log write performance?

Accepted Answer

Migrate to the Business Critical service tier.. The General Purpose service tier in Azure SQL Database has a maximum log write throughput of 1.5 MB/s for the most common configurations. The Business Critical tier uses local SSD storage and a higher log I/O limit (up to 100 MB/s), which directly addresses log write throughput bottlenecks. Migrating to Business Critical is the correct action because it provides significantly higher log write throughput and lower latency for transaction log writes.

Answer

Enable accelerated database recovery.

Answer

Migrate to the Hyperscale service tier.

Answer

Increase the DTU purchase model to a higher tier.

Question 12

You are monitoring an Azure SQL Database using Query Performance Insight. You see a query with high duration and high CPU usage. The query plan shows a clustered index scan. What is the most likely cause and recommendation?

Accepted Answer

Missing nonclustered index; create an index on the predicates.. Query Performance Insight shows a query with high duration and CPU usage, and the query plan reveals a clustered index scan. A clustered index scan reads all rows in the table, which is inefficient when only a subset of rows is needed. The most likely cause is a missing nonclustered index on the columns used in the WHERE clause (predicates), which would allow a seek operation instead of a full scan, reducing both CPU and duration.

Answer

Fragmented clustered index; rebuild the clustered index.

Answer

Insufficient memory; increase the service tier.

Answer

Parameter sniffing; add OPTION (RECOMPILE).

Question 13

You need to configure Azure SQL Database to automatically adjust indexing based on workload patterns. Which feature should you enable?

Accepted Answer

Automatic tuning. Automatic tuning in Azure SQL Database continuously analyzes query execution plans and workload patterns, then automatically creates, drops, or rebuilds indexes to improve performance. It uses built-in intelligence to recommend and apply index changes without manual intervention, making it the correct feature for automatically adjusting indexing based on workload patterns.

Answer

Azure Advisor

Answer

Intelligent Insights

Answer

Query Store

Question 14

You deploy a new Azure SQL Database and need to ensure that all queries are logged for performance analysis. Which configuration should you enable?

Accepted Answer

Query Store. Query Store captures a history of query execution plans, runtime statistics, and wait statistics, enabling detailed performance analysis and troubleshooting. It is the correct choice because it is specifically designed to log query-level performance data for Azure SQL Database without requiring external storage or additional configuration.

Answer

Data classification

Answer

Server-level audit

Answer

Diagnostic settings for SQLInsights

Question 15

You have an Azure SQL Database with a heavy workload. You notice that the `PAGEIOLATCH_SH` wait is the top wait. Which performance issue does this indicate?

Accepted Answer

I/O subsystem bottleneck. The `PAGEIOLATCH_SH` wait type indicates that a query is waiting for a data page to be read from disk into the buffer pool. Since this is the top wait, it points to an I/O subsystem bottleneck where the storage cannot keep up with the demand for reading pages, causing performance degradation.

Answer

Blocking

Answer

CPU bottleneck

Answer

Memory pressure

Question 16

You need to monitor Azure SQL Database performance over time and receive alerts when CPU usage exceeds 80%. Which Azure service should you use?

Accepted Answer

Azure Monitor Alerts. Azure Monitor Alerts is the correct service because it allows you to create metric-based alert rules that trigger when the CPU percentage of an Azure SQL Database exceeds a defined threshold (e.g., 80%). It continuously monitors performance metrics over time and sends notifications (e.g., email, SMS, or webhook) when the condition is met, fulfilling the requirement for both monitoring and alerting.

Answer

Automatic tuning

Answer

Query Performance Insight

Answer

SQL Assessment

Question 17

You have an Azure SQL Database in the Hyperscale service tier. You need to ensure that read-only workloads are offloaded to a readable secondary. Which configuration should you set?

Accepted Answer

Use ApplicationIntent=ReadOnly in the connection string.. Setting `ApplicationIntent=ReadOnly` in the connection string directs read-only workloads to a readable secondary replica in Azure SQL Database Hyperscale. This offloads read traffic from the primary, improving performance for write-heavy operations. The Hyperscale tier supports this feature without requiring a failover group or explicit read-scale configuration.

Answer

Set ReadOnlyRouting=1 on the database.

Answer

Add the database to a failover group.

Answer

Set ReadScale to 1 on the database.

Question 18

Which THREE metrics should you monitor to detect a memory pressure issue in Azure SQL Database?

Accepted Answer

avg_page_life_expectancy. B (avg_page_life_expectancy) is correct because it measures how long (in seconds) a data page stays in the buffer pool before being evicted. A low value (typically below 300 seconds) indicates that pages are being flushed quickly due to memory pressure, forcing more physical I/O. This is a direct indicator of insufficient memory for the buffer cache.

Answer

avg_io_stall_ms

Answer

log_bytes_used

Question 19

Which TWO configurations can help improve the performance of an Azure SQL Database experiencing high `WRITELOG` waits?

Accepted Answer

Use in-memory OLTP to reduce log writes.. High WRITELOG waits indicate that the transaction log is a bottleneck, often due to excessive log I/O. In-memory OLTP reduces log writes by logging only the delta changes for memory-optimized tables, rather than full row versions, which directly alleviates log pressure. This makes option B correct.

Answer

Enable Transparent Data Encryption (TDE).

Answer

Enable Query Store.

Answer

Increase the frequency of database backups.

Question 20

Which THREE factors should you consider when choosing between vCore and DTU purchase models for Azure SQL Database performance optimization?

Accepted Answer

DTU is simpler for customers who want a bundled metric.. The DTU (Database Transaction Unit) model bundles compute, storage, and I/O into a single, simple metric, making it easier for customers who want a straightforward, pre-configured performance tier without needing to manage individual resources. This contrasts with the vCore model, which requires separate configuration of vCores, memory, and storage, offering more granular control but greater complexity.

Answer

Only vCore supports Azure Hybrid Benefit.

Answer

Only DTU supports elastic pools.