Across a lot of client environments the pattern looks the same: a backup job that used to finish in twenty minutes now runs past an hour, a data file that has doubled since last year with no growth anyone can point to in the actual data, and a nightly maintenance window that keeps slipping later. Nobody added that much data. Somewhere on the instance, SQL Server LOB columns in indexes are quietly storing a second copy of something large, and nobody decided that on purpose.
How do I find SQL Server LOB columns in indexes that are wasting disk space? SQL Server LOB columns in indexes duplicate data: a nonclustered index that INCLUDEs a varchar(max), nvarchar(max), varbinary(max) or xml column stores a full second copy of every value in its leaf pages. Measure the index's total size, not its LOB page count alone, because small values are stored in row and hide from that count.
The first place most people look is table size, or a top space consumers query ranked by row count. That is a reasonable instinct and it is almost always the wrong ranking here, because the tables involved are not always large and the duplicate is not in the table at all. Database Health Monitor's Index LOB Columns report exists for the narrower, more useful question: which nonclustered indexes are carrying a second copy of a value the table already stores once.
In this post
- Why SQL Server LOB Columns in Indexes Cost You Twice
- The Number Everyone Checks First, and Why It Lies
- What to Measure Instead
- The Three Verdicts
- Before You Trust a Red Row
- Reading the Grid
- Fixing What You Find
- What This Actually Costs a Business
Why SQL Server LOB Columns in Indexes Cost You Twice
An index does not normally duplicate much. A nonclustered index copies the columns in its key, plus whatever sits in its INCLUDE list, and both are usually a handful of bytes per row. The trouble starts when somebody adds a varchar(max), nvarchar(max), varbinary(max), or xml column to that list, usually to make one query fully covering so it skips a trip back to the table. SQL Server does exactly what it was told. The table still holds one copy of the document, the contract, or the blob. The index leaf now holds a full second copy of it, for every row that has one. Grow that column and you have not just doubled the table, you have doubled what travels in the index too, and both copies ride along in every backup and every restore from then on.
Index LOB Columns 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.
The Number Everyone Checks First, and Why It Lies
Ask around a shop with a disk space problem and the first instinct is almost universal: sort tables by size, or pull a LOB page count for anything holding large objects and rank on that. Both feel reasonable, and both point in the wrong direction here. Sorting by table size finds the table, but the table is not where the duplicate lives, the index is, and a modest table can carry an outsized index if enough of its rows are large documents. Ranking on LOB page counts is worse, because it is quietly wrong rather than just unhelpful. A max column only lands in a separate LOB allocation unit once its value is too big to fit inside an ordinary page. Anything smaller stays in row, inside the index's own pages, counted as ordinary space rather than LOB space. An index that includes a varbinary(max) column full of six kilobyte values reports zero LOB pages, every time, while still carrying a complete second copy of every one of those values. Rank on LOB pages and that index sorts to the bottom of the list, underneath things that matter far less.
What to Measure Instead
The honest number is the index's total size, not just the part that happens to sit off row. That total is the real cost: every byte SQL Server has to write, back up, restore, and keep in the buffer pool for that index, whether the duplicated value fits in a page or spills into its own allocation unit. Off-row bytes are still worth showing, because they are the part you can prove came from the included column rather than from the keys, but they belong inside the total as a share of it, not in place of it. The most useful comparison is not even within one index. It is the index's off-row bytes measured against the table's own off-row storage, on the same scale. When that slice is nearly as long as the table's own off-row footprint, the value really is sitting on disk twice, once for the table and once for the index that included it. When the index's off-row bar is empty but the index itself is large, the values are small enough to live in row, and the row should say so rather than report a misleading zero.
The Three Verdicts
Every carrying index gets sorted into one of three buckets, and the sort is deliberate about which fact wins. An index nobody has read since the last restart is the clear case regardless of size, because there is nothing on the other side of the cost. Among the indexes that are being used, size is what separates the ones worth a change window from the ones to leave alone for now.
| Verdict | When it applies | What it means |
|---|---|---|
| Dead weight | No reads at all since the last restart | Nothing asked for the copy, whatever it costs |
| Heavy | Read, and 100 MB or more | Big enough that removing the include is worth a change window |
| Watch | Read, and under 100 MB | The pattern is there, but not yet worth touching |
The read count wins over the size test on purpose. However small the copy is, if nothing has asked for it since the server started, there is cost on one side of the ledger and nothing on the other, and that changes the conclusion rather than just its size. The size test only decides how urgent a Heavy or a Watch verdict is. It is never used to soften a Dead weight finding into something safer to ignore.
Before You Trust a Red Row
Everything a Dead weight verdict claims rests on usage counters SQL Server keeps only since the last restart. Restart the service, and every index looks unread for a while, not because nothing wants it but because nothing has asked yet. A server patched at two in the morning can show a page full of red by nine, and none of it means what it looks like until the instance has run a full business cycle, weekly jobs included. The same caution applies to any database that could not be read at all, whether it was offline, mid-restore, or simply not granted to the login running the check: skip it, note it, and do not let its absence read as a clean bill of health. A database that was not counted is not the same thing as a database with nothing to find.
Reading the Grid
The chart tells you where to look. The grid is where you confirm it, row by row, with a bar of its own split the same way as the chart. Each row carries the database and schema-qualified table, the index name, and which large object columns it includes, because a table with several duplicating indexes needs to be told apart. Reads, writes, and how long ago the index was last touched sit at the right, and that last column is doing more work than it looks like: it is the fact that turns a Dead weight verdict from a guess into something you can act on. Click a bar or a tile and the matching grid row lights up, and the reverse works too, so moving between the chart and the detail does not cost you your place.
Fixing What You Find
Two fixes cover almost everything on this page, and they are not interchangeable. The one that applies almost everywhere is to rebuild the index without the large object column in its INCLUDE list, keeping the same keys, the same remaining includes, the same filter, and the same filegroup, using DROP_EXISTING = ON so the index never disappears out from under a running workload. Whatever query relied on that column being covering now takes a key lookup for it instead, which costs less than carrying every value twice, unless the index was doing real work covering a range scan across that column. That is the one case worth checking before you touch anything: an index with millions of reads and a genuine reason for the include is not a Dead weight finding, it is an index earning its keep in an expensive way, and the fix there is different from the fix for one nobody has opened since the server last restarted.
The other option, dropping the index outright, only makes sense when the whole index has earned nothing, which is what a Dead weight verdict is for. A drop script written defensively carries the read count in a comment right next to the statement, so whoever runs it later can see what they are removing, and it should warn if the index is enforcing uniqueness before anyone runs it against a table where that assumption might be load bearing. ONLINE rebuilds need Enterprise edition and carry their own restrictions on tables with large object columns, so budget for an offline window unless you are already licensed for it.
A few shapes turn up often enough across client servers that they are worth naming outright:
- One enormous, never-read index built around a document column, left behind after the query that needed it changed
- An index including an xml column, which duplicates just as much as a max column but never showed up on older reports
- A wide index reporting values stored in row rather than a byte count, where the copy is real but has no LOB pages to point to
- A small database giving over a large share of itself to one or two of these indexes, easy to miss on a byte ranking and often the cheapest thing on the server to fix
- A Heavy index with millions of reads, which needs the rebuild rather than the drop, and a plan for the key lookups that follow
What This Actually Costs a Business
None of this shows up as an error. It shows up as a backup window that keeps creeping later, a restore test that takes longer to prove out every quarter, and a storage bill nobody remembers approving that size for. We see it most often on tables built around documents, contracts, JSON payloads, or scanned images, where somebody reached for INCLUDE to make one report faster and never went back to check what it cost once the documents themselves grew. The fix is usually cheap. Finding it is the expensive part, because nothing in a standard space report separates a genuine data growth problem from a self-inflicted duplicate, and by the time the pattern is obvious on a disk usage graph, it has often been compounding for a year or more across every full backup taken since. A half hour spent finding which indexes are carrying a second copy of something large, and which of those nobody has read, is usually the fastest space anyone recovers all quarter.
This report earns its keep as one pass in a wider instance-level sweep rather than a one-time check. If you run it alongside the rest of that sweep, it is worth also looking at why your alert history keeps burying the messages that matter. Why Your SQL Server Alert History Is Full of Noise covers a different kind of noise, but the same habit of checking a report before assuming a metric is telling you the truth. For the exact catalog views, DMVs, and edge cases behind every column on this page, the Index LOB Columns documentation is the full reference.
What to check on your own server
- Check how long the instance has been up before trusting any index shown as never read
- Find nonclustered indexes that INCLUDE a varchar(max), nvarchar(max), varbinary(max), or xml column
- Compare each index's total size against its off-row bytes to see how much of the copy is provable
- Query sys.dm_db_index_usage_stats for reads and writes since the last restart on those indexes
- Rebuild the index without the LOB include using DROP_EXISTING, and save dropping it outright for the ones nothing has ever read
Try Database Health Monitor Today
It finds every nonclustered index on the instance that is quietly storing a second copy of a large document, xml value, or blob, and tells you in bytes what removing the copy would give back. 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 Index LOB Columns 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.