Optimizing SQL Performance

A core invoice listing query degraded from ~200ms to 4+ seconds as one table crossed a hundred million rows. Timeouts began appearing in approval workflows during month-end close — the worst possible time for finance users.

The table backed nearly every AP screen. Rewriting the application layer was off the table in the short term; whatever we did had to ship inside a normal release window with zero behavioral change.

Execution plan analysis showed a key lookup running millions of times against the clustered index. A covering index on the filter columns with the display columns as includes removed the lookup entirely, and a filtered index handled the archived-rows case separately.

CREATE NONCLUSTERED INDEX ix_invoices_status_tenant
ON invoices (tenant_id, status, invoice_date DESC)
INCLUDE (supplier_id, total, currency)
WHERE deleted_at IS NULL;

Covering indexes trade write amplification and storage for read speed. On a table this hot for reads and comparatively cool for writes, the trade was clearly worth it — but we documented the cost so future writers know it exists.

P95 on the listing endpoint dropped from 4.2s to 60ms. Month-end timeout alerts went to zero and stayed there.

Read the plan before touching the code — the database usually tells you exactly what it's missing.

Fix the biggest lookup first; most slow queries have one dominant cost, not ten equal ones.