Index LOB Columns Report

Overview

The Index LOB Columns report answers one question across the whole server: where is SQL Server storing the same large value twice?

When a nonclustered index has INCLUDE (SomeColumn) and that column is a varchar(max), nvarchar(max), varbinary(max) or xml, every row’s value is copied into the index leaf. The table stores the document once. The index stores it again. A 40 GB column included in one index is 80 GB on disk, in every backup, and through every restore.

The report finds all of them, measures what each one occupies, and gives each a verdict.

What this fixes

The report used to be a flat ten column grid, and seven things were wrong with it:

• Nothing was measured in bytes. The whole reason this pattern matters is the second copy, and the page said nothing about what any of it cost.
• Four columns could not vary. Max Length was always -1, because the query filtered on max_length = -1 to find these indexes in the first place. Instance Name was identical on every row of an instance report. Index Column Id printed 1 nearly everywhere. Is Nullable changes neither the cost nor the fix.
• The grain was wrong. One row per included column rather than per index, so an index including two of them appeared twice and nothing could be ranked or counted.
• 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 “more than 10,000 rows” filter was applied to that inflated number.
• xml columns never appeared. An xml column is equally legal as an include and is stored off row the same way.
• Alias types were skipped. The type test joined sys.types on user_type_id, which returns the alias name, so a user defined type over nvarchar(max) matched nothing.
• Errors were returned as findings. A database the login could not read was inserted into the results as a row reading No Access / Access denied, sorted into the middle of the list.

---

Where to find it

An instance-level report. Right-click the server in the tree and open Instance Reports → Index LOB Columns. The page title reads Index LOB Columns for <server name>.

No database needs to be selected first – the report reads every user database on the instance itself.

---

The three views

The segmented control at the left of the toolbar switches between three readings of one result set. Switching does not re-run the query, and the choice is remembered across sessions.

View	What it answers
By Database	Which database on this server has the problem. The default.
Cost	Which single index is worth a change window.
Map	Where the space went, in one glance.

By Database opens first, and that is the one deliberate difference from the database level LOB Columns page. Somebody opening an instance report is asking which database to go and look at; a ranked list of individual indexes answers that only by being read all the way down.

Reading the By Database view

One lane per database, ranked by what it carries, with the bar split into the three verdicts and scaled against the heaviest database on the instance.

The two numbers at the right of each lane are deliberately different readings of the same database:

• The bytes are the absolute cost – what you would get back.
• The share of its data files is the proportion – how much of that database is a duplicate.

Those disagree more often than not. A database can carry a third of the bytes another does while giving over a far larger part of itself to them, and the second database is usually the more interesting one. A ranked list of indexes shows neither.

Reading the Cost view

Every carrying index on the instance, flattened across databases, biggest first. The database name comes first on each label and is tinted, because on this page two rows can carry the same table name from different databases.

Each row carries, left to right:

Element	What it is
Coloured stripe and chip	The verdict – Dead weight, Heavy or Watch.
First line	The database, then the schema-qualified table.
Second line	The index name, and which large object columns it includes.
Bar	What the index occupies in total, scaled against the largest one on the page.
Violet segment	The part held off row – the portion provably attributable to the large values.
Hatched bar underneath	The table’s own off-row storage, on the same scale and baseline.
Value	The size, the off-row share, and the read count.

The hatched bar is the finding. When the violet segment above is nearly as long as the hatch below it, the data is on disk essentially twice.

Reading the Map view

A treemap partitioned into database blocks. Tile area is what that index occupies, tile colour is its verdict, and the band across the top of each block names the database and its total. It is the view that survives a server with fifty of these, where any ranked list is a scroll.

---

The verdicts

Verdict	When	What it means
Dead weight (red)	No reads at all since restart	The copy bought nothing. Whatever it costs, nothing asked for it.
Heavy (amber)	Read, and 100 MB or more	Big enough that removing the include would give back space worth a change window.
Watch (teal)	Read, and under 100 MB	The pattern is here, but the bytes do not yet justify a change.

Never read wins over the size test, because it changes the conclusion rather than its magnitude. However small the copy is, nothing asked for it, so it is cost with nothing on the other side.

The size test is deliberately on the index’s total size rather than on its off-row share. An index whose included values all sit in row reports no off-row bytes at all while still duplicating every one of them, and a share test would let that case through as Watch no matter how large it grew.

---

Why the bar is the whole index, not just the LOB pages

