Why a Wide Clustered Index Costs More Than You Think
The nightly index maintenance job used to finish before anyone got in. Now it runs long, and nobody added enough rows to explain the extra hour. Somewhere in that database, a wide clustered index is quietly making every rebuild more expensive than it should be.
Why is a wide clustered index a problem in SQL Server? A wide clustered index is expensive because SQL Server copies the clustering key into every row of every nonclustered index on the table, not just the base table itself. The real cost is key width multiplied by row count and by how many nonclustered indexes carry it, not the byte count alone.
The instinct is to open sys.indexes, sort by key length, and blame whichever clustering key is widest. That number alone lies. A wide key on a small table with no nonclustered indexes costs almost nothing to carry. Database Health Monitor's Big Clustered Indexes report ranks tables by what the width actually costs, not by the width itself.
The Real Cost of a Wide Clustered Index
Big Clustered Indexes is one of the reports in Database Health Monitor. It runs against your own servers, and it takes about a minute to have this same screen open on one of them.
Carried Weight is key width × rows × nonclustered index count, and it changes the story completely. A hundred-byte key on a lookup table with two thousand rows is noise. A sixteen-byte uniqueidentifier clustering key on a table with a hundred million rows and nine nonclustered indexes underneath it is not noise, because SQL Server writes that key into every one of those nine indexes, on every single row. Width alone cannot tell the two apart. Carried Weight can.
A wide clustering key isn't automatically wrong. If the table is genuinely queried by ranges on that key, the width might be earned, and the report only tells you what it costs, not whether to change it. That judgment is still yours.
What the Grid Actually Shows
Open the grid and four columns matter more than the rest of it.
| Column | What It Tells You |
|---|---|
| Carried Weight | Key width times rows times nonclustered index count – the ranking itself, drawn as a bar. |
| Key Width | How many bytes the clustering key itself takes up. |
| NC Indexes | How many nonclustered indexes copy that key – easy to miss, and it multiplies everything else in the row. |
| Verdict | Whether that width is actually worth worrying about. |
The report defaults to a noise filter for a reason. A wide key on a table with a thousand rows isn't a finding worth your time, so raising the minimum row count with the toolbar's size buttons – or narrowing to Top 25 by Carried Weight – keeps the list to tables that are actually worth a rebuild.
Three shapes show up again and again:
- A
uniqueidentifiersitting at the head of a big, heavily indexed table – the classic case, and the priciest one. - Four or five columns stitched together into one composite key, usually chosen for uniqueness rather than how the table gets queried.
- A wide key on a table nobody indexed further – technically wide, but Carried Weight stays low because there's nothing multiplying it.
Doing Something About It
Read Carried Weight first, not Key Width – it's the number that actually reflects cost. Then look at what the key is made of: a uniqueidentifier or a four- or five-column composite explains most of the width you'll find. Check how many nonclustered indexes are copying it, because that count is the real multiplier. A narrower surrogate key is one fix, but swapping a clustering key drags a rebuild through the base table and every nonclustered index sitting on top of it, so weigh that cost honestly. Often the cheaper move sits on the other side of the equation: Find SQL Server Unused Indexes Before They Cost You walks through dropping the indexes carrying the weight instead of touching the key that created it.
The full column reference, including how the Top N and minimum-row filters narrow the list, and what each verdict means, is in the Big Clustered Indexes reference.
What to check on your own server
- Find your widest clustering keys and check which ones sit on genuinely large tables
- Count the nonclustered indexes on each of those tables, since that number multiplies the cost directly
- Check whether the wide key is a uniqueidentifier or a composite of several columns, the two usual causes
- Look for unused or duplicate nonclustered indexes on the worst offenders before considering a narrower key
Try Database Health Monitor Today
It finds the clustering key that is secretly bloating every nonclustered index on a table, and puts a real cost next to it instead of a guess. Database Health Monitor shows it on every instance you connect, in the time it takes to open the report.
Download Database Health Monitor and run the Big Clustered Indexes report against your own server. There is nothing to configure first, and you will know inside a few minutes whether it tells you something you did not already know.