Black Friday slowdown on a 10-million-row entity

Stop adding capacity to the wrong tier. The connection pool increase from 100 to 300 made things worse because more concurrent queries hit an already saturated database, increasing lock contention and context switching. First, revert the pool to a value the database can sustain and add a queue or timeout so requests fail fast rather than pile up. Second, add the missing index on Status after checking selectivity and the query plan; a non-indexed filter on a 10-million-row table explains the full scan and the CPU. Third, limit the order-list screen to smaller pages and require a date range or customer filter before querying. Fourth, enable short-lived caching for the list if the business can tolerate seconds of staleness. Fifth, protect the database with statement timeouts and a read-only reporting path if available. Communicate a Cyber Monday readiness decision by the end of the day: which changes are proven in staging and which are rolled back if latency crosses a threshold.

Redesign the screen around indexed, selective access. Add a composite index on the filter and sort columns used by the list, for example Status, OrderDate, and Id, and verify with the actual execution plan. Change the aggregate to filter on the server and return only the page the user needs, using Start Index and Max Records rather than fetching a large set and paging in memory. Avoid selecting all columns; expose only the columns displayed. For historical orders, archive rows older than the operational window into a reporting store or separate entity so the hot table stays small. Add a materialized summary for counts if the dashboard needs totals. Introduce caching at the aggregate level for read-heavy, low-change data, with explicit invalidation. Load-test with production-like data volumes and concurrency, not a small test database. The reasoning: the current design does work proportional to table size per request; at 10 million rows, only selectivity and reduced data movement will scale.

Set thresholds in three layers. Database: CPU below 70% sustained, average lock wait under 50 ms, no query over 2 seconds on the order path, and connection-pool wait time under 100 ms. Application: p95 order-list latency under 1 second, error rate under 0.5%, and concurrent sessions tracked against the tested ceiling of 1,500. Front-end: CPU below 65%, memory stable, and no thread-pool starvation. Add business metrics: checkout completion rate and orders per minute. Run a load test to 1,500 concurrent sessions with production-sized data before Monday and record the breaking point. Pre-agree scaling actions: add a front-end if app CPU is the limit; add a read replica or offload reporting if database reads are the limit; enable queueing if the database is write-bound. Staff a war room with a DBA and a platform engineer, and review dashboards every 30 minutes. The reasoning: capacity is a chain, so thresholds must identify the weakest link before customers do.