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

Question 1

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?

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

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 2

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 3

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 4

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 5

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

Accepted Answer

avg_page_life_expectancy. avg_page_life_expectancy (B) is a key memory-pressure indicator because it measures how long pages stay in the buffer pool in seconds; values below ~300 seconds typically signal that the buffer pool is under memory pressure and pages are being evicted too quickly. avg_pending_disk_io (D) is correct because memory pressure forces more physical I/O, causing I/O requests to queue; a rising pending disk I/O count reflects that the buffer pool can no longer keep working data in memory. page_cache_hit_ratio (E) is correct because it shows the percentage of page requests satisfied from the buffer pool; a low or declining ratio indicates that pages are being evicted and must be read from disk, which is a classic symptom of memory pressure. The unmarked options do not belong: avg_io_stall_ms (A) measures I/O latency rather than memory pressure directly, and log_bytes_used (C) tracks transaction log space consumption, which is unrelated to buffer pool memory pressure.

Answer

avg_io_stall_ms

Answer

log_bytes_used

Question 6

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 model intentionally bundles compute, storage, and I/O into a single blended metric, making it simpler for customers who don't want to reason about separate resource dimensions. Option C is correct because vCore supports Azure Reserved Instances (1- or 3-year commitments) that can deliver significant cost savings, whereas DTU only offers the older DTU-based reserved capacity model. Option D is correct because vCore lets you independently select hardware generation, number of cores, and memory, giving more predictable and tunable performance for steady, consistent workloads. Option A is wrong because Azure Hybrid Benefit is available for both vCore and DTU models, not vCore alone. Option E is wrong because elastic pools are supported in both the DTU and vCore purchase models, so it is not a differentiator.

Answer

Only vCore supports Azure Hybrid Benefit.

Answer

Only DTU supports elastic pools.

Question 7

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 8

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 9

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.. 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 10

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 11

You are the database administrator for an Azure SQL Database used by a financial trading application. The database is in the Business Critical service tier with 16 vCores. The application executes thousands of small, high-frequency transactions per second. Recently, the application's response time has increased, and you observe high PAGELATCH_EX waits in sys.dm_os_waiting_tasks. The database is 500 GB with a single data file (tempdb.mdf) and a single log file (tempdb_log.ldf). TempDB is configured with the default settings. You need to reduce PAGELATCH_EX contention in TempDB. What should you do?

Accepted Answer

Add additional TempDB data files equal to the number of vCores (16).. PAGELATCH_EX contention in TempDB is typically caused by allocation page contention when many concurrent transactions compete for the same system page (e.g., PFS, GAM, SGAM). In Azure SQL Database Business Critical tier, adding multiple TempDB data files equal to the number of vCores (16) reduces contention by distributing allocations across files, each with its own allocation structures. This is a proven best practice for high-concurrency workloads like financial trading applications.

Answer

Move TempDB to Azure Premium Storage for better IO latency.

Answer

Increase the database service tier to 24 vCores.

Answer

Increase the initial size of the TempDB data file to 100 GB.

Question 12

A company runs a critical SQL Server database on Azure Virtual Machines. The database experiences intermittent performance degradation during peak hours. The database administrator notices high I/O latency on the data drive. The current disk configuration uses Standard HDDs. What is the most cost-effective action to reduce I/O latency without changing the virtual machine size?

Accepted Answer

Migrate the data files to Premium SSD managed disks.. Migrating the data files to Premium SSD managed disks is the most cost-effective action to reduce I/O latency without changing the virtual machine size. Premium SSDs provide consistent low-latency performance and higher IOPS/throughput compared to Standard HDDs, directly addressing the high I/O latency during peak hours. This change does not require resizing the VM, keeping compute costs unchanged while improving storage performance.

Answer

Enable instant file initialization in SQL Server.

Answer

Increase the virtual machine size to a series with higher I/O limits.

Answer

Migrate the data files to Standard SSD managed disks.

Question 13

You are monitoring an Azure SQL Database using Query Performance Insight and notice that a specific query has a high average duration and high CPU usage. The query plan shows a clustered index scan on a large table. Which two actions should you take to optimize performance? (Choose two.)

Accepted Answer

Update statistics on the table to ensure the optimizer has current information.. Option B is correct because stale or missing statistics cause the query optimizer to misestimate cardinality, which can lead it to choose a clustered index scan instead of a more selective seek; updating statistics gives the optimizer current distribution data so it can produce a better plan. Option E is correct because a covering nonclustered index includes all columns referenced by the query (key columns plus included columns), allowing the engine to satisfy the query with a seek and avoid scanning the large clustered index, directly reducing duration and CPU. Option A is not appropriate because fragmentation mainly affects scan efficiency and rebuilds are a maintenance action, not the targeted fix for a plan choosing a scan due to missing index coverage. Option C is not appropriate because forcing a plan via Query Store hints does not create a better access path if no suitable index exists, and it is a workaround rather than an optimization. Option D is not appropriate because scaling up DTUs adds resources but does not change the inefficient scan-based plan, so it treats the symptom rather than the cause.

