CCNA Monitor Optimize Db Questions

Option C is correct because 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.

Option A is correct because Long-Term Retention (LTR) is designed for retaining backups beyond the default retention period, with weekly, monthly, yearly options. Option B is wrong because Point-in-Time Restore (PITR) only allows restore to a point within the retention period, not long-term. Option C is wrong because automated backups have a maximum retention of 35 days.

Option D is wrong because geo-restore is for disaster recovery, not long-term retention.

Option C is correct because REORGANIZE is the recommended maintenance for columnstore indexes to remove rows from deleted bitmaps. Option A is incorrect because REBUILD is more resource-intensive and not always necessary. Option B is incorrect because dropping and recreating is disruptive.

Option D is incorrect because updating statistics alone does not address fragmentation.

Options A, B, and D are correct. Query Store, DMVs, and Azure Metrics are built-in and free. Option C is wrong because SQL Server Profiler is deprecated and not supported.

Option E is wrong because Performance Insights is not an Azure SQL feature.

Option D is correct because auto-pause is only supported for General Purpose serverless databases, and the use of VBS enclave (preferredEnclaveType: "VBS") indicates Always Encrypted with secure enclaves, which is not supported with serverless. Option A is wrong because minCapacity 0.5 is valid for serverless. Option B is wrong because licenseType BasePrice does not affect auto-pause.

Option C is wrong because autoPauseDelay 60 minutes is valid; the default is 60.

Azure SQL Analytics (preview) in Azure Monitor provides a solution specifically for monitoring Azure SQL databases and managed instances, including storage usage per database. Option A is wrong because Query Performance Insight focuses on query performance. Option C is wrong because Automatic tuning is for performance optimization.

Option D is wrong because Intelligent Insights provides proactive diagnostics but not per-database storage monitoring.

Option D is correct because increasing the number of files in the filegroup can distribute page allocation and reduce contention. Option A is wrong because read-only replicas do not reduce primary's latch contention. Option B is wrong because increasing instance size may help but does not directly address PAGELATCH_SH.

Option C is wrong because delayed durability affects log write, not page latches.

Option A is correct because Azure Monitor metric alerts can be configured on DTU percentage. Option B is wrong because SQL Insights is for visualization, not alerting. Option C is wrong because Azure Advisor provides recommendations but not real-time alerts.

Option D is wrong because Log Analytics workspaces store logs but do not natively provide metric alerts.

Option C is correct because high log write wait times typically indicate that the transaction log throughput is a bottleneck, often due to the log rate limit. Option A is wrong because CPU pressure does not directly cause log write waits. Option B is wrong because insufficient storage space causes different symptoms.

Option D is wrong because blocking causes other wait types like LCK_M_*.

Option D is correct because batch mode requires sufficient memory grant; if the query is memory-constrained, it may fall back to row mode. Option A is wrong because compatibility level 130 or higher supports batch mode. Option B is wrong because columnstore indexes support batch mode by default.

Option C is wrong because MAXDOP setting does not disable batch mode.

Option A is correct: Query Store tracks query history. Option D is correct: Azure SQL Analytics (now part of Azure Monitor) provides historical performance data. Option B is incorrect: Dynamic Management Views show current state, not historical.

Option C is incorrect: Intelligent Insights provides summaries, not detailed query history. Option E is incorrect: Extended Events are for real-time tracing, not historical analysis.

PAGEIOLATCH_SH waits indicate I/O latency for reading data pages from disk. Option B is correct. Option A is wrong because PAGEIOLATCH_SH is for I/O, not memory.

Option C is wrong because it is not CPU-related. Option D is wrong because it is not blocking.

Option D is correct because retentionDays set to 0 disables retention, meaning audit logs will not be retained in storage. Option A is wrong because state Enabled is fine. Option B is wrong because emailAddresses is provided.

Option C is wrong because emailAccountAdmins true is fine.

Option B is correct because autoscale settings on the DTU or vCore model allow automatic scaling based on metrics like CPU. Option A is wrong because elastic pools are for managing multiple databases, not auto-scaling a single database. Option C is wrong because elastic jobs are for scheduling tasks.

Option D is wrong because automatic tuning optimizes performance but does not scale resources.

Option A is correct because Query Store captures query performance metrics and allows detailed analysis. Option B is incorrect because Intelligent Insights provides summaries, not detailed query analysis. Option C is incorrect because database-level metrics don't pinpoint queries.

Option D is incorrect because Automatic Tuning applies fixes but doesn't analyze root cause.

Options A, B, and D are correct. Wait statistics (A) show where queries are waiting. Query Store (B) tracks plan regression and performance.

Log IO (D) indicates transaction log throughput bottlenecks. Option C is wrong because it shows storage size, not performance. Option E is wrong because log backup frequency affects recovery, not performance.

