CCNA Monitor Optimize Db Questions — Page 3 of 3

151

MCQhard

You have an Azure SQL Database in the General Purpose tier. You notice that the log write throughput is consistently above the service tier limit, causing transaction throttling. You need to resolve this without moving to Business Critical. What should you do?

A.Increase the max log size using ALTER DATABASE.

B.Batch transactions and reduce log writes.

C.Enable accelerated database recovery to reduce log I/O.

D.Move to Business Critical tier.

AnswerB

Log write throughput is capped by the General Purpose tier, so throttling stems from excessive log generation. Batching transactions and reducing log writes lowers the log rate itself, resolving throttling without the cost of moving to Business Critical.

Why this answer

Batching transactions reduces the number of log write operations, lowering the log write throughput below the service tier limit and avoiding throttling. Option A is incorrect because increasing the max log size does not affect the log write rate; it only provides more storage. Option C is incorrect because accelerated database recovery (ADR) improves recovery time and may reduce log I/O for crash recovery, but it does not directly reduce the sustained log write throughput from transaction processing.

Option D is incorrect because the requirement explicitly states not to move to Business Critical.

Exam trap

Candidates may think that increasing log size or enabling ADR will solve throttling, but only reducing log writes (via batching or minimally logged operations) addresses the throughput limit.

Practice this question →

152

MCQmedium

Refer to the exhibit. An Azure SQL Database is receiving Intelligent Insights degradation alerts. Which action should be taken first?

A.Increase the maximum storage size

B.Change the service tier to BusinessCritical

C.Scale up to Standard S3 (100 DTU)

D.Implement automatic tuning recommendations

AnswerD

Intelligent Insights identifies regressions caused by query plan changes, and automatic tuning can apply the corrective plan or index fix without manual intervention. Implementing recommendations first restores performance directly, satisfying the requirement to resolve the degradation before deeper investigation.

Why this answer

Intelligent Insights is an Azure SQL Database feature that uses built-in intelligence to detect and diagnose performance degradation, and it surfaces root cause analysis along with recommended actions. The first and most appropriate response is to apply the automatic tuning recommendations it provides, because Intelligent Insights alerts are diagnostic in nature and typically point to issues like missing indexes, parameter sniffing, or plan regressions that automatic tuning can resolve without changing the service tier or storage. Scaling resources or changing tiers does not address the underlying query-level cause and may be unnecessary or costly.

Exam trap

DP-300 often tests the misconception that any performance alert should be resolved by scaling up resources, when in fact Intelligent Insights alerts are designed to be addressed first through automatic tuning recommendations that target the root cause.

How to eliminate wrong answers

Option A is wrong because increasing maximum storage size only affects the database's capacity ceiling and does nothing to resolve performance degradation caused by query plan or index issues that Intelligent Insights reports. Option B is wrong because moving to Business Critical changes the underlying hardware and adds local SSD and read replicas, which is a costly architectural change that does not target the specific root cause identified by Intelligent Insights and is not a first-step remediation. Option C is wrong because scaling up to Standard S3 (100 DTU) increases compute resources but does not fix the query-level problems Intelligent Insights diagnoses, and it may not even be the correct tier or size for the workload.

Practice this question →

153

MCQeasy

You need to monitor the storage space usage of an Azure SQL Database over time. Which tool should you use?

A.Intelligent Insights

B.Azure SQL Analytics (Azure Monitor)

C.Query Store

D.SQL Server Management Studio (SSMS)

AnswerB

Azure SQL Analytics consumes the database's resource-usage telemetry, including storage consumption, and surfaces it in Azure Monitor with historical trending. This satisfies the requirement to track storage space usage over time rather than only viewing a current value.

Why this answer

Azure SQL Analytics in Azure Monitor provides historical storage metrics. Option A is wrong because Intelligent Insights is for proactive diagnostics, not storage monitoring. Option C is wrong because Query Store focuses on query performance.

Option D is wrong because SSMS does not provide historical monitoring.

Practice this question →

154

MCQmedium

You manage an Azure SQL Database that supports a reporting workload. Users report that a complex aggregation query returns different elapsed times throughout the day, but the logical reads remain consistent. You need to determine whether the query is experiencing CPU pressure or waiting on resources. Which Query Store view should you use to analyze wait statistics for the query?

A.sys.query_store_plan

B.sys.query_store_runtime_stats

C.sys.query_store_query_text

D.sys.query_store_wait_stats

AnswerD

This view captures wait statistics aggregated by query and plan, including wait categories and total wait time. It shows whether the query is waiting on CPU, I/O, locks, or other resources. Since the user needs to distinguish CPU pressure from resource waits, this is the correct source. It provides the necessary wait data to make that determination.

Why this answer

To analyze wait statistics for a specific query in Query Store, you must use the sys.query_store_wait_stats view. It aggregates wait times by query and plan, allowing you to see the wait categories and durations. This directly addresses the need to differentiate between CPU pressure and resource waits.

The other views provide runtime stats, plan details, or query text, but none include wait information.

Exam trap

The trap here is assuming that runtime statistics alone can reveal wait types, when in fact wait categories are stored separately in the wait stats view.

