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

Question 1

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 2

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 3

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 4

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 5

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 6

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 7

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 8

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 9

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.. Option D is correct because 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 10

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.. A is correct because adding a nonclustered index can reduce the number of pages that need to be updated during write operations, thereby decreasing the I/O load that causes `PAGEIOLATCH_UP` waits. These waits occur when a task is waiting for a page I/O request to complete while holding an update latch. By optimizing the index structure, fewer pages are modified, reducing the I/O contention.

Answer

Reduce MAXDOP to 1.

Answer

Increase the buffer pool size by adding more memory.

Question 11

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 12

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 13

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.. Option B is correct because 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.

Question 14

You are the database administrator for a large e-commerce company. The production Azure SQL Database (Business Critical, 16 vCores) hosts the order processing system. Recently, users report that order submissions are slow during peak hours. You examine the wait statistics and find that `LCK_M_IX` waits are the top wait type. You also notice that the stored procedure `usp_PlaceOrder` performs an `UPDATE` on the `Orders` table, and there is a high volume of concurrent transactions. The table has a clustered index on `OrderID` and a nonclustered index on `CustomerID`. The procedure uses serializable isolation level. Which action will most effectively reduce the blocking?

Accepted Answer

Change the database to use read committed snapshot isolation (RCSI) and modify the procedure to use read committed.. The primary issue is blocking caused by `LCK_M_IX` waits under the serializable isolation level, which holds range locks and prevents concurrent updates. Changing to read committed snapshot isolation (RCSI) and using read committed eliminates these locks by providing statement-level row versioning, allowing concurrent transactions to read without blocking writers. This directly addresses the high volume of concurrent `UPDATE` operations without requiring schema or hardware changes.

Answer

Increase the vCore count to 24.

Answer

Convert the Orders table to a memory-optimized table.

Answer

Add a nonclustered index on the Status column of the Orders table.

Question 15

You manage an Azure SQL Database (General Purpose, S2) used by a reporting application. The database has a table `FactSales` with 500 million rows. Queries that aggregate sales by date are slow. The execution plan shows a clustered index scan on `FactSales`. The table has a clustered index on `SaleID` and a nonclustered index on `DateKey`. The queries filter by `DateKey` and `ProductKey`. You need to improve query performance without changing the service tier. Which action should you take?

Accepted Answer

Create a covering index on DateKey and ProductKey including the aggregated columns.. The query filters by DateKey and ProductKey and aggregates sales data. A covering index on DateKey and ProductKey that includes the aggregated columns (e.g., SUM(SalesAmount)) allows the query to be satisfied entirely from the index without touching the clustered index, eliminating the costly clustered index scan. This is the most direct and effective way to improve performance without changing the service tier.

Answer

Create a nonclustered index on ProductKey only.

Answer

Partition the table by DateKey.

Answer

Create a clustered columnstore index on the table.

Question 16

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 17

You are the database administrator for an Azure SQL Managed Instance hosting a data warehouse workload. You notice that the storage space consumed by the database is significantly larger than expected. The database has multiple large tables with clustered columnstore indexes. You suspect that the columnstore indexes have become fragmented and that deleted rows are consuming space. You need to reclaim storage space with minimal impact on query performance during business hours. What should you do?

Accepted Answer

Perform an ALTER INDEX REORGANIZE with the COMPRESS_ALL_ROW_GROUPS option on the affected columnstore indexes.. Option B is correct because REORGANIZE with COMPRESS_ALL_ROW_GROUPS compresses all row groups, including those in the delta store, and removes deleted rows from columnstore indexes. This operation is online and can be performed during business hours with minimal impact on query performance, unlike a rebuild which is offline and resource-intensive. It directly addresses the fragmentation and deleted row space consumption in columnstore indexes.

Answer

Perform an ALTER INDEX REBUILD on the affected columnstore indexes after business hours.

Answer

Perform an ALTER INDEX REORGANIZE on the affected columnstore indexes.

Answer

Rebuild the entire database by creating a new database and copying data.

Question 18

A company has an Azure SQL Database that experiences periodic performance degradation. The database uses the General Purpose service tier. You need to identify the most common performance bottlenecks. You enable the Query Store and collect data for a week. Which Query Store view should you query to find queries that have the highest total resource consumption over time?

Accepted Answer

sys.query_store_query_stats. A is correct because sys.query_store_query_stats aggregates runtime statistics per query across all plans and time intervals, making it the ideal view to identify queries with the highest total resource consumption (e.g., CPU, I/O, duration) over the collected week. Query Store captures historical execution data, and this view provides the cumulative metrics needed to pinpoint the most resource-intensive queries for performance bottleneck analysis.

Answer

sys.query_store_plan

Answer

sys.dm_exec_query_stats

Answer

sys.dm_db_resource_stats

Question 19

You are monitoring an Azure SQL Database using the Automatic Tuning feature. The database has a workload that is read-intensive. You enable the CREATE INDEX and DROP INDEX options. After a week, you observe that the database has created several new indexes automatically. However, you notice that one of the new indexes is causing increased write latency for an application that performs frequent updates. What should you do to resolve the issue without losing the benefits of automatic tuning for other indexes?

Accepted Answer

Manually drop the problematic index using a DROP INDEX command.. Option D is correct because manually dropping the problematic index allows you to resolve the specific performance issue caused by increased write latency while retaining the benefits of automatic tuning for other indexes. The Automatic Tuning feature in Azure SQL Database can create indexes to improve read performance, but these indexes may introduce overhead on write operations. By issuing a DROP INDEX command, you surgically remove only the offending index without disabling the overall tuning mechanism.

Answer

Use the Azure portal to revert all automatic tuning recommendations for the past week.

Answer

Manually create the missing indexes that were dropped by automatic tuning.

Answer

Disable automatic tuning for the entire database.

Question 20

You are responsible for performance tuning of an Azure SQL Database that hosts a customer relationship management (CRM) application. The database has several tables with millions of rows. Users report that a report query that joins four tables is slow. You examine the query execution plan and notice that the database engine is using an Index Spool (Lazy Spool) operator. Which TWO actions should you take to improve query performance? (Choose two.)

Accepted Answer

Create appropriate indexes on the columns used in joins and filters.. An Index Spool (Lazy Spool) operator in an execution plan indicates that the query engine is creating a temporary index on the fly to support join or filter operations, which is a sign of missing or inadequate permanent indexes. Creating appropriate indexes on the columns used in joins and filters (Option C) eliminates the need for the spool, reducing I/O and improving performance. Updating statistics (Option E) ensures the query optimizer has accurate distribution information to generate efficient plans, which can also help avoid spool operations.

Answer

Disable parallelism for the query using the MAXDOP 1 hint.

Answer

Increase the DTU or vCore count of the database.

Answer

Rewrite the query using table hints to force a specific join order.