Nightly SQL Latency Spike on a Rebuilding Node
A seven-node cluster runs 280 VMs. A business-critical SQL VM has latency spikes every day at 02:00 for about 25 minutes. During the spike: VM CPU ready is 18%, host CPU is 88%, storage p95 latency rises from 6 ms to 42 ms, CVM CPU is 75%, and network throughput is 4 Gbps of 20 Gbps. One node has been rebuilding a failed SSD for 6 hours and is 70% complete. A snapshot job runs at 02:00 across 40 VMs. DRS is set to manual, and no VM has migrated in 30 days. The SQL VM runs on the node that is rebuilding. The backup job writes to the same cluster.
1. Rank the likely contributors to the SQL latency spike and justify each with the metrics.
Read the worked answer
The primary contributor is resource contention on the rebuilding node. The SQL VM is on that node, and the rebuild is only 70% complete after 6 hours, so it is consuming disk and network I/O while the CVM is at 75% CPU. The 42 ms storage p95 latency is consistent with rebuild I/O competing with the SQL workload. The second contributor is the 02:00 snapshot job across 40 VMs, which adds bursty write and metadata load at the same time. The third is CPU contention: VM CPU ready at 18% means the SQL VM is waiting for physical CPU, and host CPU at 88% leaves little headroom. Network at 4 Gbps of 20 Gbps is not the bottleneck.
DRS being manual and no migrations in 30 days explains why the SQL VM was not moved off the affected node. The ranking is rebuild first, snapshots second, CPU contention third, and network last.
2. What sequence of investigation and remediation would you follow during the next 02:00 window?
Read the worked answer
Before the window, confirm the SQL VM's current host and whether it is on the rebuilding node. Check the rebuild progress and I/O rate, the snapshot job's start time and concurrency, and the VM's CPU ready and storage latency history. During the window, capture per-VM storage latency, CVM CPU, host CPU, and snapshot task duration. Do not change multiple variables at once.
Immediate remediation: manually migrate the SQL VM to a node that is not rebuilding, if capacity and affinity rules allow. Stagger or delay the snapshot job for non-critical VMs, and reduce snapshot concurrency. If the rebuild is saturating the node, throttle it if the platform supports it. After the window, compare latency with and without the changes. The key judgement is to remove the SQL VM from the affected node first, because that is the highest-impact and most reversible action.
3. What long-term changes would prevent recurrence?
Read the worked answer
Enable automated DRS or create a mandatory anti-affinity rule that keeps tier-1 workloads off nodes that are rebuilding. Schedule backups and snapshots outside the peak business window and stagger them across containers. Add monitoring alerts for VM CPU ready above 10%, storage p95 latency above 20 ms, and rebuild duration above the expected baseline. Capacity planning should keep enough headroom that a single node rebuild does not consume the cluster's I/O budget.
Also review the backup target: writing backups to the same cluster competes with production I/O. Use a separate backup target or throttle the backup stream. Test a node failure and a rebuild during a controlled window to see whether the SQL VM meets its latency objective. The durable fix is policy and placement, not a one-time migration.