CCNA Monitor, configure, and optimize database resources Questions

sys.dm_os_wait_stats aggregates cumulative wait statistics by wait type across the instance, letting you rank the top resource waits causing the slowdown. It directly satisfies the need to identify which resource the OLTP workload is waiting on.

Memory grants pending counts queries waiting for workspace memory before execution, so sustained non-zero values directly signal memory pressure. Unlike buffer cache hit ratio, which reflects page availability, this metric exposes grant contention at the workspace level, satisfying the stem's requirement to detect memory pressure in Azure SQL Database.

Elevated wait times on PAGEIOLATCH and related I/O waits, combined with the Extended Events output, point to storage latency rather than CPU or blocking. The database is waiting on data pages to be read from disk, indicating the underlying I/O subsystem cannot meet demand.

Azure Monitor alert rules can monitor the CPU percentage metric of an Azure SQL Database. You can set a threshold of 80% and an aggregation granularity of 10 minutes. Associating an action group with email notifications fulfills the requirement with minimal effort, as it is a built-in, integrated solution.

`PAGEIOLATCH_SH` waits occur when sessions block acquiring shared latches while pages are read from disk into the buffer pool, so sustained dominance points to the storage layer rather than CPU or locking. This satisfies the stem's heavy-workload constraint by identifying the I/O subsystem as the bottleneck, prompting investigation of disk latency and throughput.

The high CPU time with a Hash Match (Aggregate) suggests that the aggregation is processing a large number of rows. Reducing the row count earlier in the query (e.g., by adding more selective filters, pre-aggregating in a subquery, or using a indexed view) decreases the work the hash aggregate must perform. This directly lowers CPU consumption. Other options like index changes or plan forcing may help but are less direct and may not address the root cause of excessive rows entering the aggregate.

Per-database min eDTU guarantees the critical database a reserved floor of resources within the elastic pool, so contention from other databases cannot starve it. This directly satisfies the requirement that the critical database always receives a minimum amount of resources.

Increasing the service tier to Business Critical provides better log write performance through local SSD storage, reducing the log generation rate. This option is correct.

The service level objective sets the provisioned compute and storage limits, which directly cap the transaction log generation rate for Business Critical databases. Higher SLOs provision more log throughput, so the SLO determines the ceiling on log rate independent of workload tuning.

Dropping the single problematic index removes the write-latency penalty while Automatic Tuning continues managing the remaining indexes. This satisfies the constraint of retaining automatic tuning benefits elsewhere, since disabling the feature globally would forfeit those gains.

Default deny with a VNet rule for the subnet allows only that subnet.

Hyperscale log rate limits scale with the service level objective, so scaling up raises the permitted log generation rate. This directly addresses the throttling constraint, unlike storage or replica changes, which do not affect the log rate ceiling.

Query Store captures query execution plans and runtime statistics, including CPU usage, duration, and reads. It allows you to identify top resource-consuming queries over time. This makes it the ideal tool for finding and optimizing high-CPU queries in Azure SQL Database.

Query Store captures execution plans, runtime statistics and missing-index suggestions per query, letting you confirm the high-CPU queries Intelligent Insights flagged and assess the proposed index's projected impact. This satisfies the stem's requirement to validate the recommendation before implementing it, since Query Store retains historical workload data rather than relying on the advisory alone.

Automatic tuning continuously analyses workload telemetry and applies index create, drop, and rebuild recommendations without manual intervention, directly satisfying the requirement to adjust indexing based on workload patterns. Unlike manual index maintenance or Query Store alone, it acts automatically, making it the appropriate choice for Azure SQL Database.

Vulnerability assessment is inactive until its state is Enabled, and the audit mandates 90-day result retention. Setting retentionDays to 90 meets that minimum while enabling scans satisfies the first requirement, so both properties must change together on the server's configuration.

CPU percentage is a native Azure Monitor metric for Azure SQL Database, reflecting average compute utilisation. It satisfies the autoscale requirement because Azure Monitor autoscale rules can trigger scale actions directly from this metric, unlike log-based or non-emitted measures.

The serverless compute tier automatically scales compute resources based on workload demand and bills per second of compute usage. It pauses the database during inactive periods, which can reduce cost. For a financial application with variable demand, serverless provides automatic scaling without manual intervention, meeting the requirement. It is available in the General Purpose service tier, so no tier change is needed.

Forcing a plan changes execution behaviour, so the administrator must verify the regression is actually resolved. Monitoring query performance confirms the forced plan restores the previous good performance before the recommendation is left active or reverted.