The obvious design is to draw lob_used_page_count and call that 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. So 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’s full length is the index’s total size, which is the complete and accurate cost, and the violet segment inside it is the off-row part – LOB plus row overflow – which is the portion that can be proven. Where the values sit in row the violet segment is absent and the row says “values stored in row” rather than claiming a zero.

The grid does the same thing: the Off-Row column reads “in row” rather than “0 B”, because a zero there reads as “no copy here”, which is the opposite of what it means.

---

The header

Element	What it shows
Stat tiles	How many of these indexes and on how many tables; how many databases of how many scanned carry one; what they occupy and what share of the instance’s data files that is; how many have never been read.
Subtitle line	How much of the total is off-row storage, the usage window, how many of how many were fetched, and that there is no size floor.
Amber band	Shown only when something invalidates the reading.

All of the tile figures are computed over every carrying index on the instance, not the Top N, so the headline does not move when you change the Top N button.

The amber band

Two things earn it, and both can appear at once.

A recent restart. Everything the Dead weight verdict claims rests on usage counters that reset when SQL Server restarts. Under 24 hours of uptime, the band says so and asks you to let the server run a full business cycle before rebuilding anything on that basis.

An unreadable database. A database that is offline, restoring, or simply not readable by this login is skipped, and its name goes in the band. Nothing it holds is counted in any figure on the page. This is the fact the old report used to put in the grid as though it were a finding.

---

Reading the grid

Column	What it is
Verdict	Dead weight, Heavy or Watch – filled for the first two.
Database	Which database the index is in.
Index Size	What the index occupies, with an in-cell bar. Violet is the off-row part, azure the rest.
Off-Row	The part held off row, or “in row” where the values are small enough to stay in the pages.
Share Off-Row	That part as a percentage of the index.
Table	Schema-qualified table name.
Index Name	The nonclustered index carrying the include.
LOB Columns	Which large object columns it includes, with their types.
Rows	The table’s row count, from the heap or clustered index only.
Reads	Seeks, scans and lookups since the last restart.
Writes	Updates since the last restart.
Last Read	How long ago the index was last used.

Clicking a bar or a tile selects the matching grid row, and selecting a grid row highlights its bar – the views stay in step.

---

The toolbar

Button	What it does
By Database · Cost · Map	Which view is drawn. Does not re-run the query.
Top 25 · Top 100 · Top 500	How many indexes the grid and the index views carry. Default is Top 100.
Refresh	Runs the scan again.

Changing the Top N re-runs the report. The expensive part of the scan does not depend on it – every database is read either way, and the Top N only decides how much of the result comes back. The stat tiles and the By Database lanes cover everything found regardless.

---

Fixing what you find

Right-click a bar, a tile or a grid row for the two things worth doing, in the order they should be considered:

Copy rebuild script without the LOB include. The ordinary fix. It scripts the same index – same keys, same surviving includes, same filter, same filegroup – minus the columns that are duplicating the large values, using DROP_EXISTING = ON so the index never disappears from under a running workload. A query that needed the column does a key lookup for it instead, which is cheaper than carrying every value twice unless this index was covering a range scan over that column.

Copy DROP script. Only right when the index as a whole earns nothing, which is what the Dead weight verdict means. The script carries the read count in a comment so you can see what you are acting on, and warns you if the index is unique.

Both scripts USE the index’s own database, not the one the connection was opened against, so they can be pasted and run as they are.

ONLINE is left OFF in the generated script. It needs Enterprise, and on a table with LOB columns it has its own restrictions by version – the comment says where to change it.

---

Drilling in

Double-click a lane to open that database’s own LOB Columns report, which is the same report one database deep and has the room to show every index in full.

Double-click a bar, a tile or a grid row to do the same for that index’s database.

Right-click the chart away from a row for the chart-wide actions, including copying the chart to the clipboard as an image.

---

Requirements

This is a live report. It reads catalog metadata and usage DMVs on the instance as they are now.

The login needs to be able to read catalog views in each database it is meant to report on. A database it cannot read is skipped and named in the amber band rather than failing the report or appearing as a result row.

Usage counters reset when SQL Server restarts, which is what the amber band caveats.

---

What gets scanned, and what does not

User databases only. System databases are excluded – a finding in master or msdb is not something anybody is going to rebuild, and their indexes would otherwise compete for lanes with the ones that matter. DBHealthHistory is excluded too.

Only ONLINE databases this login can read. Everything else is skipped in its own error handler, so one unreadable database does not derail the whole scan, and its name reaches the amber band.

Disabled indexes are excluded. They store nothing, so they cost nothing to keep.

