Nightly Load Misses Its SLA After a CPU Increase Is Proposed
Fabrikam migrated a 500 GB order database to a 6-ECPU Autonomous Transaction Processing instance with auto scaling enabled. The nightly load inserts 12 million rows and then runs a 40-minute aggregation; it now takes 3 hours and 20 minutes against a 90-minute SLA. The load connects with the LOW service and runs as one session. Database Actions shows CPU at 18% during the load, with wait events dominated by 'resmgr: cpu quantum' and single-block reads. Automatic Indexing has created nine indexes but identifies no new index for the aggregation. Storage is 62% used, and auto scaling has never raised the ECPU count. The team proposes doubling the base ECPU count to 12. The DBA asks for a diagnosis and a lower-cost remedy before the next billing cycle.
1. Diagnose why auto scaling did not help and why doubling the base ECPU count is unlikely to fix the SLA miss.
Read the worked answer
Auto scaling adds CPU when demand rises, but CPU is only 18 percent, so there is no sustained CPU pressure to trigger a useful scale-out. The dominant wait, 'resmgr: cpu quantum', shows the session is being throttled by resource management, consistent with a single serial session on the LOW service. The single-block reads suggest the load is doing row-by-row or index-driven work rather than efficient direct-path or parallel operations. Doubling the base ECPU count raises the ceiling but does not change the serial execution plan, the consumer-group limits, or the I/O pattern. It would mainly increase cost. The correct diagnosis is a concurrency and service-level problem, not a raw capacity problem. Before adding ECPU, verify the execution plan, the degree of parallelism, the insert method, and whether the aggregation is doing repeated scans of the same data.
2. What changes would you make first, in what order, and how would you validate each one?
Read the worked answer
First, move the load to MEDIUM or HIGH so it can use parallel execution and is no longer throttled as a LOW serial session. Set an explicit parallel degree appropriate to 6 ECPUs, enable parallel DML for the insert if the plan supports it, and confirm that the consumer group has a cap that still protects daytime work. Second, examine the aggregation plan: if it repeatedly scans a large table, partition by load date or use a materialized intermediate table so each run scans less. Third, check whether direct-path inserts are possible and whether indexes are being maintained during the load; disable or defer nonessential index maintenance if the design allows. Validate each change with SQL Monitoring and a controlled run, comparing elapsed time, DB time, I/O, and waits. Only after those changes should the team consider more ECPU. The judgment is to fix the serial, throttled execution path before buying capacity that the workload cannot use.
3. What ongoing monitoring would you put in place so this regression is caught before the next SLA breach?
Read the worked answer
Track the load as a named service and consumer group so its DB time, CPU, I/O, and wait classes are visible separately from order traffic. Capture a baseline run in Performance Hub or Real-Time SQL Insights, then alert on elapsed time and DB time versus that baseline rather than only on CPU utilization. Include run-queue and CPU-quantum waits, parallel execution downgrades, TEMP usage, and rows per second. Monitor Automatic Indexing recommendations but do not assume an index is the answer for a large aggregation; review any new index for load-time cost. Schedule a weekly comparison of the load's plan and statistics freshness. Also watch storage growth and the auto-scaling history so a capacity change is based on evidence. The key judgment is that a 90-minute SLA needs an elapsed-time and wait-based alert; a CPU dashboard would have shown 18 percent and missed the impending breach entirely.