Forcing the previous plan through Query Store pins the known-good execution plan, bypassing the regressed plan chosen by the optimiser. This restores consistent CPU performance without application changes, directly satisfying the requirement to avoid rewriting code. Plan forcing applies immediately to the identified query, resolving the intermittent degradation.

Query Store is fully supported in the Hyperscale service tier and captures query plans and runtime statistics. It allows you to identify resource-intensive queries, view their plans, and force a specific plan. This directly meets the need to capture, analyze, and force a plan for a query causing excessive physical reads.

Included columns are stored at the nonclustered index leaf level, eliminating the key lookups back to the clustered index for the queried columns. This addresses the high IO and long execution times while adding far less storage than a new covering index.

A clustered index scan reading the whole table indicates the predicate columns lack a supporting nonclustered index, forcing full scans that inflate duration and CPU. Creating a nonclustered index on those predicate columns enables seeks, directly addressing the observed plan.

Query Store is a built-in, free feature that captures query performance data and helps identify top resource consumers.

FORCE LAST GOOD PLAN automatically detects plan regressions caused by parameter sniffing and forces the last known good plan. This directly addresses the issue by reverting to a plan that performed well previously, ensuring stable performance during peak hours.

Query Store captures execution plans and runtime statistics, letting you identify the specific queries driving the 80% CPU and 70% data IO. It directly satisfies the need to find the resource-consuming cause rather than guessing at infrastructure or indexing issues.

Error 40501 is a transient resource governance response, and the documented mitigation is to retry the operation after a delay. Adding exponential backoff lets the ETL ride out short throttling periods without failing. This is the correct approach when you must remain on the same service tier and cannot reduce the workload's resource demand or move it to a less busy time.

Query Store captures all plans for a query and allows you to force a specific plan by plan_id. Forcing the fast plan ensures the optimizer uses that plan regardless of parameter values, eliminating the variability. This is the precise, supported method to stabilize performance for a known-good plan in Azure SQL Database.

RESOURCE_SEMAPHORE waits occur when queries cannot obtain their requested memory grant, so checking queries with large memory grants exposes the contention directly. This satisfies the stem's requirement to identify the root cause of high wait times, since oversized grants exhaust the workspace memory pool and block other queries.

sys.dm_exec_requests exposes one row per currently executing request, including a total_elapsed_time column, so filtering on total_elapsed_time > 30000 returns queries running over 30 seconds. It satisfies the stem's requirement to monitor long-running queries directly, unlike completed-query or historical views.

PAGEIOLATCH_SH waits occur when queries wait for data pages to be read from storage into the buffer pool. Index rebuilds and reorganizations scan large amounts of data, evicting useful pages and causing heavy physical reads. By reducing how often or how broadly you rebuild indexes, you lower the I/O pressure and the associated latch waits, which directly addresses the stalls without changing the service tier.

For vCore-based service tiers such as General Purpose, CPU percentage is a key metric that shows how much of the provisioned compute is being used. If it consistently approaches 100% during month-end processing, the database is CPU-bound and likely needs more vCores or query tuning to improve response times.

Query Store persists historical execution plans and runtime statistics, so it directly exposes the plan change and the regressed duration. Intelligent Insights only flags the degradation; comparing the previous and current plans in Query Store identifies the root cause, such as a plan regression or missing statistics.

SQL auditing records authentication events, including failed logins, into Azure Storage, Log Analytics, or Event Hubs, satisfying the failed-login tracking requirement. It captures the security-relevant activity the stem demands, complementing metric alerts for DTU and Query Store for query performance analysis.

Each committed transaction requires a log flush, so committing one row at a time multiplies the number of flushes and inflates log write throughput. Grouping many row modifications into a single transaction reduces the number of commits and therefore the number of log flushes, lowering log write pressure while staying on the same tier. This directly targets the observed near-limit log throughput.

Active geo-replication ships transaction log records asynchronously to the secondary region, so elevated log write latency there points to the WAN link rather than local storage. The primary stays healthy because it commits locally without waiting for the secondary, matching the stem's normal primary performance.

RESOURCE_SEMAPHORE waits occur when queries are waiting for memory grants. By identifying queries that request large memory grants and optimizing them (e.g., adding indexes, rewriting queries), you reduce memory pressure and alleviate the waits. This addresses the root cause directly.