Option C is correct because reducing the log write rate by batching transactions or using minimally logged operations (e.g., bulk insert with TABLOCK) can keep throughput under the limit. Option A is wrong because increasing storage doesn't affect log throughput. Option B is wrong because moving to Business Critical is not allowed.

Option D is wrong because enabling accelerated database recovery reduces version store I/O, not log throughput.

Option B is correct because az sql db resume is the correct command to resume a paused database. Option A is wrong because az sql db start does not exist. Option C is wrong because az sql db update can change properties but not resume a paused database.

Option D is wrong because az sql db restart does not exist.

Option A is correct because a large log file can cause log write latency due to file fragmentation and long I/O paths. Option B is wrong because transaction log is sequential write, not random I/O. Option C is wrong because log write latency is primarily about storage, not CPU.

Option D is wrong because increasing log file size further would exacerbate the issue.

Option C is correct because the query filters for queries with average duration > 1000 ms and orders by duration. Option A is wrong because it doesn't identify plan changes. Option B is wrong because it doesn't sort by frequency.

Option D is wrong because it doesn't compare over time intervals.

Option B is correct because geo-replicated secondaries are readable and do not accept write traffic until failover; after failover, the new primary has no geo-secondary configured initially, so writes are not replicated asynchronously, but latency is likely due to the lack of a local secondary. Option A is wrong because service objective is same. Option C is wrong because geo-replication delay affects read scalability, not write latency.

Option D is wrong because the secondary is readable but not writable before failover.

Option D is correct because automatic tuning must be enabled at the server level for Managed Instance, and the FORCE_LAST_GOOD_PLAN option must be set to ON. Option A is incorrect because automatic tuning is not a database-level setting for Managed Instance. Option B is incorrect because query_store_desired_state controls Query Store, not automatic tuning.

Option C is incorrect because automatic_tuning set to OFF disables tuning.

Options A and C are correct. Query Store identifies top resource-consuming queries, and sys.dm_exec_query_stats shows current CPU/memory usage. Option B (rebuilding indexes) is a fix, not diagnostic.

Option D (scaling up) is reactive. Option E (changing recovery model) is not relevant to resource consumption.

Option C is correct because the 'context' should be 'memberName' as a string, but the value has a backslash which is not escaped in JSON; also the classifier name is not referenced in the context correctly. Option A is wrong because importance is correctly set. Option B is wrong because resource percentiles are valid.

Option D is wrong because the classifier is not a built-in role.

Option C is correct because Query Store provides detailed query performance data, including plan regressions and improvements. Option A is wrong because Azure Monitor alerts only notify that a regression occurred, not the improvement. Option B is wrong because the automatic tuning log shows actions taken but not performance metrics.

Option D is wrong because dynamic management views give current stats but not historical comparison.

Option B is correct because Query Store captures historical query performance and allows plan comparison. Option A is wrong because Azure SQL Analytics provides aggregated metrics but not per-query historical comparison. Option C is wrong because Intelligent Insights provides diagnostic analysis but not detailed historical data.

Option D is wrong because Dynamic Management Views (DMVs) provide current state, not historical trends.

Option C is correct because sys.dm_db_index_physical_stats returns fragmentation details for all indexes in a database with minimal overhead when used with limited scanning. Option A is wrong because SHOWPLAN_XML shows query plans, not fragmentation. Option B is wrong because sys.indexes does not include fragmentation info.

Option D is wrong because missing index DMVs suggest new indexes, not fragmentation.

Option A is correct because FORCE_LAST_GOOD_PLAN automatically forces the last known good plan when a regression is detected. Option B is wrong because it creates indexes automatically, which is not about plan regression. Option C is wrong because it drops unused indexes.

Option D is wrong because it corrects nonclustered indexes, not query plans.

Option C is correct because the yearly retention is currently 3 years (P3Y), which is less than 10 years. Option A is wrong because weekly retention is 4 weeks, which is correct for weekly. Option B is wrong because monthly retention is 12 months, which is correct for monthly.

Option D is wrong because the weekOfYear is used for yearly retention, but the retention period itself needs to be increased.

Option B is correct because the password may have expired (if SQL Server password policy is enforced). Azure SQL Database does not support Windows authentication for SQL Agent jobs. Option A would not cause login failure.

Option C is irrelevant. Option D is wrong because the job already uses SQL authentication.

Option D is correct because SSMS can graphically open deadlock graphs from extended events. Option A is wrong because Query Store does not capture deadlocks. Option B is wrong because Azure Monitor collects metrics but not deadlock details.

Option C is wrong because sys.dm_exec_requests does not show deadlock history.

