LOB Columns
Overview
The LOB Columns report finds nonclustered indexes that INCLUDE a large object column
varchar(max),nvarchar(max),varbinary(max)orxml– and ranks them by what they occupy.
That copy is the whole point. An included column is stored in the index leaf, so a 200 KB document that lives once in the table lives twice on disk the moment an index includes it. Every insert and every update to that column writes both copies.
What this fixes
Nothing on the old page was measured in bytes. The report found exactly the right indexes and then declined to say what any of them cost, which left you to open a second tool to decide whether there was anything worth acting on.
Three of the nine old columns could not vary.
Max Length was always
-1, because the query filtered onmax_length = -1to find these indexes in the first place. Index Column ID is an internal ordinal that printed1on nearly every row. Is Nullable changes neither the cost nor the fix.
The row count was wrong. It summed sys.partitions grouped by object_id alone, which adds the table’s rows once per index – so a five index table reported five times its size, and the old “more than 10,000 rows” filter was being applied to that inflated number.
xml columns were never found. An xml column is equally legal as an included column and is stored off row the same way. Those indexes are included now.
Where to find it
A database-level report. Select a database, then open LOB Columns under Real Time. The page title reads Indexes with LOB Columns for <database name>.
The four counts
| Tile | What it is |
|---|---|
| Indexes on tables | How many indexes include a large object column, and across how many tables. |
| Occupied by these indexes | What they take up between them. This is the number the report exists to produce. |
| Share of data files | The same figure as a percentage of the database’s allocated data files. |
| Never read since restart | How many of them have returned no reads at all. Drawn in red when it is not zero. |
The tiles cover every matching index in the database, not just the Top N on screen, so they do not move when the Top N button does.
Reading the Cost view
Each row is one index. The bar’s full length is what the index occupies, against the largest one on the page. The violet segment drawn from the left edge is the part held off row – LOB plus row overflow – and azure is everything else: keys, other includes, in-row pages.
The off-row part is drawn first, from the left, so every violet segment starts at the same baseline and can be compared with the others by length alone.
Underneath, on the same scale and the same baseline, is a hatched bar for the table’s own off-row storage. A violet segment nearly as long as the hatched bar under it is the finding: the data is on disk essentially twice.
Why the bar is the whole index, not the copy alone
The obvious design would be to draw lob_used_page_count and call it the copy. It is wrong, and quietly so.
A max column whose values fit inside a page is stored in row, not in a LOB allocation unit. An index including a
varbinary(max)of six kilobyte values reports zero LOB pages while carrying a full second copy of every one of them.
Ranking on that number sorts the clearest findings to the bottom of the page. So the bar is the index’s total size, which is the complete and accurate cost, and the violet segment is the part that can be attributed to the large values with certainty. Where the values sit in row, the row says values stored in row rather than reporting a zero.
The verdicts
| Chip | Applies when | What it means |
|---|---|---|
| Dead weight | No reads recorded against the index over the window | The copy bought nothing. Nothing has asked for this index, so the duplicated bytes are pure cost. |
| Heavy | The index is 100 MB or more | Large enough that removing the include would give back space worth a change window. |
| Watch | Everything else the report finds | The pattern is here, but the bytes do not yet justify a change window. |
Dead weight wins over the size test deliberately. However small the copy is, if nothing read it, it is cost with nothing on the other side.
Heavy tests the index’s total size rather than its off-row share, for the reason above: an index whose included values all sit in row reports no off-row bytes at all while duplicating every one of them, and a share test would let that case through as Watch no matter how large it grew.
Reading the Map view
The Map view is a treemap where tile area is what the index occupies and colour is the verdict. It answers “where did the space go” in one glance, and it is what survives a database with fifty of these, where any ranked list is a scroll.
Both views are drawn from the same result set, so switching between them does not go back to the server.
Reading the grid
| Column | What it is |
|---|---|
| Verdict | Dead weight, Heavy or Watch. |
| Index Size | Everything the index occupies. Drawn with a bar, split off-row and in-row – this is the finding. |
| Off-Row | The part held in LOB or row overflow pages. Reads in row when the values are small enough to sit in the index’s own pages. |
| Share Off-Row | The same figure as a percentage of the index. |
| Table | The table. |
| Index Name | The index. |
| LOB Columns | Every included max or xml column on this index, in one cell. |
| Rows | The table’s rows, counted from the heap or clustered index only. |
| Reads | Seeks + scans + lookups since the last service restart. |
| Writes | Writes maintaining the index, including both copies of the LOB value. |
| Last Read | When anything last used it. |
One row per index, not per column. An index that includes two max columns is one row with both columns named in the LOB Columns cell.
The toolbar
| Group | Buttons |
|---|---|
| View | Cost · Map |
| Top N | Top 25 · Top 100 · Top 500 |
Refresh |
The view you pick is remembered between sessions.
Scripting the fix
Right-click a bar, a tile or a grid row.
Copy rebuild script without the LOB include is the ordinary fix. It generates the same index – same keys, same surviving includes, same filter, same filegroup – minus the columns that are duplicating the LOB data, using DROP_EXISTING = ON so the index never disappears from under a running workload.
The script sets ANSI_NULLS and QUOTED_IDENTIFIER on before the CREATE INDEX. That is not decoration copied from SSMS: creating a filtered index fails outright without them, and they are off by default in sqlcmd and in a good many Agent job steps, which is exactly where a script like this gets pasted.
ONLINE is scripted as OFF, because it needs Enterprise and because turning it on silently would be a decision made for you on your production server. The comment in the script says where to change it.
Copy DROP script is for the Dead weight rows, where the whole index earns nothing. The script carries the read count in a comment, so you can see what you are acting on.
Copy Chart to Clipboard takes the whole chart at full height, not the strip of it currently scrolled into view.
How to read the report
- Read the second tile. If the total is small, nothing here needs a change window.
- Start at the top of the Cost view. It is ranked by what each index occupies.
- Compare the violet segment with the hatched bar under it. Close to the same length means the column is on disk twice.
- Check the verdict. Dead weight means nothing read the index at all – that one is a drop, not a rebuild.
- Check the amber band. If SQL Server restarted recently, “never read” only means “not read yet”.
- Right-click and take the rebuild script.
Common patterns
A document table with an index that includes the document. The index is nearly all copy. This is the report’s core finding.
An audit or log table with an included payload column. Usually written constantly and read almost never, which is Dead weight with a large number next to it.
Everything shows as never read just after a restart. The counters are young. The amber band says so; let the server run a full business cycle.
An index that occupies almost nothing. Real, and worth knowing about, but Watch means what it says. There is no size floor on this report, so small ones are shown rather than hidden.
A large index whose Off-Row column reads “in row”. The included values are each small enough to fit in a page, so they are duplicated into the index’s own pages instead of into a LOB allocation unit. The copy is just as real; it simply cannot be counted separately from the keys.
Related reports
| Report | Why you would go there |
|---|---|
| Table Space Breakdown | Where the table’s own LOB storage sits in the wider picture. |
| Unused Indexes | Where a Dead weight index belongs if you are dropping it. |
| Inefficient Indexes | The same cost-versus-benefit question, measured in writes rather than bytes. |
| Large Tables | The tables these indexes hang off. |
Frequently asked questions
Why is including a max column a problem at all? Because the value is stored again in the index leaf. You are paying for a second copy of every row’s document, and paying again on every update.
Is it always wrong? No. If a query genuinely needs to cover a range scan that returns the column, the copy is buying something. That is why the report ranks by cost rather than simply listing the pattern, and why Watch exists.
Where do the byte figures come from? sys.dm_db_partition_stats. Index Size is used_page_count; Off-Row is lob_used_page_count plus row_overflow_used_page_count. The hatched reference bar is the same off-row figure for the table’s own heap or clustered index.
Why does an index show 0 off-row when it clearly includes an nvarchar(max)? Because the values are small enough to be stored in row. SQL Server only moves a max value into a LOB allocation unit when it will not fit; below that it lives with the row, and in an index that means a second copy inside the index’s own pages. The report shows in row rather than a zero so this is not mistaken for “no copy here”.
Why did indexes appear that were not here before? Two reasons. xml columns are now found, and the old 10,000 row floor is gone – now that the report measures bytes, a small table holding large documents is a bigger finding than a large table holding short strings, which is exactly the case the floor used to hide.
Why is my row count different from the old report? The old one was wrong. It added the table’s rows once per index.