DP-300 Monitor, configure, and optimize database resources Practice Test 8 — 15 Questions

Question 1

You have an Azure SQL Database that uses the Hyperscale service tier. You notice that the log rate is consistently high, causing performance issues. Which metric should you monitor to identify the log generation rate?

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

Accepted Answer

Log IO percent. 'Log IO percent' in Azure SQL Database reflects the log generation rate, which is critical for monitoring in the Hyperscale tier. Option B is incorrect because 'DTU used' is specific to DTU-based tiers, not Hyperscale. Option C is incorrect because 'CPU percent' measures processor usage, not log rate. Option D is incorrect because 'Data IO percent' measures data file I/O, not log I/O.

Answer

DTU used

Answer

CPU percent

Answer

Data IO percent

Question 2

You administer an Azure SQL Database that uses the General Purpose tier. Users report that queries are slow during peak hours. You need to identify if the slow performance is due to log write latency. Which metric should you examine in Azure Monitor?

Accepted Answer

Log write latency. The correct metric to examine is Log write latency (option D), as it directly measures the time taken to write to the transaction log, which can indicate slow performance due to log write latency. Option A (Log IO percent) measures the percentage of log throughput used, not latency. Option B (Transaction log usage) measures log file space used, not performance. Option C (Average IO latency) includes both data and log I/O, so it is not specific to log writes.

Answer

Log IO percent

Answer

Transaction log usage

Answer

Average IO latency

Question 3

You have an Azure SQL Database with Intelligent Insights enabled. You receive an alert that 'SQLInsights: Resource utilization is consistently high'. You need to determine whether the issue is caused by an increase in user workload or a degradation in query performance. Which Intelligent Insights dimension should you review?

Accepted Answer

Impact. (Impact) is correct because the Impact dimension in Intelligent Insights indicates whether the high resource utilization is caused by an increase in user workload or a degradation in query performance. Option A (Metric) is incorrect because Metric shows the metric name (e.g., 'Resource utilization') but not the root cause. Option B (Severity) is incorrect because Severity indicates the alert level (e.g., high, medium) and does not provide insight into the cause. Option D (Resource type) is incorrect because Resource type shows the Azure resource type (e.g., SQL Database) and is not relevant to the cause of the alert.

Answer

Metric

Answer

Severity

Answer

Resource type

Question 4

You are tuning an Azure SQL Database workload. Which TWO actions can help reduce PAGELATCH_EX contention?

Accepted Answer

Add multiple data files to the filegroup. Adding multiple data files to the filegroup helps reduce PAGELATCH_EX contention by distributing page allocation activity across multiple files. When a single data file is used, all concurrent insert operations compete for the same allocation page (e.g., PFS, GAM, SGAM), causing exclusive latches. With multiple files, each file has its own set of allocation structures, allowing parallel allocation operations to proceed with less contention.

Answer

Increase MAXDOP

Answer

Use a columnstore index

Answer

Configure a readable secondary replica

Question 5

You manage an Azure SQL Managed Instance. You need to monitor storage space usage. Which TWO dynamic management views can you use?

Accepted Answer

sys.dm_db_file_space_usage. Options C and D are correct. sys.dm_db_file_space_usage provides data file space usage, and sys.dm_db_log_space_usage provides transaction log space usage. Option A is incorrect because sys.dm_db_partition_stats shows row counts and partition-level information, not storage space. Option B is incorrect because sys.dm_exec_query_stats is for query performance metrics. Option E is incorrect because sys.dm_os_performance_counters includes various performance counters but does not directly show per-database space usage.

Answer

sys.dm_db_partition_stats

Answer

sys.dm_exec_query_stats

Answer

sys.dm_os_performance_counters

Question 6

You are troubleshooting a transaction log growth issue on an Azure SQL Database. Which THREE conditions can cause the transaction log to grow unexpectedly?

Accepted Answer

Replication that has not delivered transactions to the subscriber. Options A, C, and D are correct. A long-running uncommitted transaction (C) prevents log truncation, causing growth. Replication with undelivered transactions (A) holds log space until delivery completes. Excessive updates due to a missing index (D) generate many log records, increasing log size. Option B is incorrect because memory-optimized tables use a different logging mechanism (e.g., native redo) that does not inherently cause more log growth than disk-based tables. Option E is incorrect because page compression reduces the size of data pages, which can decrease log record size for changes, not cause growth.

