DP-300 Monitor, configure, and optimize database resources Practice Questions

Explanation: 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.

Explanation: 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.

Explanation: 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.

Explanation: 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.

Explanation: 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.