The Fixes

Part 1 traced a database saturation to a cache‑miss avalanche; Part 2 separated the real causes from the victims and red herrings with layered evidence. This is the payoff — the fixes. What's striking is how small most of them are once you know exactly where to aim, and how much of the skill is in the fixes you decide not to make.

One constraint shaped all of it: this ran on an older, Standard‑edition SQL Server with no automatic plan correction. Newer/Enterprise engines can notice a regressed query plan and revert it on their own. Here, plan stability was a manual craft — which is exactly why the diagnosis mattered so much.

Fix 1 — Recompile the statement that was actually sniffing

The clearest parameter‑sniffing case was a single statement inside the product‑detail procedure — a "related titles" lookup off a temp table. Its history held two plans: a good one at ~9 ms and a bad one averaging ~8.6 seconds (peaking over nine minutes), with roughly 18% of calls landing on the bad plan. Blended, it averaged about a second.

The fix was one line — OPTION (RECOMPILE) on that statement, so it builds a fresh plan for the actual inputs each time instead of reusing a poisoned one. It collapsed to a single ~9 ms plan.

-- recompile the one poisoned statement — not the whole procedure
SELECT TOP 20 r.Title, r.Url
FROM   #RelatedItems r
JOIN   dbo.Catalog c ON c.Sku = r.Sku
ORDER BY r.Rank
OPTION (RECOMPILE);   -- a fresh plan for the actual inputs, every call

Note the surgical scope: recompile the one volatile statement, not the whole procedure. You pay a tiny compile cost only where the inputs actually vary.

Fix 2 — A table variable that lied about its size

Mid‑remediation, the site went fully down again one evening — a useful reminder that incidents don't wait for you to finish. This one traced to a different procedure, behind series pages, that earlier passes had missed. Its problem is a classic: it built its result set in a table variable.

SQL Server keeps no statistics on a table variable, so it estimates one row — always. Give the query more than one row and the optimizer builds a plan (and reserves a sort memory grant) sized for a single row. Under a burst it degraded from ~10 ms to minutes, and piled up into another pool exhaustion: connections 62 → 390, thousands of queued requests, ~9,500 errors.

-- Before: a table variable → the optimizer always estimates 1 row
DECLARE @Items TABLE ( Sku nvarchar(32), Rank int );

-- After: a #temp table → real statistics, right-sized plan + memory grant
CREATE TABLE #Items ( Sku nvarchar(32), Rank int );
-- same data, same query:  avg ~1,400 ms  →  ~0.8 ms

Swapping the table variable for a real temporary table (a heap #temp, which carries actual statistics) let the optimizer size the plan correctly. Same data, same query: average went from ~1,400 ms (max ~11 minutes) to ~0.8 ms — roughly an 1,800× improvement.

An honest aside: our first theory for this one was wrong. We assumed the procedure was loading the whole catalog. Reading the code showed the app rejects invalid series before the procedure ever runs, so it only handled small result sets. The villain wasn't row count — it was the table variable itself under concurrency. Diagnosis is iterative; you keep testing the theory against the code.

Fix 3 — Knowing when not to apply a fix

Remember the query that looked like a victim in Part 2 — the "other books by this author" lookup? The temptation was to slap OPTION (RECOMPILE) on it too. We didn't, and that was the right call.

Its plan was already stable — one plan, ~8 ms, healthy 99% of the time. Its rare multi‑minute spikes lined up exactly with the outage and the nightly data‑import window: it was waiting on a memory grant under pressure — a victim of contention, not a sniffing cause. Recompilation fixes sniffing; it does nothing for a query that's simply being starved of memory by its neighbors. Adding it would have cost overhead and fixed nothing.

The only thing it needed was hygiene: unforcing a stale, orphaned "forced plan" left over from an earlier attempt. (Which points at a broader lesson — plan‑forcing was a failed strategy here. A forced plan had still hit twelve minutes; forcing a bad plan only makes it permanent.) The rule: a fix applied to a victim is worse than no fix — it costs you and it hides the real problem.

Fix 4 — Turn a heap scan into a seek

One table was a heap — no clustered index — with zero indexes at all, a few hundred thousand rows, probed by a single key millions of times (nearly 4.7 million scans since the last restart). Every lookup was a full scan of the entire table.

-- turn a full heap scan into one covering seek
CREATE UNIQUE NONCLUSTERED INDEX IX_Meta_Key
    ON dbo.ItemMetadata (LookupKey)
    INCLUDE (RelatedCount, LastUpdated);
-- single-key lookup:  ~1,270 reads (scan)  →  3 reads (seek)

One covering nonclustered index on that key — including the columns the reader needs — turned the lookup into a single seek that never touches the base table. A single‑key lookup went from ~1,270 logical reads to 3 — about 400× less work per call. (A small real‑world wrinkle: the key column was declared far wider than its actual data, pushing it past SQL Server's clustered‑index key‑width limit, so a nonclustered covering index was the pragmatic choice, with the column‑narrowing left as a follow‑up.)

Fix 5 — Delete the work that wasn't doing anything

Two quieter wins in the same pass on the listing procedures. First, removing a SET TRANSACTION ISOLATION LEVEL SNAPSHOT that wasn't needed — the reads were already NOLOCK, so snapshot isolation was only adding version‑store pressure in tempdb. Second, deleting a dead catch‑all filter predicate that was always false for these pages. Neither is glamorous; both remove real work from a hot path. In a saturation, the cheapest read is the one you don't do.

Fix 6 — The 74 indexes we didn't create

The last fix was mostly a decision. A read replica — a copy of the database used to offload reads during the nightly import window — was missing ~74 nonclustered indexes the primary had. (A known quirk: replication rebuilds the replica's tables with rows and the primary key, but not the extra indexes.) Under the import load, reads routed to the replica were scanning under‑indexed tables. So — copy all 74 over, right?

No. We created ~46, not 74, and the reasoning is the part worth keeping:

1. Only the tables the replica actually reads benefit. The replica served product/catalog reads. Copying the user/order indexes onto it would add write‑maintenance during replication for zero read benefit.
2. The primary itself carried redundant, overlapping indexes. Blindly mirroring replicates the bloat. Several tables had three near‑identical indexes on the same column; we kept the most‑covering one and dropped the subsets.
3. Every index has a write cost. The replica applies a flood of row changes during the import; each extra index is per‑row maintenance on that path. On a read replica you add only what the reads actually seek on.

There was a durability twist, too: those indexes would vanish the next time the replica was rebuilt from a snapshot — unless you flip the replication option that copies nonclustered indexes. The durable fix wasn't the script; it was enabling that option so the replica's indexes track the primary automatically.

What it adds up to

None of these is exotic: a surgical recompile, a table variable swapped for a temp table, one covering index, some dead code removed, and a disciplined index backfill. What made them work wasn't cleverness in the fixes — it was the diagnosis. Knowing which query was the cause, which was the victim, and which was just busy is what let each fix be one precise line instead of a scattershot of "optimizations." On an older SQL Server with no automatic plan correction, that judgment is the job.

And the fix that prevents the next avalanche lives a layer up from the database — the caching and edge story from Part 1. Fix the query and you survive today; fix the cache and you survive the next scraper. A saturated database is rarely just a database problem.

This kind of evidence‑first diagnosis — separating cause from victim before touching a line — is exactly the work we do — see our SQL Server performance tuning service. If your system has a "it gets slow and we're not sure why" problem, that's a good conversation to have.