CCNA Monitor, configure, and optimize database resources Questions

Option A is correct because the 'Impact' dimension shows whether performance degradation is due to increased workload or query regression. Option B is wrong because 'Metric' shows the metric name, not root cause. Option C is wrong because 'Resource type' is the Azure resource type.

Option D is wrong because 'Severity' indicates alert level, not cause.

Option A is correct because failover groups with readable secondary allow read-only routing. Option B is wrong because auto-failover groups are for disaster recovery, not read-only offloading. Option C is wrong because SQL Server Integration Services is for ETL.

Option D is wrong because transaction replication requires additional configuration and is not native to Managed Instance.

Options A, B, and C are correct. Using RECOMPILE forces a new plan per execution, optimizing for unknown uses average distribution, and creating multiple cached plans via parameterization. Option D is wrong because query hints force specific plans.

Option E is wrong because it only captures data.

Option B is correct because Azure SQL Database audit logs are stored in Azure Storage and a retention policy can be set on the storage account to automatically delete blobs after a specified number of days. Option A is wrong because the audit retention policy in SQL Database is only for the number of days to retain audit records, but it does not automatically delete from storage; deletion is handled by storage lifecycle management. Option C is wrong because Extended Events are not the primary method for audit log cleanup.

Option D is wrong because backup retention is unrelated to audit logs.

Option A is correct because RESOURCE_SEMAPHORE waits indicate memory grant pressure, often due to large queries requiring memory. Option B is incorrect because disk I/O causes PAGEIOLATCH waits. Option C is incorrect because blocking causes LCK_M_* waits.

Option D is incorrect because CPU pressure causes SOS_SCHEDULER_YIELD waits.

Scaling up to a higher service tier (e.g., S4) provides more CPU resources. Option A is correct. Option B is wrong because the database is not at the maximum tier (S12 is max for Standard).

Option C is wrong because enabling read scale-out does not increase CPU. Option D is wrong because query store recommendations take time.

Option A is correct because sys.dm_exec_requests shows currently executing requests with elapsed time. Option B is wrong because it shows query statistics, not currently running queries. Option C is wrong because it shows resource usage.

Option D is wrong because it shows session-level waits.

Option B is correct because Query Store provides detailed plan history to identify plan regression. Option A is wrong because index tuning may not address plan regression directly. Option C is wrong because restarting the database is disruptive and not a troubleshooting step.

Option D is wrong because increasing DTU does not fix plan regression.

Option A correct because Azure Monitor alerts can alert on DTU. Option C correct because audit logs track failed logins. Option D correct because Query Store retains query performance data.

Option B wrong because SQL Assessment checks best practices, not performance. Option E wrong because Autoscale is not manual monitoring.

Options A, B, and C are correct. Automatic tuning options include CREATE INDEX, DROP INDEX, and FORCE LAST GOOD PLAN. Option D is not an automatic tuning option; it's a manual feature.

Option E is not part of automatic tuning.

Option B is correct because increasing the log rate limit allows more log throughput, reducing latency. Option A is incorrect because changing tier to General Purpose would reduce performance. Option C is incorrect because data compression does not affect log writes.

Option D is incorrect because enabling Accelerated Database Recovery does not reduce log write latency.

Options B and D are correct. Adding multiple data files distributes allocation pages (PFS, GAM, SGAM), reducing contention. Using a hash partition on the table spreads data across files.

Option A is wrong because read-only replicas do not affect primary contention. Option C is wrong because columnstore indexes are not directly related. Option E is wrong because increasing max degree of parallelism can increase contention.

Option D is correct because log write latency on the secondary is typically due to network latency between regions. Option A is wrong because the primary performance is normal. Option B is wrong because the secondary is readable but not for writes.

Option C is wrong because the secondary does not generate its own log; it applies log from primary.

Option C is correct because the query calculates average duration per query and returns the top 10 by average duration, which identifies the slowest queries. Option A is wrong because it does not filter by time range. Option B is wrong because it does not look at resource waits.

Option D is wrong because it does not include CPU time.

