Monitor, configure, and optimize database resources practice questions

Increase the maximum number of concurrent workers.

Why wrong: Worker count does not directly reduce CPU usage.

Reduce MAXDOP to 1.

Why wrong: Reducing MAXDOP may increase serialization and CPU usage.

Increase MAXDOP to 8.

Why wrong: Increasing MAXDOP can increase parallelism and CPU usage.

Set-AzSqlDatabase with -AutoTuningProperties

Why wrong: Set-AzSqlDatabase is for Azure SQL Database, not Managed Instance.

Set-AzSqlInstance with -DtcEnabled

Why wrong: -DtcEnabled enables distributed transactions, not auto-tuning.

Set-AzSqlServer with -AdministratorLogin

Why wrong: This sets server admin, not auto-tuning.

Enable accelerated database recovery.

Why wrong: This reduces rollback time, not log throughput.

Migrate to the Hyperscale service tier.

Why wrong: Hyperscale has lower log write throughput per vCore.

Increase the DTU purchase model to a higher tier.

Why wrong: DTU tiers also have log throughput limits; Business Critical is vCore.

Fragmented clustered index; rebuild the clustered index.

Why wrong: Fragmentation causes scans but not necessarily high CPU; missing index is more likely.

Insufficient memory; increase the service tier.

Why wrong: Memory pressure might cause scans, but missing index is a common cause.

Parameter sniffing; add OPTION (RECOMPILE).

Why wrong: Parameter sniffing causes varying plans, not necessarily scans.

Azure Advisor

Why wrong: Azure Advisor gives recommendations but does not auto-apply.

Intelligent Insights

Why wrong: Intelligent Insights provides proactive analysis.

Query Store

Why wrong: Query Store captures query performance data.

Data classification

Why wrong: Data classification labels sensitive columns.

Server-level audit

Why wrong: Audit logs actions but not query text.

Diagnostic settings for SQLInsights

Why wrong: Diagnostic settings send metric data, not query text.

Blocking

Why wrong: Blocking shows `LCK_M_*` waits.

CPU bottleneck

Why wrong: CPU bottlenecks show `SOS_SCHEDULER_YIELD` waits.

Memory pressure

Why wrong: Memory pressure causes `PAGEIOLATCH` but the primary issue is I/O.

Automatic tuning

Why wrong: Automatic tuning adjusts performance, not monitoring.

Query Performance Insight

Why wrong: Query Performance Insight analyzes query performance.

SQL Assessment

Why wrong: SQL Assessment checks best practices, not real-time alerts.

Set ReadOnlyRouting=1 on the database.

Why wrong: ReadOnlyRouting is not a database setting.

Add the database to a failover group.

Why wrong: Failover groups provide geo-replication, not read routing.

Set ReadScale to 1 on the database.

Why wrong: ReadScale is set at server level.

Reduce MAXDOP to 1.

Why wrong: Reducing MAXDOP may increase serial I/O.

Increase the buffer pool size by adding more memory.

Why wrong: Memory helps page reads, not log writes.

avg_io_stall_ms

Why wrong: I/O stall is an I/O metric.

log_bytes_used

Why wrong: Log bytes used is a log throughput metric.

Enable Transparent Data Encryption (TDE).

Why wrong: TDE increases log writes due to encryption.

Enable Query Store.

Why wrong: Query Store writes to the database, increasing log I/O.

Increase the frequency of database backups.

Why wrong: More backups increase log activity.

Only vCore supports Azure Hybrid Benefit.

Why wrong: Both support Azure Hybrid Benefit.

Only DTU supports elastic pools.

Why wrong: Both support elastic pools.

Increase the vCore count to 24.

Why wrong: More compute does not reduce blocking.

Convert the Orders table to a memory-optimized table.

Why wrong: Memory-optimized tables require application changes and are complex.

Add a nonclustered index on the Status column of the Orders table.

Why wrong: Index on Status does not reduce locking conflicts.

Create a nonclustered index on ProductKey only.

Why wrong: ProductKey index alone does not cover the query.

Partition the table by DateKey.

Why wrong: Partitioning does not guarantee index seeks.

Create a clustered columnstore index on the table.

Why wrong: Columnstore is for DW, not OLTP.

Increase the service tier of the Azure SQL Database to provide more resources.

Why wrong: Increasing resources might improve performance but does not address the root cause of the suboptimal plan; the scan may still occur.

Rebuild the clustered index to reduce fragmentation.

Why wrong: Rebuilding the clustered index can reduce fragmentation but does not guarantee a change from scan to seek if the query predicate is not selective.

Update the statistics on the nonclustered index only.

Why wrong: Updating only the nonclustered index statistics may not affect the decision to use the clustered index scan if the query predicate does not filter on that index's key columns.

Perform an ALTER INDEX REBUILD on the affected columnstore indexes after business hours.

Why wrong: REBUILD is an offline operation that locks the table, impacting availability.

Perform an ALTER INDEX REORGANIZE on the affected columnstore indexes.

Why wrong: REORGANIZE alone does not compress delta row groups and may not fully reclaim space from deleted rows.

Rebuild the entire database by creating a new database and copying data.

Why wrong: This is excessive and causes significant downtime.

sys.query_store_plan

Why wrong: This view shows execution plan details, not aggregated resource consumption.

sys.dm_exec_query_stats

Why wrong: This DMV returns statistics from the plan cache, which may not have historical data.

sys.dm_db_resource_stats

Why wrong: This DMV shows CPU, IO, and memory consumption at the database level, not per query.

Use the Azure portal to revert all automatic tuning recommendations for the past week.

Why wrong: This would remove beneficial indexes as well.

Manually create the missing indexes that were dropped by automatic tuning.

Why wrong: This does not address the problematic index.

Disable automatic tuning for the entire database.

Why wrong: This would stop all automatic tuning, not just the problematic index.

Disable parallelism for the query using the MAXDOP 1 hint.

Why wrong: Disabling parallelism might reduce the spool's cost but does not eliminate it and could slow down other operations.

Increase the DTU or vCore count of the database.

Why wrong: Adding resources may reduce the impact but does not address the root cause of the spool.

Rewrite the query using table hints to force a specific join order.

Why wrong: Forcing a join order may not eliminate the spool and could cause other performance issues.