Option D is correct because scaling up to a higher service objective (e.g., S3) provides more DTUs, which directly improves performance without code changes. Option A is wrong because creating indexes requires code changes. Option B is wrong because query store is a monitoring tool, not a performance fix.

Option C is wrong because read scale-out is for read-only workloads.

Option B is correct because deadlock graphs captured by trace flag 1222 are written to the error log. Option A is wrong because deadlocks are not in Query Store by default. Option C is wrong because sys.dm_exec_requests shows current requests, not deadlock history.

Option D is wrong because those DMVs show current sessions, not deadlocks.

Option D is correct because creating a read-only replica allows you to offload reporting queries to the replica, which can be at a lower service tier, reducing load on the primary and improving performance. Option A is wrong because scaling up the primary to S3 during reporting hours increases cost. Option B is wrong because adding indexes helps but may not be sufficient.

Option C is wrong because columnstore indexes are not ideal for OLTP workloads and may affect write performance.

Option C is correct because sys.dm_os_wait_stats provides cumulative wait statistics across all sessions, which can be used to identify top waits. Option A is wrong because sys.dm_db_index_usage_stats shows index usage, not waits. Option B is wrong because Query Store Wait Stats tracks waits per query, but the question asks for top resource waits overall.

Option D is wrong because sys.dm_exec_query_stats shows execution statistics, not waits.

Option A is correct: Page life expectancy (PLE) indicates how long pages stay in cache; low PLE suggests memory pressure. Option B is correct: Memory grants pending indicates queries waiting for memory. Option C is incorrect: CPU percentage indicates CPU pressure.

Option D is incorrect: Data IO percentage indicates I/O pressure. Option E is incorrect: Log IO percentage indicates log write pressure.

Option B is correct because Azure Monitor alerts on metrics like dtu_consumption_percent are cost-effective and meet the requirement. Option A is wrong because SQL Server Agent is for job scheduling, not metric alerts. Option C is wrong because Log Analytics workspaces cost money for ingestion.

Option D is wrong because Elastic Database Jobs are for executing T-SQL across databases, not for alerts.

Option A is correct because larger batch sizes reduce log writes per row. Option D is correct because using delayed durability reduces log flush frequency. Option B is wrong because simple recovery model is not available in Azure SQL Database.

Option C is wrong because minimal logging is not fully supported. Option E is wrong because index rebuilds generate log writes.

Options A and B are correct. High DTU consumption and increased query duration are direct indicators of performance issues. Option C is a security metric.

Option D is a connectivity metric. Option E is an availability metric.

Options A and C are correct. Enabling data compression reduces data size, lowering storage costs. Moving unused data to Azure Blob Storage using Elastic Database Jobs and external tables can also reduce costs.

Option B is wrong because automatic tuning does not affect storage. Option D is wrong because increasing service tier increases cost. Option E is wrong because changing backup retention does not reduce storage for data files.

Option B is correct because Query 2 has the highest average worker time per execution (4500 ms), indicating it is the most expensive per run, and optimizing it could yield significant CPU savings if it runs frequently. Option A is wrong because Query 1 has high total but lower per execution (500 ms). Option C is wrong because Query 3 has low per execution (150 ms).

Option D is wrong because Query 2 is the best candidate.

Options A, B, and D are correct. Read-scale replicas offload reporting, columnstore indexes improve aggregation performance, and result set caching reduces repeated reads. Option C (increasing MAXDOP) could cause parallelism issues in OLTP.

Option E (disabling automatic tuning) is detrimental.

Option B is correct because per-database min eDTU guarantees resources for a database. Option A is wrong because per-database max eDTU limits resource usage, not guarantee. Option C is wrong because DTU increase for the pool may not guarantee resources for a specific database.

Option D is wrong because moving to a different pool may not be efficient.

Option B is correct because compressing transaction log backups reduces log volume indirectly, but more directly, enabling accelerated database recovery (ADR) reduces log IO. Since ADR is not an option here, the best answer is to use compression on backups. Option A is wrong because increasing service tier does not reduce log generation.

Option C is wrong because adding indexes increases log writes. Option D is wrong because backup retention does not affect log generation.

Option C is correct because rebuilding the columnstore index reduces fragmentation and improves performance. Option A is wrong because reorganizing a columnstore index is not supported; only rebuild is. Option B is wrong because dropping and recreating is more disruptive.

Option D is wrong because increasing service tier addresses symptoms not fragmentation.

Options A, B, and D are correct. A long-running transaction prevents log truncation. Replication with transactions waiting to be delivered can hold log space.

A missing index can cause large updates that generate many log records. Option C is wrong because a memory-optimized table uses a different log mechanism but does not directly cause log growth more than disk-based tables. Option E is wrong because page compression reduces log size if anything.