Option B is correct because in Hyperscale, the log service is a separate component that handles redo; high latency there directly affects redo speed. Option A is wrong because compute node CPU affects query processing, not redo latency specifically. Option C is wrong because page servers handle data reads, not redo.

Option D is wrong because storage IO is distributed and rarely the bottleneck for redo in Hyperscale.

Option C is correct because increasing the number of vCores improves the log write throughput and reduces latency, as the log writer can use more CPU and memory. Option A is wrong because reducing the number of replicas is not possible in Business Critical (always 4). Option B is wrong because Premium SSD v2 may not be available in all regions and is not a direct fix for log write latency.

Option D is wrong because turning off automatic tuning does not affect log write latency.

Option A is correct because Azure SQL Database's automatic tuning can automatically implement index recommendations. Option B is wrong because automatic tuning for plan correction is separate. Option C is wrong because it only provides recommendations.

Option D is wrong because Query Store is a monitoring tool, not an automatic tuning feature.

Option B is correct because scaling up to a higher service tier (e.g., S2 or S3) provides more DTUs, improving report performance. Option A is wrong because index maintenance helps but may not be sufficient if resource is the bottleneck. Option C is wrong because read scale-out is only available on Premium/Business Critical.

Option D is wrong because partitioning adds complexity and is not a guaranteed fix.

Option A is correct because the S3 tier has low DTU (100 DTU) and is likely overwhelmed. Option B is wrong because auto-pause delay is set to 3600 seconds (1 hour) and would not cause issues during active use. Option C is wrong because zone redundancy does not affect performance.

Option D is wrong because read scale is disabled but not needed for primary workload.

Option B is correct because isAzureMonitorTargetEnabled is true, meaning audit logs are sent to Azure Monitor. Option A is wrong because state is Enabled. Option C is wrong because retentionDays is 90.

Option D is wrong because isStorageSecondaryKeyInUse is false.

Option B is correct because Query Store can show plan history and regression details, helping analyze why the forced plan is not optimal. Option A is wrong because reverting may cause further regression. Option C is wrong because manual plan guide may be too drastic.

Option D is wrong because scaling up doesn't address plan quality.

Automatic tuning in Azure SQL Database includes FORCE LAST GOOD PLAN and CREATE INDEX. DROP INDEX is not an automatic tuning option. Option A and B are the correct ones.

Option C is wrong because automatic tuning does not include DROP INDEX. Option D is wrong because it includes DROP INDEX.

Options A, C, and D are correct. Query Store, automatic tuning, and dynamic management views are core monitoring and optimization tools. Option B is for security.

Option E is for threat protection.

Options B and C are correct. Columnstore indexes (B) improve analytical query performance. Query Store (C) helps identify regressions.

Option A is wrong because enabling TDE does not improve performance. Option D is wrong because it increases storage but not performance. Option E is wrong because it's for compatibility, not optimization.

Option D is correct because the storage profile has autogrow enabled and storage size is 65536 MB (64 GB). Option A is wrong because version is 16, not 15. Option B is wrong because geoRedundantBackup is Disabled.

Option C is wrong because highAvailability mode is ZoneRedundant, meaning it uses a standby in another zone, not same zone.

Option A is correct because PAGELATCH waits indicate contention on data pages, often due to concurrent inserts to the same page. Option B is wrong because buffer pool insufficient is PAGEIOLATCH. Option C is wrong because blocking is related to lock waits.

Option D is wrong because CPU pressure is not directly related to page latches.

