Performance Tuning and Monitoring
This domain tests layered diagnosis: is the bottleneck in SQL, the engine, the VM, the Nutanix storage layer, or the network? You must read the right metric before changing a parameter.
Monitoring layers
- Engine: Oracle AWR/ASH and wait events; SQL Server DMVs and Query Store; PostgreSQL pg_stat_statements; MySQL performance_schema.
- VM: CPU ready, memory ballooning, disk latency, and network drops.
- Nutanix: Prism cluster and VM metrics, storage latency, IOPS, and controller utilization; NDB alerts and SLA compliance.
Worked example
Oracle AWR shows high average wait on db file sequential read. First check whether the statement is doing many single-block index reads because of a poor clustering factor or a range scan returning too many rows. Compare that with Nutanix storage latency for the same vDisk. Reasoning: if storage latency is normal and the SQL reads far more blocks than needed, tune the index or predicate; if storage latency is elevated across databases on the same cluster, the problem is infrastructure, not SQL. Change one layer at a time and re-measure the same workload.
Traps
- Tuning SQL when CPU ready or storage latency is the real constraint.
- Using averages that hide tail latency.
- Increasing parallelism without checking CPU and I/O headroom.
- Ignoring queue depth and controller saturation.
- Comparing benchmarks from different block sizes or workload mixes.