Option A is correct because the exhibit already has defaultAction: Deny and a virtual network rule for the subnet, which allows traffic from that subnet. Option B is wrong because adding an IP rule would allow traffic from the IP, which is not desired. Option C is wrong because setting ignoreMissingVnetServiceEndpoint to true would allow the rule even if the endpoint is missing, which could cause unintended access.

Option D is wrong because changing defaultAction to Allow would allow all traffic.

Option A is correct because sys.dm_exec_query_stats provides aggregated query performance data without adding significant overhead, and high wait stats often point to resource contention. Option B is wrong because Query Store insights require Query Store to be enabled and may not be the first step. Option C is wrong because automatic tuning recommendations are for optimization, not troubleshooting.

Option D is wrong because scaling up is a reactive measure, not a diagnostic step.

Increasing the log rate limit allows more transactions per second. Option D is correct. Option A is wrong because Hyperscale does not have compute replicas for log rate.

Option B is wrong because changing backup retention does not affect log rate. Option C is wrong because increasing max degree of parallelism may not reduce log generation.

Option A is correct because enabling Query Store and forcing a plan that reduces CPU is a non-intrusive way to improve performance. Option B is wrong because it increases DTUs, which changes service tier. Option C is wrong because it adds indexes.

Option D is wrong because scaling up is changing tier.

Option B is correct because you should use Query Store to identify the specific queries causing high CPU and verify the index impact. Option A is wrong because automatic index tuning could be risky without validation. Option C is wrong because scaling up may not address the root cause.

Option D is wrong because the index already exists in the recommendation.

Option D is correct because the exhibit shows the vulnerability assessment is disabled (state: Disabled) and retentionDays is 30, which is less than 90. You must enable it and set retentionDays to at least 90. Option A is wrong because the disabledAlerts list is about alerts not to send, not the scan itself.

Option B is wrong because emailAddresses do not affect scan frequency or retention. Option C is wrong because the state must be enabled, not just retentionDays increased.

Option B is correct because reviewing the plan history in Query Store helps identify why the forced plan is not optimal, and perhaps the regression is due to parameter sniffing or outdated statistics. Option A is wrong because disabling automatic tuning may cause further regression. Option C is wrong because scaling up might be unnecessary if plan issue is fixable.

Option D is wrong because modifying data types is a heavy change.

Option B is correct because switching to the vCore model with Hyperscale tier provides better scalability and eliminates resource contention by separating compute and storage, reducing PAGELATCH_IO waits. Option A is wrong because scaling to S4 only adds more DTU but does not address the architecture limitation causing PAGELATCH_IO. Option C is wrong because adding a read-only replica does not help write-heavy OLTP.

Option D is wrong because increasing storage does not affect DTU limits.

Options A and D are correct because DTU percentage and CPU percentage are standard metrics for autoscaling. Option B is wrong because deadlocks are an event, not a continuous metric. Option C is wrong because log write throughput is not typically used for autoscale.

Option E is wrong because Query Store is not a metric source.

Option C is correct because forcing the previous known good plan via Query Store plan forcing stabilizes performance without code changes. Option A is wrong because index tuning may not revert the plan. Option B is wrong because dropping and recreating the index is disruptive.

Option D is wrong because DTU increase addresses symptoms not root cause.

Option A is correct because creating a clustered columnstore index on the table will significantly reduce IO by using columnar storage and eliminate key lookups; it also provides high compression to minimize storage. Option B is wrong because adding included columns to the nonclustered index will increase storage size and may not fully eliminate lookups. Option C is wrong because partitioning by customer ID can improve maintenance but may not reduce IO for point queries.

Option D is wrong because creating a filtered index on customer ID is not helpful for many different customer IDs.

Options A and C are correct. Query Store can show top queries by CPU over a time period. sys.dm_exec_query_stats provides cumulative CPU time for cached queries. Option B is wrong because sys.dm_os_wait_stats shows waits, not CPU.

Option D is wrong because sys.dm_exec_requests shows currently executing requests, not historical CPU. Option E is wrong because sys.dm_db_index_usage_stats shows index usage.

Option D is correct because Query Store is a built-in, no-cost feature that tracks query performance. Option A is wrong because Azure SQL Analytics is a paid solution. Option B is wrong because dynamic management views require manual querying.

Option C is wrong because SQL Server Profiler is deprecated and not supported in Azure SQL Database.

Option C is correct because Query Store provides detailed query performance data to identify high resource queries. Option A is incorrect because blocking and deadlocks cause waits, not necessarily high CPU/IO. Option B is incorrect because maintenance tasks like index rebuild are scheduled and not typically the first check.

Option D is incorrect because connection pooling issues cause connection errors, not high CPU/IO.