CCNA Monitor Optimize Db Questions

Log write latency directly measures the time taken to write transaction log records to durable storage, which is the precise bottleneck causing slow queries during peak hours in the General Purpose tier. Examining this metric in Azure Monitor isolates whether log throughput, not CPU or data I/O, constrains performance.

High worker time with low logical reads indicates CPU consumption rather than I/O. Inefficient plans, such as scans, bad cardinality estimates or missing indexes causing repeated CPU-heavy operations, burn worker time without many page reads, and no blocking or deadlocks were reported.

To identify the blocking chain and the T-SQL statements involved, you should query sys.dm_exec_requests to get the blocking_session_id, join it with sys.dm_exec_sql_text to retrieve the SQL text using the sql_handle, and optionally join with sys.dm_exec_sessions for session details. This combination provides the blocking session, the blocked session, and the statements they are executing, giving a complete view of the blocking scenario.

The avg_log_write_percent column measures log write throughput as a percentage of the database's provisioned limit. At 95%, the database is approaching its log write IOPS ceiling, risking throttling. Scaling up the service tier or SKU raises that limit, while optimising log-heavy operations reduces demand, directly addressing the sustained near-saturation constraint.

READ COMMITTED SNAPSHOT uses row versioning in tempdb, so readers don't take shared locks on data pages. This removes the shared-versus-exclusive lock conflicts that cause deadlocks during concurrent flash-sale transactions, satisfying the requirement to minimise deadlock occurrences.

The query filters on CustomerID and returns a small number of rows, but the optimizer chooses a clustered index scan, likely because the nonclustered index on CustomerID does not cover the query and key lookups would be expensive. A covering index includes all columns referenced by the query (in the key or INCLUDE clause), eliminating key lookups and enabling an index seek. This reduces IO and CPU, improving performance for this frequent query.

Disabling auto-pause keeps the serverless database continuously active, eliminating the cold-start resume latency that delays the first query. This directly satisfies the requirement to improve first-query performance after idle periods, though it forfeits the cost savings auto-pause provides during inactivity.

PAGEIOLATCH_SH waits occur when sessions stall reading pages from storage into the buffer pool, so the first suspect is storage throughput. Checking database-level IOPS or throughput limits identifies whether the tier is throttling reads during the degradation windows.

Streaming 'AllMetrics' captures the platform metrics Azure SQL Database emits natively — CPU percentage, data IO and log IO — without enabling expensive SQL Insights or query store overhead. Diagnostic settings route these directly to the Log Analytics workspace, satisfying the long-term analysis and cost-effectiveness constraints. Basic and Instance metrics tiers are excluded, keeping ingestion charges minimal.

Adaptive joins let the optimiser defer the hash-versus-nested-loops decision until after the first input is scanned, choosing based on actual row counts. This corrects poor join choices caused by inaccurate cardinality estimates at compile time.

High total_logical_reads means the query is pulling many 8 KB pages from the buffer pool, indicating it scans more data than necessary. This points to missing or ineffective indexes forcing large scans, satisfying the stem's constraint of diagnosing poor query performance.

Azure Monitor metric alerts evaluate platform metrics such as DTU consumption against thresholds over a defined window, satisfying the 80% for 10 minutes condition. DTU percentage is emitted automatically as a metric, so no diagnostic logging or query is needed, unlike Log Analytics-based alerts.

Updating statistics gives the query optimiser accurate cardinality estimates, so it can choose index seeks or better join orders instead of scanning large tables. This directly addresses the stem's table-scan symptom while remaining within the existing General Purpose tier, satisfying the no-added-cost constraint.

A nonclustered index stores the WHERE-clause key values in a separate B-tree structure, letting the engine seek directly to matching rows instead of scanning every page. This sharply reduces the pages touched per execution, directly lowering logical reads — the metric the stem asks you to minimise.

Sustained log write percent above 90% signals the transaction log is saturating its provisioned throughput, a resource-bound bottleneck that scaling up the service tier or compute size directly relieves. This metric isolates log I/O pressure rather than general CPU load.

Disabling dropIndex prevents automatic tuning from dropping indexes while leaving other tuning options active. This satisfies the requirement to stop the specific action causing slow queries while still benefiting from automatic tuning for index creation and plan regression correction.

In the General Purpose service tier, the maximum log write throughput scales with the number of vCores. Increasing from 4 to 8 vCores doubles the log write throughput limit, directly addressing the bottleneck without changing the service tier. This is the correct action because the scenario specifies that the service tier must remain the same, and scaling vCores is the only way to increase log throughput within General Purpose.