Answer

A memory-optimized table with a large number of rows

Answer

Page compression enabled on tables

Question 7

You have an Azure SQL Database that uses automatic tuning. Which TWO benefits does automatic tuning provide?

Accepted Answer

Automatically identify and correct query plan regressions. Options B and E are correct. Automatic tuning can identify and correct query plan regressions (B) and automatically create missing indexes (E). Option A is wrong because automatic tuning does not automatically scale the service tier; that's auto-scale. Option C is wrong because automatic tuning does not automatically create read replicas. Option D is wrong because automatic tuning does not automatically update statistics.

Answer

Automatically scale up the database service tier

Answer

Automatically create read replicas

Answer

Automatically update statistics

Question 8

You are optimizing an Azure SQL Database that uses the Business Critical tier. Which TWO factors affect the maximum log rate?

Accepted Answer

Service level objective (SLO). The maximum log rate in Azure SQL Database Business Critical tier is determined by the service level objective (SLO) which includes a preset log rate limit, and the number of vCores as part of that SLO. The number of log files does not affect the log rate; log write throughput is governed by the storage performance tied to the SLO. Backup retention period and page compression level have no impact on log rate.

Answer

Backup retention period

Answer

Number of log files

Answer

Page compression level

Question 9

You are the database administrator for a large e-commerce application hosted on Azure SQL Database in the Business Critical tier (8 vCores). The database has a table named Orders with 500 million rows. The table is partitioned by OrderDate using a monthly partition function. The application frequently runs queries that aggregate sales by month for the current year. Users report that these queries are slow. You examine the query execution plans and notice that the queries are scanning all partitions, not just the relevant ones. You need to improve query performance without changing the application code. Which action should you take?

Accepted Answer

Re-create the partition function with a different boundary type (e.g., RIGHT instead of LEFT) to align with query patterns.. The issue is that partition elimination is not occurring, meaning the query scans all partitions instead of only the relevant ones for the current year. Since the table is partitioned by OrderDate using a monthly partition function, the alignment of partition boundaries with the query predicate is critical. If the current partition function uses LEFT boundaries, the boundary values represent the start of each partition, but queries for the current year might include dates that cross boundaries in a way that prevents elimination. Re-creating the partition function with RIGHT boundaries ensures that each boundary value is the first day of the next partition, which often aligns better with range queries like 'WHERE OrderDate >= '2025-01-01' AND OrderDate < '2025-12-31''. This can enable partition elimination without changing application code. Option A (update statistics) does not affect partition elimination. Option B (columnstore index) improves compression and query speed but does not force partition elimination. Option D (indexed view) requires code changes and does not directly cause partition elimination. Therefore, option C is correct.

Answer

Update statistics for the Orders table.

Answer

Create a columnstore index on the Orders table.

Answer

Create an indexed view that pre-aggregates sales by month.

Question 10

A company has an Azure SQL Database that is experiencing performance degradation during peak hours. The database is configured with the Standard tier (S2). Which action should you recommend to improve performance without changing the application code?

Accepted Answer

Scale up the database to a higher service objective (e.g., S3).. Scaling up to a higher service objective (e.g., S3) increases DTUs, providing more resources to handle peak loads without requiring any application code changes. Option B is wrong: Query Store aids in performance monitoring and troubleshooting but does not directly improve performance. Option C is wrong: read scale-out offloads read-only workloads to a readable secondary, but the issue is general performance degradation, not specifically read-heavy. Option D is wrong: creating nonclustered indexes may improve query performance but often requires application or query adjustments, and does not guarantee improvement without code changes.

Answer

Enable Query Store and run the Performance Dashboard.

Answer

Enable read scale-out to offload read queries.

Answer

Create nonclustered indexes on all tables.

Question 11

You manage an Azure SQL Managed Instance that hosts a critical OLTP database. You notice that the average CPU usage is consistently above 90% during business hours. You have enabled Intelligent Insights, which recommends creating a missing index. What should you do first to validate the recommendation before implementing it?

