A whole network of websites went slow at once, and not one server was busy.

A network of recruitment sites went soft at the same time. Not one page, not one site: all of them, together, the kind of farm-wide fog that makes an operator assume the servers are drowning and it is time to buy bigger ones. That assumption is expensive, it is common, and in this case it was wrong. Every machine in the farm was bored. The whole farm was waiting on the database, and the database was waiting on a single query.

Every tier was idle except one

On a farm-wide slowdown, read the utilisation of every tier before anything else, and see which one is actually pinned. The load balancers were idling. The web servers were bored. Nothing in the request path was working hard enough to explain a slow site.

This is the tell that ends the capacity argument before it starts. If every tier is idle except the database, a bigger web server buys you a faster idle machine. The constraint was never compute, and no hardware order was going to fix it.

The expensive answer was to buy more machines

More hardware would have scaled the waste, not removed it. The only honest next step was to find out what the database was actually spending its time on.

We aggregated the query history by fingerprint

An application rarely writes the same query the same way twice. The same lookup shows up parameterised several different ways, so counting raw query text hides the offender in the noise. The fix is to aggregate by a fingerprint that survives that inconsistency, then rank every statement by its share of total database time. We ran it across the full query history.

-- reconstructed, de-identified rendering of cleared facts. not a real capture.
-- full query history, grouped by query_hash
-- (a fingerprint that survives inconsistent parameterisation)
SELECT TOP 5
  query_hash,
  SUM(execution_count)                AS calls,
  SUM(total_logical_reads)            AS pages_read,
  SUM(total_elapsed_time) * 100.0
    / SUM(SUM(total_elapsed_time)) OVER () AS pct_of_db_time
FROM   sys.dm_exec_query_stats
GROUP  BY query_hash
ORDER  BY pct_of_db_time DESC;

One row stood out: more than half of everything the database did, spent on one statement. The method is deliberately boring, it does not guess, and it is the same every time.

One query was more than half of the database

The statement that fell out is the kind you would never look at twice. A city lookup, two columns, one filter.

-- reconstructed, de-identified. table/column names renamed from the real schema.
SELECT CityID, CityName
FROM   Cities
WHERE  RoutingPath = @p;

-- Execution plan: Clustered Index Scan (a full scan)
--   Returns:          ONE row
--   Filtered column:  RoutingPath --> no index

The column it filtered on had no index. So every call scanned the whole table to hand back a single row. On its own that is a small cost; repeated on every page view across the whole network, it became the biggest thing the database did.

A one-row answer should read a couple of pages, not the whole table

“A one-row answer should read two or three pages, not hundreds.”

That sentence is the whole diagnosis, compressed to something you can say to the person who owns the code. Nothing else makes a single-row lookup read that much. Not slow disk, not a busy CPU, not a bad night. A one-row answer reading the whole table is a full scan, and a full scan on a filtered column is an unmistakable signature: the column needs an index. We did not need to argue about hardware, we just showed the reads.

One online index, from a scan to a seek

-- reconstructed, de-identified. index name illustrative, on the renamed identifiers.
-- Index the column in the WHERE clause; cover the two columns it returns
CREATE NONCLUSTERED INDEX IX_Cities_RoutingPath
  ON Cities (RoutingPath)
  INCLUDE (CityID, CityName)
  WITH (ONLINE = ON);   -- applied without interrupting the site

-- New execution plan: Index Seek

One index, on the one column the application actually filtered on. It was applied online, without interrupting the sites, and the lookup that had dominated the database became one of the cheapest things it did.

The customer's engineer added to the diagnosis

The best part of this engagement was not our index. It was what happened when the customer's own database engineer joined in and added to the diagnosis: he brought a reader/writer blocking pattern from his side of the system. We folded his fix in with ours, and the two went in together.

“The customer's engineer left the call a collaborator.”

That is the difference between a support ticket and a diagnosis. When the finding is true and you show the receipts, both sides stop defending positions and start improving the platform together.

When every tier is idle except one, stop looking at capacity

The lesson is short and it is the opposite of the reflex. Farm-wide slowness with idle machines is not a capacity problem, it is a software question wearing a capacity costume. Here is what to check before you sign a hardware order.

Through all of it, the machine stayed calm. The load balancers idled, the web servers sat bored, and the fix was one index on the column the application filtered on. The database server was healthy; one statement inside it needed an index.

The instinct on a slow farm is to buy your way out of it. The honest answer is almost always cheaper: read every tier, find the one that is pinned, and go read its queries.