Previously: we halved compute by understanding a spiky load profile. Cutting CPU was only half the picture — the database's size and its indexes were the other half.
Two things about fragmentation stood out early. First, there was no regular index maintenance job at all, so fragmentation simply accumulated. Second — and more fundamental — several of the largest, busiest tables used a random uniqueidentifier (GUID) as their clustered primary key.
Here's why that matters, briefly. A clustered index defines the physical order in which rows are stored. With a random GUID, new rows don't land at the end of the index; they land in arbitrary positions, which forces page splits and constant fragmentation. On these tables, even a full defragmentation was undone within hours under load. To be fair, a GUID clustered key isn't always a mistake — but for large, high-insert tables, a random clustered key has real consequences (and a real price). And, as I said at the start: this is evolution, not criticism — the original choice had its own context.
How we tackled it
The strategic answer, which we're working on now, is to refactor and migrate these tables to a partitioned design. Partitioning lets index maintenance happen per partition instead of rebuilding a multi-terabyte index in one piece — and it should also let us drop some indexes altogether, since partitioning can take over work they currently do. This is a strategic task, not a quick one, precisely because of the scale: even today, after the cleanup, the five largest time-series tables together hold around 10 TB including indexes and still aren't partitioned — big enough that maintaining them means heavy, manual, resource-hungry work rather than something we can automate.
But we needed relief sooner, so we started with the safe, quick wins. First, I set up and configured automatic index maintenance for the smaller indexes and rebuilt them — cheap, low-risk, and no need for much free space. Then came a step that unlocked everything after it: we removed a few large, unused indexes (more on how we chose them below), which freed several terabytes. That mattered for more than cost — before it, used space almost equalled allocated space, so a big rebuild would have had nowhere to work without inflating the database's allocated size (the very number we were trying to bring down). Clearing those indexes first created the headroom the rebuilds needed, and avoided that inflation — there's more on this in the next part.
With that room available, we could rebuild the large non-GUID indexes. Even this was a multi-week effort — these indexes are huge — run as resumable index rebuilds during off-peak hours, watching for blocking and resource pressure as they went.
The large GUID-based clustered indexes were a harder case, and rebuilding them only made sense after fixing their root cause. The truly correct fix would be to move off these GUID keys altogether — and we will, once the tables are refactored and partitioned. But that's a large, invasive change, and we needed something faster that would also free up space. So, as an interim step, we changed how their key was generated: we let the database produce a NEWSEQUENTIALID value at insert time instead of the application assigning a random GUID beforehand, and refactored the stored procedures and the C# / Entity Framework code accordingly. New rows now write compactly toward the end of the index, sharply reducing page splits. Only then was it worth rebuilding these indexes — otherwise the random key would have re-fragmented them almost immediately. That was another multi-week effort, one index at a time, each running for days.
There was a final, related optimization. After moving to sequential keys, I looked at wait statistics and saw PAGELATCH_EX waits — classic last-page insert contention, which sequential keys can introduce under very high concurrency. So on the hottest table I enabled the index option OPTIMIZE_FOR_SEQUENTIAL_KEY, and once the effect was confirmed, applied it to other suitable tables. Neither change is the whole answer on its own — but together they added up.
Choosing which indexes to remove
Deciding what to drop needed care: dropping the wrong index is how you cause an outage. We found roughly 160 indexes that appear unused, but we did not drop them all at once. Stability comes first, and some are genuinely seasonal — a few are tied to invoicing or financial periods and only get used a couple of times a year. The ones we removed were the largest and most clearly safe, and even then we checked each carefully (one had been built for a specific client, so we confirmed with them).
The rest of the unused indexes were a different case. Together they came to under 5 GB — negligible on storage at this scale, but still overhead on every write — so instead of dropping them outright, we set up multi-month usage monitoring, snapshotting index usage every week or two, so that we build a conservative drop list only for indexes that are truly unused. Crucially, the monitoring has to span at least one full invoicing period, since some indexes only wake up then. It's been running for over a month and still lists 150+ drop candidates.
There's a broader point here: an index is not just disk space. Every index adds overhead on INSERT, UPDATE, and DELETE, because the engine has to keep it current. Removing an unneeded index gives you two wins — less storage and cheaper writes. Creating an index for every query is a tempting instinct, and the wrong one — and so is blindly following automated tuning advice (the Azure portal, for instance, will happily suggest a new index to speed up a query). Each index has a cost, so the goal is the right indexes, not the most; a recommendation is a starting point for judgement, not a command to follow.
We also looked at the quality of an index's usage, not just whether it's used at all: seeks and scans versus updates. An index used once a month but updated millions of times a day is economically wrong — the cost of maintaining it dwarfs its value. Such an index should be changed, dropped, or replaced by another way of getting the same result.
A concrete example
One index existed to support the SSIS synchronization I mentioned earlier — the process that keeps this database in step with another of our systems. To compare the two sides, the sync reads rows from our table, checks them against the source by a set of rules, and writes back the differences; this index was there to make the read-and-check step on our side efficient. It was about 3 TB, nearly 100% fragmented (even defragmented it would be 1.3 TB), and updated millions of times a day — which is what first caught our eye. This one our Team Leader and I worked through together, from spotting it to shipping the fix. And the key insight wasn't a tuning trick; it was business context. The table holds roughly 16 billion rows, but only about 49 million of them — well under 1% — actually needed to be synchronized. We didn't need to index everything; we only needed to index the rows that participate in the sync. A new filtered index brought it from 3 TB down to under 5 GB.
And there was a nice side effect. Fixing this on the database side made the synchronization itself faster and more stable — fewer crashes, less load while it runs. Database optimization often reveals improvements well beyond the database layer.
Next: reclaiming the space itself — shrink, storage, and backup cost in Hyperscale.
Originally published as a LinkedIn post series, "The Database Marathon."
Top comments (0)