Answer

Rebuild the clustered index to reduce fragmentation.

Answer

Force a different query plan using Query Store hints.

Answer

Increase the DTU service tier of the database.

Question 14

Match each Azure SQL Database backup type to its description.

Accepted Answer

Full backup: Contains all data in the database.. These are the three types of backups used for point-in-time restore in Azure SQL Database.

Answer

Full backup: Contains only changes since the last full backup.

Answer

Differential backup: Contains all committed transactions since the last full backup.

Question 15

You manage an Azure SQL Database with the Premium service tier. You need to reduce storage costs by automatically deleting old audit logs that are older than 90 days. What should you configure?

Accepted Answer

Configure a lifecycle management policy on the Azure Storage account that stores the audit logs.. Azure SQL Database audit logs are stored in Azure Storage. A lifecycle management policy on the storage account can automatically delete blobs older than a specified number of days, such as 90 days, reducing storage costs. Option A is incorrect because Extended Events are not designed for automated deletion of audit logs. Option B is incorrect because backup retention is unrelated to audit logs. Option C is incorrect because the audit retention policy in Azure SQL Database's server audit settings only controls how long audit records are retained in the database, not the actual deletion of stored logs.

Answer

Create an Extended Events session that filters and deletes old audit events.

Answer

Reduce the backup retention period to 90 days.

Answer

Set the audit retention policy to 90 days in the server's audit settings.

Question 16

You have an Azure SQL Database that is experiencing performance degradation. You suspect that parameter sniffing is causing suboptimal execution plans. What should you do to mitigate this issue without changing application code?

Accepted Answer

Enable forced parameterization for the database.. Forced parameterization (option D) is the correct mitigation because it instructs the SQL Server query optimizer to treat all literal values in queries as parameters, which reduces the likelihood of parameter sniffing causing suboptimal plans for repeated queries. This is done at the database level without requiring any changes to application code, making it ideal for scenarios where code modification is not feasible.

Answer

Add the OPTION (RECOMPILE) query hint to all queries.

Answer

Clear the plan cache using DBCC FREEPROCCACHE.

Answer

Enable the 'Optimize for Ad Hoc Workloads' setting.

Question 17

You are monitoring an Azure SQL Database using Intelligent Insights. You receive an alert that resource usage has exceeded 90% for the past hour. What is the recommended first step to troubleshoot?

Accepted Answer

Review the Intelligent Insights root cause analysis report.. Intelligent Insights is Azure SQL Database's built-in diagnostics engine that continuously analyzes workload telemetry and produces a root cause analysis (RCA) report whenever it detects a performance anomaly such as sustained resource usage above 90%. The RCA report already identifies the specific problem category (for example, a blocking query, a missing index, or a plan regression) and points to the offending query or wait. Reviewing this report first is the fastest path to the actual cause, and it prevents the common mistake of blindly scaling the tier before knowing whether the bottleneck is CPU, IO, or a query-level issue.

Answer

Scale up the database to a higher service tier immediately.

Answer

Check sys.dm_os_wait_stats for high wait types.

Answer

Enable the Query Store and wait for data collection.

Question 18

You have an Azure SQL Database that uses the Hyperscale service tier. You notice that during peak hours, the log rate is throttled frequently. You need to reduce log write throttling. What should you do?

Accepted Answer

Increase the service level objective (SLO) to a higher vCore count.. In Azure SQL Database Hyperscale, log write throttling occurs when the log generation rate exceeds the throughput capacity of the current service level objective (SLO). Increasing the vCore count raises the log write throughput limit, directly alleviating throttling. This is the correct approach because Hyperscale log rate limits are tied to the SLO, and higher tiers provide higher log I/O bandwidth.

Answer

Enable accelerated database recovery to reduce log generation.

Answer

Reduce the size of individual transactions to commit more frequently.

Answer

Partition large tables to spread log writes across files.

Question 19

You run the KQL query shown in the exhibit. The query returns a list of query IDs. What is the purpose of this query?

Accepted Answer

Identify the top 10 queries by average duration.. The KQL query in the exhibit uses `summarize` with `avg(duration)` and then `top 10 by avg_duration`, which explicitly calculates the average duration per query_id and returns the top 10 queries with the highest average duration. This directly matches the purpose of identifying the top 10 queries by average duration.

Answer

Identify queries consuming the most CPU.

Answer

Identify queries with the highest total wait time.

Answer

Identify queries with the highest execution count.

Question 20

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