The high log write percentage indicates that the database is approaching the log write throughput limit for its current service tier and compute size. Scaling up increases the log write rate limit, directly addressing the bottleneck. Since CPU and data IO are low, the workload is log-write-bound, so a higher tier or more vCores will provide more log throughput and improve report performance.

In-memory OLTP stores hot tables and their indexes in memory-optimised structures, so transactional changes are applied without conventional page-latch logging. This directly reduces the volume of log records generated per transaction, lowering WRITELOG waits caused by log-write throughput saturation on the target Azure SQL Database.

PAGELATCH_EX waits on tempdb allocation pages (such as PFS, GAM, SGAM) occur when many concurrent sessions allocate space in tempdb. Adding more tempdb data files distributes the allocation load across multiple files, reducing contention on allocation pages. This is a standard remedy for tempdb allocation bottlenecks. Since the service tier cannot change, adding files is the appropriate action to alleviate the contention without scaling up.

Index Spool (Lazy Spool) indicates the optimiser repeatedly rewinds intermediate results because no suitable index supports the joins and predicates. Creating indexes on the join and filter columns gives the engine seek access, eliminating the spool and reducing the millions of rows scanned.

FORCE_LAST_GOOD_PLAN is an automatic tuning feature in Azure SQL Database that monitors for plan regressions and automatically forces the last known good plan when a regression is detected. It is specifically designed for the scenario where a query becomes slower due to a plan change, which matches the reported symptom.

Query Store automatically captures query plans and runtime statistics, and its Regressed Queries view compares plan performance over time to highlight queries whose plan changed and became slower. Because the feature is built into the database, no external monitoring tool is required, and it directly answers which queries regressed after the statistics update.

Scaling up raises the provisioned compute ceiling, so the same workload consumes a smaller proportion of available DTUs. This directly satisfies the stem's constraint of sustained consumption above 90% during peak hours, where the database is genuinely resource-bound rather than suffering from inefficient queries or missing indexes.

Automatic plan correction detects regressions where a previously good plan is replaced by a worse one, then forces the last known good plan using FORCE_LAST_GOOD_PLAN. This directly satisfies the requirement to automatically force a better-performing plan, which plain automatic tuning options such as CREATE INDEX or DROP INDEX do not provide.

The storage_percent metric in Azure Monitor for Azure SQL Database reports the percentage of storage space used relative to the maximum data size. Setting an alert on this metric at 90% directly meets the requirement. This metric is specific to Azure SQL Database and is the correct signal to monitor storage utilization.

Query Store captures execution plans and runtime statistics, letting you identify the regressed plan causing high CPU and force a superior one via a plan guide. This directly addresses the stem's constraint: statistics and indexes are already refreshed, so the remaining lever is plan choice, not stale metadata.

Minimal logging reduces log space for large imports under full recovery model.

Serverless with auto-pause suspends compute after inactivity and resumes on connection, so SalesDB only incurs compute cost during the 8 AM to 6 PM window. Storage charges persist, but idle overnight and weekend compute is eliminated.

The Business Critical tier uses local SSD storage and has a significantly higher maximum log rate compared to General Purpose. This directly addresses the log write wait issue by providing faster log throughput. While it increases cost, it is the correct action when the log rate is the bottleneck and needs to be raised beyond the General Purpose limit.

Query Store persists execution plans, runtime statistics and wait categories per query, letting you rank statements by CPU, duration or logical reads. This directly identifies the queries driving high DTU consumption on the Azure SQL Database.

Automatic tuning detects plan regressions by comparing query performance over time and forces the last known good plan, restoring throughput without manual intervention. This directly addresses the regression-correction benefit, distinct from index creation or parameter forcing.

FORCE LAST GOOD PLAN automatically reverts a query to its previous good execution plan when a regression is detected, and it is applied without approval once automatic tuning is enabled. It satisfies the stem's constraint of recommendations applied automatically.

`sys.dm_db_file_space_usage` returns page counts per database file, split into allocated, unallocated and mixed-extent categories, letting you track space consumed within each data and log file. This directly satisfies the requirement to monitor storage space usage on the managed instance, since it exposes per-file consumption without relying on instance-level or host-level metrics.

Queries With High Variation highlights queries that have multiple execution plans with significantly different performance metrics. This directly addresses the scenario where a stored procedure sometimes runs fast and sometimes slow. From this report, you can drill into the query and force the better-performing plan, resolving the inconsistent performance.