Option B is correct because isIntelligentInsightsOn is set to true. Option A is wrong because isLedgerOn is false. Option C is wrong because there is no indication of Always Encrypted with secure enclaves (preferredEnclaveType is VBS but that alone doesn't mean enabled; the feature must be configured on specific columns).

Option D is wrong because no availability group info is shown.

Option B is correct because scaling up the service tier to a higher DTU level provides more resources, reducing contention and timeouts. Option A is wrong because read scale-out only distributes read-only queries. Option C is wrong because Query Store is a monitoring tool, not a scaling solution.

Option D is wrong because automatic tuning can help with plan regression but not resource shortage.

Option B is correct because high DTU indicates resource pressure; scaling up provides immediate relief. Option A is wrong because indexes may not be the cause. Option C is wrong because Query Store helps identify queries, but scaling is a direct solution.

Option D is wrong because waiting may cause further issues.

Option B is correct because 'Log IO percent' in Azure SQL Database reflects log generation rate. Option A is wrong because 'Data IO percent' measures data file I/O. Option C is wrong because 'CPU percent' measures processor usage.

Option D is wrong because 'DTU used' is for DTU-based tiers, not Hyperscale.

Options A, B, and D are correct. Gen5 and Premium-series differ in IOPS, memory-to-vCore ratio, and compute generation. Option C is wrong because vCore count is not a factor; it's independent of hardware series.

Option E is wrong because both support the same service tiers.

Option A and B are correct. sys.dm_os_wait_stats is a DMV that shows cumulative wait statistics. Query Store also captures wait statistics for queries. Option C is wrong because sys.dm_exec_query_stats shows query performance but not waits.

Option D is wrong because Azure Monitor metrics do not include wait statistics. Option E is wrong because Dynamic Management Views is a category, not a specific method.

Option A is correct because increasing the service level objective (SLO) provides a higher log rate limit, reducing throttling. Option B is wrong because Hyperscale does not allow enabling accelerated database recovery to reduce log generation; it's always enabled. Option C is wrong because using larger transaction batches reduces log rate by reducing commit frequency.

Option D is wrong because partitioning large tables does not directly reduce log write throttling; it may even increase.

Option C is correct because sys.dm_db_resource_stats provides DTU consumption per minute, and sys.dm_exec_query_stats can be joined to find top queries. Option A is wrong because it shows resource consumption per database, not per query. Option B is wrong because it shows per minute, but per query requires sys.dm_exec_query_stats.

Option D is wrong because it shows waits, not DTU.

Option B is correct because Query Store runtime statistics are sent to Azure Diagnostics only if diagnostic settings are configured to collect that category. Option A is wrong because Query Store is enabled. Option C is wrong because the query is correct.

Option D is wrong because the query does not have time filter.

Option A is correct because Query Store is built-in and designed for low overhead query performance monitoring. Option B is wrong because Extended Events can have higher overhead and is more for custom event collection. Option C is wrong because Azure Monitor metrics provide resource-level metrics, not query-level details.

Option D is wrong because SQL Server Profiler is deprecated and has high overhead.

Option B is correct because a scheduled job that runs during the spikes can be optimized by moving it to off-peak hours or tuning it, reducing the spike impact. Option A is wrong because scaling to a higher tier would eliminate spikes but increase cost unnecessarily. Option C is wrong because Query Store helps diagnose but does not resolve the issue.

Option D is wrong because Hyperscale is for large databases, not for smoothing spikes.

Option C is correct because the Query Store is the primary tool in Azure SQL Database for tracking query performance, including CPU, IO, and duration. Option A is wrong because dynamic management views like sys.dm_exec_query_stats can be used but require more effort and may not persist history. Option B is wrong because Extended Events are for custom monitoring, not built-in query analysis.

Option D is wrong because Azure SQL Database does not support SQL Server Profiler.

Options B and D are correct. Read scale-out offloads read queries to a secondary replica; enabling Query Store helps identify performance issues. Option A is a security feature.

Option C is for indexing. Option E is for data retention.

PAGELATCH_EX waits are due to contention on the last page of indexes (often due to sequential inserts). Using OPTIMIZE_FOR_SEQUENTIAL_KEY, partitioning, and using hash or round-robin distributions can reduce contention. Option A, B, and C are correct.

Option D is wrong because increasing MAXDOP may increase contention. Option E is wrong because snapshot isolation does not directly reduce PAGELATCH_EX.

Option C is correct because CPU time directly measures CPU usage per query, helping correlate high duration with CPU pressure. Option A is wrong because logical reads indicate I/O, not necessarily CPU. Option B is wrong because execution count without CPU may miss resource intensity.

Option D is wrong because wait stats indicate blocking, not CPU.

Option B is correct because Intelligent Insights provides a root cause analysis report that can help identify the issue. Option A is wrong because scaling up should only be done after identifying the root cause. Option C is wrong because Query Store may not be enabled by default.

Option D is wrong because checking wait statistics is lower-level; Intelligent Insights already provides a diagnosis.

Query Performance Insight helps identify the top resource-consuming queries. Optimizing those queries is the most direct way to reduce DTU consumption. Option A is wrong because scaling up increases cost and may not address root cause.

Option B is wrong because index maintenance can increase DTU temporarily. Option D is wrong because increasing the log rate limit does not reduce DTU consumption.

Option A is correct because missing indexes are a common cause of scans. Option B is wrong because columnstore indexes are for data warehousing, but you should first analyze workload. Option C is wrong because scaling up is costly and may not be needed.

Option D is wrong because partitioning doesn't reduce scans without proper indexing.

Option C is correct because creating a role with EXECUTE permission on the schema grants execute on all stored procedures in that schema. Option A is wrong because granting SELECT on schema gives read access to tables. Option B is wrong because granting EXECUTE on individual procedures is more administrative work.

Option D is wrong because granting CONTROL is excessive.

sys.dm_db_resource_stats provides per-minute resource consumption data for the database. Option A is wrong because sys.dm_exec_query_stats is not available in Azure SQL Database (it is for SQL Server). Option C is wrong because sys.dm_os_wait_stats is for server-level waits.

Option D is wrong because sys.dm_exec_requests shows only currently executing requests.

Option B is correct because DTU/CPU consumption directly correlates with query performance during peak load. Option A is wrong because deadlocks are rare and not the typical cause of sustained degradation. Option C is wrong because although important, the primary indicator is resource consumption.

Option D is wrong because failed connections indicate availability issues, not performance.

Option C is correct because reducing transaction log writes by batching transactions or using minimally logged operations reduces throughput. Option A is wrong because increasing max log size does not affect throughput. Option B is wrong because Premium tier is not allowed.

Option D is wrong because log rate is not configurable.

Option C is correct because enabling forced parameterization makes the database treat literal values as parameters, allowing query plans to be reused and reducing plan cache bloat. Option A is wrong because plan guides are for specific queries, not the entire stored procedure. Option B is wrong because it's an after-the-fact analysis tool.

Option D is wrong because index tuning is a reactive measure, not a proactive plan stability solution.

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

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

Option D is correct because increasing the number of tempdb data files to match the number of vCores (or slightly higher) reduces allocation contention. Option A is wrong because memory-optimized tempdb metadata is for system metadata, not data pages. Option B is wrong because it only adds more files, but optimal number is vCores.

Option C is wrong because accelerated database recovery does not address tempdb contention.

Option A is correct because the FORCE_PARAMETERIZATION database option forces parameterization and can help with sniffing issues. Option B is wrong because it's for plan forcing after regression, not prevention. Option C is wrong because it's a query hint, not a database-wide setting.

Option D is wrong because it's for index maintenance.

Option A is correct because sys.dm_exec_query_stats provides aggregated performance statistics for cached query plans, including CPU time. Option B is wrong because sys.dm_exec_sessions shows active sessions but not query-level CPU. Option C is wrong because sys.dm_exec_requests shows currently executing requests, not historical data.

Option D is wrong because sys.dm_exec_query_plan shows the actual plan, not CPU stats.

Option D is correct because PAGEIOLATCH_SH waits indicate I/O bottlenecks; adding indexes can improve data access patterns and reduce I/O. Option A is wrong because increasing memory will not directly reduce I/O waits if the issue is inefficient queries. Option B is wrong because enabling compression may increase CPU but not necessarily reduce I/O waits.

Option C is wrong because forcing parameterization may help plan stability but not I/O.

Option C is correct because the configuration only includes SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP and FAILED_DATABASE_AUTHENTICATION_GROUP. Option A is wrong because it includes actions not in the config. Option B is wrong because it includes data modification actions.

Option D is wrong because it includes all actions.

Options A, C, and D are correct. Hyperscale benefits from nonclustered indexes on read-intensive tables, accelerated database recovery reduces log IO, and read-scale replicas distribute read load. Option B is wrong because Hyperscale does not support resource governor.

Option E is wrong because increasing service tier is not an optimization action.