Hyperscale offloads log processing to dedicated log service compute nodes, so redo log latency scales with log service throughput rather than primary compute. Increased redo latency therefore points to that log service throughput as the bottleneck.

Automatic tuning applies Intelligent Insights index recommendations directly, creating the suggested index without manual action. Enabling the CREATE INDEX option on the server or database satisfies the no-intervention requirement, and Business Critical tier supports it.

S3 caps at 100 DTUs, so peak-hour demand exceeding that ceiling throttles throughput and inflates latency. The exhibit's resource-wait pattern confirms the workload outgrows the tier, making a scale-up to a higher service objective the direct remedy for the stated bottleneck.

Query Store retains multiple plans per statement, letting you pin the historically faster plan without rewriting code. Forcing plan 2 bypasses the regressed plan the optimiser currently chooses, restoring the execution characteristics the exhibit shows were previously acceptable.

Query Store captures every plan, runtime statistic and regression event, so it reveals why the forced plan still performs poorly before you change any tuning configuration. Automatic tuning only forces a plan; diagnosing the underlying cause requires the historical execution data Query Store retains.

Query Store persists execution plans, runtime statistics and wait categories inside the database, letting you identify regressed queries and plan changes across time windows. This directly satisfies the stem's requirement to monitor and optimise database resources, since historical query-level telemetry is unavailable through dynamic management views alone.

Columnstore indexes store data column-wise with compression and batch-mode execution, cutting I/O for large analytical scans on Azure SQL Managed Instance. This directly satisfies the stem's query performance optimisation goal for large tables, where rowstore indexes would scan far more pages.

PAGELATCH waits occur when sessions contend for the same in-memory page; with a clustered index, concurrent inserts target the last page, serialising access there. This last-page contention is the specific mechanism producing the high PAGELATCH waits observed, matching the stem's symptom.

Query Store captures multiple plans for a query and allows you to force a specific plan. By identifying the fast plan in Query Store and forcing it, you ensure that the query consistently uses that plan, avoiding the slow plan that causes excessive physical reads. This is the most direct and supported method in Azure SQL Database.

The cpu_percent metric represents the percentage of CPU usage by the database. It is a direct indicator of CPU pressure. Alerting when this metric consistently exceeds a threshold, such as 80%, helps identify CPU bottlenecks before they impact performance. This metric is available in Azure Monitor for Azure SQL Database.

Sustained Maximum DTU above 90% indicates the database tier is saturated and cannot absorb further load. Scaling up to a higher service tier or increasing DTUs adds compute and I/O capacity, directly relieving the resource bottleneck causing the high consumption.

The cpu_percent metric represents the percentage of CPU used by the database. It is a standard Azure Monitor metric for Azure SQL Database and is exactly what you need to alert on CPU usage exceeding a threshold. You can create an alert rule on this metric with a condition of greater than 90 and an aggregation granularity of 10 minutes to meet the requirement.

Business Critical uses local SSD for the transaction log, which significantly reduces log write latency compared to the remote storage used in General Purpose. This directly addresses WRITELOG waits by providing lower latency and higher throughput for log writes, making it the appropriate change for this scenario.

PAGEIOLATCH_SH waits indicate that queries are waiting for data pages to be fetched from storage into the buffer pool. Increasing the service tier provides more memory (larger buffer pool) and higher IOPS, reducing the frequency and duration of these waits. This directly addresses the I/O bottleneck without modifying the application, making it the appropriate action for this scenario.

Page Life Expectancy measures how long pages remain in the buffer pool before eviction. A sustained drop below a few hundred seconds indicates the buffer pool cannot hold the working set, revealing memory pressure in Azure SQL Database.

In the Business Critical tier, the transaction log write throughput is primarily determined by the number of vCores. Each vCore provides a certain log write rate, and the total log throughput scales linearly with the number of vCores. Therefore, increasing vCores directly increases the maximum log write rate.

Azure Monitor Alerts evaluates metric rules against Azure SQL Database telemetry and triggers notifications when thresholds such as CPU above 80% are breached. It provides the sustained monitoring and alerting the stem requires, unlike query-level tools or auditing features.

Query Store continuously captures query text, execution plans and runtime statistics into internal catalog views, giving historical performance analysis without trace overhead. It satisfies the requirement that all queries be logged for later analysis, and can be enabled per database in Azure SQL Database.

