Diagnosing degradation with normal CPU and memory

WHAT THIS TESTS Whether you can move past host-level metrics to database-internal wait analysis, the correct lens when sessions are stalled rather than computing.

A GOOD ANSWER COVERS Normal CPU and memory with poor performance is a strong signal that sessions are waiting on something, not doing work. The right methodology is wait-event analysis: look at currently active sessions and what each is waiting on. Inspect lock contention first, a long-running or uncommitted transaction holding row or table locks creates blocking chains where many sessions queue behind one; identify the blocker and the blocked. Examine wait statistics or the database's active session history to see whether time is spent on I/O waits, lock waits, or internal latch and spinlock contention. Check buffer pool or cache efficiency: a falling cache hit ratio means queries are reading from disk, so investigate whether a query plan regressed to scanning or working set outgrew memory. Also consider checkpoint or log-flush stalls and connection pool exhaustion that leaves requests queued client-side.

COMMON WRONG ANSWERS Concluding the database is healthy because CPU and memory are fine, randomly adding indexes, or restarting without finding the blocker.

LIKELY FOLLOW-UPS Reading a blocking-tree; identifying the leading wait event; how a plan flip causes sudden I/O; isolation level effects on lock duration.

ONE CONCRETE EXAMPLE During a slow period, active sessions show dozens waiting on a row lock. Tracing the blocker reveals an application transaction that opened, ran a slow external call, and never committed, holding locks for seconds. CPU stayed low because everyone was simply waiting. Fixing the long transaction, not adding hardware, restores performance.