The Business Critical service tier uses local SSD storage, which provides lower I/O latency compared to the remote storage used in General Purpose. This directly reduces PAGEIOLATCH_SH waits by improving I/O performance. Therefore, switching to Business Critical is the most effective action to address these waits.

Replacing the 10-year policy with a 7-year monthly retention schedule satisfies the compliance requirement while eliminating three years of unnecessary stored backups. Long-term retention policies are configurable per database, so the change directly reduces storage consumption without affecting the mandated retention window.

avg_data_io_percent reports the percentage of the data-file IOPS limit consumed, directly exposing data disk saturation. Monitoring it identifies a disk IO bottleneck caused by read or write activity against data files, satisfying the stem's requirement in Azure SQL Database.

WRITELOG waits occur when sessions wait for transaction log writes to complete. A high proportion indicates the log's I/O subsystem cannot keep pace with commit throughput, making slow log storage the direct cause rather than data file or CPU contention.

sys.dm_exec_query_stats aggregates cumulative execution statistics per cached query plan, exposing total_worker_time as the CPU metric. Querying it ordered by total_worker_time DESC yields the top CPU-consuming queries, satisfying the requirement to rank queries by total CPU time over the last hour.

Long-term retention writes backups to an Azure Storage account using a SAS token embedded in the ARM template. If that token has expired or is malformed, the storage account rejects access, producing exactly the accessibility failure described.

Updating all statistics on the table refreshes the histograms and density information for every index and column, giving the query optimizer a current view of data distribution. With accurate cardinality estimates, the optimizer can determine that the predicate is selective and choose an index seek instead of a scan. This is the targeted fix when a plan becomes suboptimal due to stale statistics, and it is the only option here that directly addresses the optimizer's input.

In Hyperscale, restore operations involve attaching the database to the existing page servers and then replaying the transaction log to bring the database to a consistent point. The amount of log that must be replayed directly impacts the restore duration. A larger log tail results in longer restore times.

Active Geo-Replication replicates user databases only; the logical server's master database, which holds server-level SQL logins, is not synchronised to the secondary server. After failover, those logins are absent, so authentication fails. Contained database users, stored inside the user database, would replicate and continue working.

Changing to asynchronous commit mode allows the primary to commit without waiting for the secondary to harden the log. This directly reduces performance impact on the primary while still maintaining disaster recovery capabilities, albeit with possible data loss.

Missing indexes force full table or index scans, so creating them lets the query optimiser seek directly to matching rows, cutting CPU consumed per query. This satisfies the constraint of reducing CPU usage while remaining on the S3 service tier.

Automatic Tuning continuously monitors query execution on Azure SQL and automatically detects plan regressions, forcing a corrected plan without manual intervention. This directly satisfies the requirement to identify and fix regressed plans for sensitive-data queries.

DTU percentage is the correct metric because it represents the percentage of DTU consumption relative to the database's DTU limit. This directly aligns with the requirement to alert when DTU consumption exceeds 80%, as it provides a normalized view of resource usage.

Per-database minimum and maximum eDTU settings let the marketing database reserve CPU during the day while the sales database draws IO capacity at night, sharing the pool's 200 eDTUs. This matches each workload's peak without over-provisioning, satisfying the cost-optimisation constraint.

Log rate governance throttles to protect secondary replicas; reducing log generation mitigates.

LTR extends retention beyond the default point-in-time window, letting you keep weekly full backups for 5 years and monthly full backups for 10 years independently. Standard short-term retention cannot meet these multi-year durations, so LTR directly satisfies the stated 5-year and 10-year requirements.

Azure Monitor provides free metrics for Azure SQL Database.

Serverless does not support VBS enclaves.

Adding data files spreads insert activity across multiple allocation bitmaps, relieving PAGELATCH_SH contention on last-page insert hotspots. This directly addresses the stem's high PAGELATCH_SH waits, which stem from concurrent inserts competing for the same page and allocation structures within a single file.

The cpu_percent metric in Azure Monitor for Azure SQL Database represents the percentage of CPU used by the database. It is the correct metric to monitor for CPU usage thresholds. Setting an alert on cpu_percent with a threshold of 90 and an aggregation window of 10 minutes will fire when the average CPU percentage exceeds 90% over that period, meeting the requirement.

Azure Monitor metric alerts evaluate platform metrics such as DTU consumption against thresholds on a defined evaluation frequency and aggregation window, satisfying the requirement to fire when DTU exceeds 80% sustained for more than 5 minutes. Azure SQL Database emits DTU percentage automatically, so no instrumentation is needed.