Azure Monitor metric alerts evaluate a specific metric against a threshold over a defined time window. The CPU percent metric reflects the percentage of the vCore limit being consumed. By creating a metric alert rule on CPU percent with a threshold of 90 and an aggregation granularity of 10 minutes, you can detect sustained high CPU and trigger notifications as required.

Scaling up to a higher service tier or compute size increases the amount of memory available to the database, which directly increases the query workspace memory. This allows more queries to obtain memory grants, reducing RESOURCE_SEMAPHORE waits. This is a valid action to alleviate memory grant pressure.

The KQL query reads QueryStoreRuntimeStatistics from Log Analytics, so absence of rows means the table was never populated. Query Store being enabled locally only writes to the instance; diagnostic settings must explicitly stream that category to the workspace before any query returns data.

Query Store captures query plans and runtime execution statistics automatically, persisting them in the user database. It provides historical data on query performance, enabling you to identify regressions, analyze plan changes, and force plans. For a read-heavy workload, Query Store is essential for diagnosing issues without manual tracing.

Query Store captures query, plan and runtime statistics inside the database engine itself, with negligible overhead and no external agent. It satisfies the minimal-overhead requirement for Azure SQL Managed Instance, unlike extended events sessions or DMV polling.

The intermittent 95% spikes with a 20% baseline indicate workload-driven contention, not insufficient capacity. Tuning the offending queries and rescheduling the heavy ETL job removes the burst at source, avoiding the cost of scaling up for brief peaks.

Query Store captures per-query runtime statistics and execution plans, letting you rank top resource consumers by CPU, duration or reads. This directly identifies the highest DTU-consuming queries, which the stem requires, without needing extended events or manual DMV polling.

PAGELATCH_EX waits arise from concurrent inserts contending on the last page of an ascending index. Partitioning the table across multiple filegroups or partition ranges spreads inserts over several hot pages, reducing that contention and therefore the exclusive page-latch waits the database is experiencing.

Query Store's avg_cpu_time exposes CPU consumption per query hash, directly revealing which queries drive CPU pressure. Duration alone can reflect waits or blocking, so adding avg_cpu_time isolates the actual CPU-heavy offenders the stem asks for.

Memory-optimized tempdb metadata removes the latch contention on system pages that track temp object metadata. This significantly reduces PAGELATCH_EX waits in workloads with heavy temp table usage. It is a recommended configuration for Azure SQL Database when tempdb contention is observed.

CPU percentage is a metric available in Azure Monitor for Azure SQL Database that shows the percentage of CPU used by the database. A consistently high CPU percentage directly indicates CPU pressure. This is the most specific and direct metric to confirm that slow queries are due to CPU bottlenecks, making it the correct choice.

Query Performance Insight surfaces the top CPU, duration and DTU-consuming queries, letting you tune indexes or rewrite statements to cut DTU usage. Scaling the tier would mask the problem rather than reduce consumption, so identifying the offending queries comes first.

Query Store captures missing index recommendations from actual workload history, letting you add indexes that eliminate the large table scans. This directly targets the scan bottleneck and is the cheapest first action, unlike scaling the General Purpose tier or rewriting queries blindly.

Query Store captures query execution statistics, including CPU time, duration, and execution count. The Top Resource Consuming Queries report in the Azure portal or via Query Store catalog views ranks queries by resource consumption, making it ideal for identifying which queries are driving high CPU usage. This directly addresses the need to pinpoint CPU-intensive queries.

Schema-scoped EXECUTE permission on the Sales schema lets AppUser run stored procedures without SELECT rights on base tables, satisfying least privilege. Ownership chaining means the procedures access tables under the schema owner's permissions, so no direct table grants are needed.

sys.dm_exec_query_stats aggregates execution counts, CPU time and logical reads per cached plan, so ordering by total resource consumption reveals the heaviest queries. This satisfies the requirement to identify which queries drive the elevated DTU usage on the Azure SQL Database.

Azure Monitor provides built-in metrics for Azure SQL Database, including cpu_percent. You can create an alert rule that triggers when the average cpu_percent exceeds 80% over a 10-minute window. This is the most direct and least administrative effort because it uses platform metrics and the Azure Monitor alerting framework, with no need to write custom queries or deploy additional components.

PAGEIOLATCH_SH waits occur when queries read data pages from storage. Adding appropriate indexes, such as covering indexes, can reduce the number of pages that must be read to satisfy a query, thereby decreasing physical IO and the associated waits. This is a targeted optimization that addresses the query workload itself. It is especially effective when queries perform scans that can be converted to seeks with the right indexes.