Big Clustered Indexes
Overview
The clustering key is copied into every row of every nonclustered index on the table. A wide clustering key is therefore not a cost paid once – it is paid again in every nonclustered index, on every row.
This report finds clustering keys over 10 bytes on tables big enough to matter, ranked by what the width actually costs.
Why key width alone is the wrong ranking
A 120 byte key on two million rows matters far less than a 16 byte GUID on a hundred million rows with nine nonclustered indexes.
Nothing on the old page multiplied width × rows × index count – the number a reader acts on. That product is the Carried Weight column, and it is the ranking.
Where to find it
A database-level report. Select a database, then open Big Clustered Indexes. The page title reads Big Clustered Indexes for <database name>.
Reading the chart
The bars rank by carried weight rather than by key width, which is the whole argument of the page: a wide key on a small table with no nonclustered indexes costs nothing, and a narrow one on a large table with a dozen indexes over it costs a great deal.
Reading the grid
| Column | What it is |
|---|---|
| Verdict | Whether the width is actually costing you. |
| Carried Weight | Width × rows × nonclustered index count. The ranking. Drawn with a bar. |
| Key Width | Bytes in the clustering key. |
| Rows | Table row count. |
| NC Indexes | How many nonclustered indexes carry the key. |
| Table | The table. |
| Index Name | The clustered index. |
| Key Columns | What the key is made of – usually where the width comes from. |
| Notes | Context. |
NC Indexes is the multiplier people forget. A wide key on a table with one nonclustered index is a minor cost; the same key with nine is nine times the cost.
The toolbar
| Group | Buttons |
|---|---|
| Top N | Top 25 · Top 100 · Top 500 |
| Minimum size | 1k+ rows · 10k+ rows · 100k+ rows |
Refresh |
The row-count filter exists because a wide key on a small table is not a finding. Raise the floor to cut the noise.
How to read the report
- Read Carried Weight, not Key Width. That is the number that reflects the real cost.
- Look at Key Columns. A
uniqueidentifier, a composite of several columns, or a widevarcharare the usual causes. - Check NC Indexes. The more there are, the more the width is multiplied.
- Consider a narrow surrogate key where the wide key is not needed for range access.
- Remember the trade. Changing a clustering key is a rebuild of the table and every nonclustered index on it.
Common patterns
A uniqueidentifier clustering key on a large table with many indexes. The classic. Sixteen bytes copied into every row of every index, plus the fragmentation a random GUID causes on insert.
A composite key of four or five columns. Often chosen for uniqueness rather than access pattern. Worth asking whether a surrogate plus a unique constraint would serve better.
A wide key on a table with no nonclustered indexes. Carried Weight will be low. Correctly so – there is nothing multiplying it.
Related reports
| Report | Why you would go there |
|---|---|
| Unclustered Tables | The opposite problem – no clustering key at all. |
| Duplicate Indexes | Fewer nonclustered indexes means less carried weight. |
| Unused Indexes | Dropping one reduces the multiplier directly. |
Frequently asked questions
Why 10 bytes? It is the threshold above which the width starts being worth looking at. Narrower keys are rarely the problem.
Is a wide clustering key always wrong? No. If the key matches how the table is actually queried for ranges, the width may be earned. The report gives you the cost so you can judge.
What is Carried Weight exactly? Key width × row count × nonclustered index count – an estimate of the bytes the key contributes across the table’s index structures.
Can I just drop some nonclustered indexes instead? Often, yes – and that is frequently the cheaper fix. Every index removed reduces the multiplier. Check Unused Indexes and Duplicate Indexes first.