High transaction throughput saturates the transaction log write rate, hitting the Azure SQL Database log rate limit and producing increased log write wait time. Intelligent Insights attributes this specific wait category to log throughput throttling, not to CPU, memory, or storage IOPS pressure elsewhere.

physical_data_read_percent measures the percentage of physical data reads relative to the limit. High values indicate I/O pressure, which can slow query performance. Alerting on this metric helps identify when the database is I/O bound. In the vCore model, this metric is relevant for detecting storage throughput issues. Setting an alert allows you to investigate and potentially optimize queries or scale up.

PAGEIOLATCH_SH waits occur when a thread waits for a data page to be read from storage into the buffer pool, so sustained high wait_time_ms points to slow read I/O rather than CPU, locking or memory pressure. This satisfies the stem's read-operation bottleneck constraint.

Query Store persists execution plans, runtime statistics and wait categories per query, letting you identify the regressed plan and the dominant wait type causing the slowdown. It provides the historical plan comparison that Intelligent Insights alerts alone do not expose.

Log IO percentage from sys.dm_db_resource_stats exposes the ratio of log write throughput to provisioned log IOPS, revealing transaction-log write saturation before commit latency degrades. This directly satisfies the stem's proactive detection requirement, since sustained high log IO percentage signals the log subsystem is throttling writes and performance issues are imminent.

db3 is a paused Azure SQL database, so resuming it requires the dedicated resume subcommand with the resource group, server and database names. The command az sql db resume --resource-group rg1 --server server1 --name db3 supplies exactly those parameters, restoring db3 to an available state.

Query Store must be enabled to capture query execution statistics. By default, it may be off or in read-only mode. Enabling it ensures that query plans and runtime statistics are collected, which is necessary to identify top resource-consuming queries. This is a prerequisite for using Query Store for performance analysis.

Automatic tuning provides a history of applied recommendations and allows you to revert a specific change. After reverting the index creation, you can disable the CREATE INDEX tuning option to prevent automatic tuning from creating indexes in the future. This targeted approach addresses both the immediate issue and the future prevention without disabling other tuning features.

Tempdb contention often arises from allocation bottlenecks when many concurrent connections create and drop temporary objects. Adding more tempdb data files distributes the allocation load across multiple files, reducing contention on allocation pages. This is the recommended approach for resolving tempdb contention in Azure SQL Managed Instance.

Query Store captures per-query runtime statistics, including CPU, duration, and logical reads, persisted across plan changes. The Top Resource Consuming Queries report ranks statements by total consumption, directly isolating which queries drive the high resource usage reported in the stem, rather than merely confirming that consumption is elevated.

Query Store persists historical execution plans and runtime statistics, letting you compare a query's performance before and after the automatic plan correction. This directly satisfies the requirement to verify improvement, since the regression and forced plan are both recorded with their respective metrics.

Query Store persists query, plan and runtime statistics in the database itself, giving historical performance data and plan comparison across index changes. Custom retention settings keep that history long enough to compare before-and-after behaviour on the Managed Instance.

Azure Monitor provides platform metrics for Azure SQL Managed Instance, including CPU percentage. You can create an alert rule that evaluates the average CPU percentage over a 10-minute window and triggers when it exceeds 80%. This is the native, straightforward method to achieve the requirement without additional configuration.

FORCE_LAST_GOOD_PLAN detects plan-choice regressions by comparing a query's performance against its previous good plan, then forces the earlier plan automatically. This satisfies the OLTP requirement to correct regressions without manual intervention, unlike CREATE INDEX or DROP INDEX tuning options.

The yearly retention period governs how long yearly full backups persist; setting it to P10Y retains them for ten years, satisfying the compliance requirement. Weekly, monthly, and daily retention values do not extend coverage to a decade.

Query Store captures query plans, runtime statistics, and history, allowing you to identify plan changes and performance regressions over time. You can pinpoint the stored procedure and see when its plan changed and how performance degraded. This is the ideal tool for diagnosing regressions because it retains historical data and provides built-in reporting for plan changes and top resource consumers.

Scaling up to S3 increases the allocated DTUs and associated compute, memory, and IO resources for the same database, relieving peak-hour contention without application changes. Vertical scaling within the Standard tier is the direct remedy when the current service objective is the bottleneck.

Increasing to S3 during reporting period provides more resources, improving performance without a permanent cost increase. This is the most feasible option given the tier limitation.