Practice this question →

155

MCQeasy

You manage an Azure SQL Database that experiences periodic performance degradation. You need to identify the top queries by CPU consumption over the last hour. Which dynamic management view should you query?

A.sys.dm_exec_sessions

B.sys.dm_exec_query_plan

C.sys.dm_exec_query_stats

D.sys.dm_exec_requests

AnswerC

sys.dm_exec_query_stats aggregates cumulative execution statistics per cached query plan, including total worker time, so ordering by CPU time over the last hour identifies the top CPU-consuming queries. It satisfies the need to rank queries by CPU consumption.

Why this answer

sys.dm_exec_query_stats returns one row per cached query plan and includes cumulative execution statistics such as total_worker_time (CPU), total_elapsed_time, and execution_count. Aggregating total_worker_time over the last hour identifies the top CPU-consuming queries. This is the standard DMV for query-level performance analysis in Azure SQL Database.

Exam trap

DP-300 often tests the distinction between DMVs that report cumulative historical statistics (sys.dm_exec_query_stats) and those that report only the current instant (sys.dm_exec_requests) — candidates pick the 'requests' DMV because it sounds like it tracks query activity.

How to eliminate wrong answers

Option A is wrong because sys.dm_exec_sessions shows session-level metadata (login name, status, host) but no per-query CPU consumption metrics. Option B is wrong because sys.dm_exec_query_plan returns the XML execution plan for a given plan handle — it describes how a query executes but contains no runtime CPU statistics. Option D is wrong because sys.dm_exec_requests shows currently executing requests only, so it cannot surface queries that already completed within the last hour.

Practice this question →

156

MCQeasy

You are monitoring an Azure SQL Database that uses the General Purpose service tier. You need to configure an alert that triggers when the database's CPU usage exceeds 90% for 10 minutes. What should you use?

A.SQL Server Agent job that queries sys.dm_db_resource_stats and sends an email.

B.Azure SQL Database automatic tuning with CPU-based recommendations.

C.Azure Monitor metric alert on the CPU percentage metric.

D.Query Store alert configured to trigger on high CPU usage.

AnswerC

Azure Monitor provides platform metrics for Azure SQL Database, including CPU percentage. You can create a metric alert that evaluates the CPU percentage metric over a 10-minute window and triggers when the average exceeds 90%. This is the standard and recommended way to set up such alerts, as it integrates with Azure Monitor and supports actions like email or webhook notifications.

Why this answer

Azure Monitor metric alerts are the correct mechanism for alerting on Azure SQL Database metrics. You can create a metric alert rule that monitors the CPU percentage metric and triggers when the average exceeds 90% over a 10-minute period. This is a native, scalable, and integrated solution that supports various notification actions.

Exam trap

The trap here is thinking that Query Store or automatic tuning can send alerts, when they are diagnostic and optimization tools, not alerting services.

Practice this question →

157

MCQmedium

Your Azure SQL Database is experiencing a sudden increase in wait time due to PAGEIOLATCH_SH waits. What should you do to reduce these waits?

A.Increase the database max memory

B.Add appropriate indexes to reduce table scans

C.Enable page compression on large tables

D.Force parameterization of queries

AnswerB

PAGEIOLATCH_SH waits indicate sessions waiting on data pages read from storage, typically caused by scans. Adding appropriate indexes reduces table scans, cutting physical I/O and shortening those waits at their source rather than masking symptoms through scaling.

Why this answer

PAGEIOLATCH_SH waits indicate I/O bottlenecks caused by excessive page reads from disk. Adding appropriate indexes reduces the number of pages read by enabling more efficient data access (e.g., index seeks instead of table scans), directly reducing I/O. Option A is incorrect because increasing max memory does not address the underlying query inefficiency driving I/O.

Option C is incorrect because page compression reduces storage but may increase CPU and does not primarily reduce I/O waits. Option D is incorrect because forcing parameterization improves plan reuse but does not target I/O reduction.

Practice this question →

158

MCQhard

You manage an Azure SQL Managed Instance that hosts a database with a high volume of transactions. You notice that the transaction log is growing rapidly and is not being truncated. You need to identify the cause and resolve the issue. What should you do?

A.Increase the maximum size of the transaction log file.

B.Identify and resolve long-running transactions or replication delays.

C.Change the database to the Simple recovery model.

D.Shrink the transaction log file to reclaim space.

AnswerB

In the Full recovery model, the transaction log cannot be truncated until all transactions are committed and log records are backed up or replicated. Long-running transactions or replication delays hold log records, preventing truncation and causing growth. Resolving these issues allows the log to truncate normally. This is the correct approach because it addresses the root cause without compromising recovery capabilities.

Why this answer

The correct action is to identify and resolve long-running transactions or replication delays. In the Full recovery model, log truncation is blocked by active transactions or replication. Resolving these allows the log to truncate, reclaiming space.

Other options either compromise recovery (Simple model) or treat symptoms (shrink, increase size). Addressing the root cause is essential for a healthy transaction log.

Exam trap

The trap here is assuming that shrinking the log or changing the recovery model is a quick fix, without addressing the underlying truncation delay.

Practice this question →