There is no size floor and no row floor. The old report ignored tables under 10,000 rows. Now that the report measures bytes, a small table holding large documents is a bigger finding than a large table holding short strings, and the old filter hid exactly that case.

---

How to read the report

1. Start on By Database. Which database carries the most, and which gives over the largest share of itself, are two different answers and both are on the lane.
2. Look for red. Dead weight means nothing has read that index at all. The copy it carries bought nothing, whatever it cost.
3. Check the amber band before acting on any red. If SQL Server restarted this morning, an index shown as never read may simply not have been reached yet.
4. Switch to Cost for the shortlist. The biggest single duplicate on the server is row one.
5. Compare the violet segment against the hatched bar under it. Nearly the same length means the data really is on disk twice.
6. Right-click and take the rebuild script. Removing the include is almost always the right first move; dropping the index outright is for the Dead weight rows.

Common patterns

One enormous index including a document column, never read. The cheapest win on the whole page. Somebody added the column to an INCLUDE list to make one query covering, the query changed, and the copy stayed.

An index including an xml column. These never appeared on the old report at all. xml is stored off row exactly like a max column and costs exactly as much.

“Values stored in row” on a large index. The included values are each small enough to fit in a page, so there are no LOB pages to point at – but the copy is real and is inside the index’s own pages. Compare the index size against what it would need for just its keys.

A small database with a large share. This is the case the By Database view exists to surface. It will never be at the top of a byte ranking, and it is often the easiest thing on the page to fix.

A Heavy index with millions of reads. Something is genuinely using it. Take the rebuild script rather than the drop, and expect key lookups where the column was being covered.

Nothing at all in the list. No index on the instance includes a large object column. That is the good outcome, and the page says what was checked so you know what the answer covers.

---

Where the data comes from

sys.indexes, sys.index_columns and sys.columns to find the indexes, with the type test going through TYPE_NAME(system_type_id) so that xml columns and alias types over nvarchar(max) are both caught.

sys.dm_db_partition_stats twice over: once per index for what that index holds, and once at index_id 0 or 1 for what the table itself holds off row – the reference the copy is drawn against. Row counts come from the same place, restricted to the heap or clustered index.

sys.dm_db_index_usage_stats for reads, writes and last read, joined as a LEFT join. An index with no row in that DMV has taken no user activity at all since the service started, which is the strongest unused signal there is – dropping those rows would hide the very indexes this page most wants to show.

sys.master_files for each database’s data file size, which is what the share on each lane is measured against.

Each database is scanned in its own pass through a cursor, each pass wrapped so an unreadable database is skipped rather than aborting the scan. The whole scan runs under READ UNCOMMITTED, so it does not block anything and is not blocked by anything.

This is one of the heaviest queries on the instance report list, because it reads every table in every database. It is allowed three minutes before it gives up.

---

Related reports

Report	Why you would go there
LOB Columns (database level)	The same report one database deep, with room for every index.
Problem Indexes	The other things wrong with the indexes on this instance.
Missing Indexes	Before adding an index with an INCLUDE, what the engine thinks it wants.
Large Tables	Which tables are large, including the ones these indexes are copying.
Databases by Size	The database totals the shares on each lane are measured against.

---

Frequently asked questions

Why is the bar the whole index rather than just the duplicated part? Because the duplicated part cannot always be separated out. A max value small enough to fit in a page is stored in row, inside the index’s own pages, with no separate counter for it. The total is the complete and accurate cost; the violet segment is the part that can be proven.

Why does a row say “values stored in row” instead of a number? Because the included values are all small enough to be kept in the index pages rather than in a LOB allocation unit. The copy is real – it is just not separately measurable. Printing “0 B” there would read as “no copy here”, which is the opposite of what it means.

Why do the tiles not change when I switch Top N? Because they cover every carrying index on the instance, not the fetched rows. The Top N decides how much of the list you see, not what was found.

Why is a database missing from the lanes? Either it has no index including a large object column, or it is a system database, or it could not be read by this login – in which case it is named in the amber band.

Should I always remove the include? No. If the index was covering a range scan over that column, removing it turns every one of those into a key lookup. The read count on the row is the thing to check first: nothing reading it at all is the clear case.

What about the clustering key being duplicated? That is a different report. Big Clustered Indexes covers a wide clustering key being carried into every nonclustered index; this page covers a large value being carried into one.

Why did the report time out? It reads every table in every database, and three minutes was not enough. Try a smaller Top N, or press F5 to run it again.

---