Previous attempts looked at the heaviest query in terms of processing time, IO, or execution count. Those certainly slowed the server down, but the really issue were application freezes of many seconds and even minutes. Fixing those unblocked the server. The heavy queries are still there, but no one cares.

More technically, before, people focused on individual queries with the hope of finding the one that is slowing down the server the most. When we instead focused on sessions, and which ones block others due to locks and DB transactions, we were able to fix the root causes.

I am simplifying a bit, but that is the jist.

Isn't most performance problems down to the CPU waiting for something? (network - disk - memory - cache)

Sometimes it's the CPU waiting.

Sometimes there is a connection open to the DB which acquired a lot of locks during a long-running transaction. The DB is processing this transaction on one DB connection, and many other DB connections are blocked because they can not access certain tables, pages, or rows. The other sessions are literally doing nothing and are waiting.

Potential ways to improve it:

- Reducing the scope of transactions - committing more often within the transaction - optimizing just the most impactful queries which contribute to the blocking session's runtime (not ones that don't block at all) - loosening the transaction isolation level

And almost all the rest are due to the CPU doing unnecessary work.