Accepted Answer

Use Query Store to review query performance and missing index details.. Use Query Store to review query performance and missing index details. Intelligent Insights provides recommendations, but you should validate them using Query Store, which shows actual query performance and missing index details. This helps confirm the index will reduce CPU usage without negative side effects. B is wrong because scaling up increases resources but doesn't address the root cause; it may be unnecessary. C is wrong because automatic index tuning would implement changes without validation, which could be risky. D is wrong because creating the index immediately without validation might cause performance issues or be unnecessary.

Answer

Scale up the managed instance to a higher tier.

Answer

Enable automatic index tuning.

Answer

Create the recommended index immediately.

Question 12

Your Azure SQL Database is configured with the Hyperscale service tier. You observe that log write latency is consistently high, affecting transaction throughput. What is the most likely cause and the recommended mitigation?

Accepted Answer

High log generation rate is causing log rate governance throttling; reduce the log generation rate by batching transactions.. In Hyperscale, log rate is governed to protect secondary replicas; high latency indicates throttling. Option A is wrong because Hyperscale uses local SSD for log, not PIOPS. Option B is wrong because log rate governance affects all log writes. Option D is wrong because scaling up compute doesn't increase log throughput limits.

Answer

The log IOPS is limited by the disk performance; increase the provisioned IOPS.

Answer

The compute replica is undersized; scale up the compute to increase log throughput.

Answer

The log write latency is due to network congestion; move the database to a different region.

Question 13

You are monitoring an Azure SQL Database using the sys.dm_db_resource_stats DMV. The avg_log_write_percent column shows 95% for the last hour. What does this indicate, and what should you do?

Accepted Answer

The database is nearing its log write IOPS limit; consider scaling up or optimizing log writes.. The avg_log_write_percent metric in sys.dm_db_resource_stats measures the percentage of the log write IOPS limit used. At 95%, the database is nearing its log write IOPS limit, which can cause transaction delays and throttling. The appropriate response is to scale up the service tier (e.g., to a higher DTU or vCore level) or optimize log writes to reduce IOPS consumption. Option A is incorrect because the metric does not indicate running out of log space; log space is separate. Option B is incorrect because storage scale (size) does not directly affect log write IOPS. Option D is incorrect because this metric is specifically about log I/O, not CPU.

Answer

The database is out of transaction log space; increase the max log size.

Answer

The database storage is running out; scale up storage.

Answer

The CPU is overloaded; scale up CPU.

Question 14

You have an Azure SQL Database that uses the Hyperscale service tier. You notice that the log rate is consistently high, causing performance issues. Which metric should you monitor to identify the log generation rate?

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

Accepted Answer

The login password has expired; update the password in the job step.. The intermittent 'Login failed for user' error with a SQL Server authentication login strongly indicates a password expiration issue. Azure SQL Database enforces password expiration policies by default, and if the password for the SQL authentication login used by the job step has expired, the job will fail until the password is updated. This is the most likely cause because the failure is intermittent (occurring after the password expires) and the job uses SQL authentication, which is subject to password policies.

Answer

The database is in a failover group and the secondary is read-only; connect to the primary.

Answer

The job schedule is conflicting with another job; change the schedule.

Answer

The SQL Server Agent is not running; start the agent.

Question 15

You are optimizing an Azure SQL Database that runs a heavy reporting workload. The database uses the General Purpose tier. You notice that many queries are scanning large tables. What is the best first action to improve performance?

Accepted Answer

Analyze the missing index recommendations from Query Store.. The best first action is to analyze the missing index recommendations from Query Store. Query Store tracks query performance and provides actionable index recommendations based on actual workload patterns. Partitioning (A) can help manage large tables but does not directly address scan issues caused by missing indexes. Scaling up to Business Critical (C) is costly and premature without first analyzing the workload. Columnstore indexes (D) are beneficial for analytical queries but should be implemented selectively based on query patterns, not as a blanket solution. Therefore, analyzing missing index recommendations is the optimal starting point for optimization.

Answer

Partition the large tables by date.

Answer

Scale up to Business Critical tier.

Answer

Implement columnstore indexes on all large tables.