Can anyone confirm that if I ran the “ALTER INDEX ALL ON REORGANISE
WITH ( LOB_CAMPACTION = ON )” statement that it would compact the
varbinary(max) LOB column(s) even though they are not present in any
of the non-clustered indexes and only in the underlying heap?
Yes. You can easily confirm this empirically, and we'll do so in a minute.
The rationale behind this would be to reclaim any space freed by the
application job which sets the LOB column to null for qualifying rows.
LOB compaction does not literally reclaim all space freed. Even rebuilding the whole table will not reclaim LOB space -- reorganizing is the best you can do, and that does not reclaim everything. If it makes you feel better: this is not restricted to heap tables, and it's actually a feature, not a bug.
Let me prove it. Let's create a heap table with LOB data:
CREATE TABLE heap_of_trouble(ID INT IDENTITY, lobby VARBINARY(MAX));
-- SQL Server will store values <8K in the row by default; force the use of LOB pages
EXEC sp_tableoption 'heap_of_trouble', 'large value types out of row', 1;
SET NOCOUNT ON;
GO
BEGIN TRANSACTION;
GO
INSERT heap_of_trouble(lobby) VALUES (CONVERT(VARBINARY(MAX), REPLICATE(' ', 4000)));
GO 10000
COMMIT;
SELECT p.[rows], p.index_id, au.[type_desc], au.data_pages, au.total_pages, au.used_pages
FROM sys.partitions p
JOIN sys.allocation_units au ON au.container_id = p.hobt_id
JOIN sys.objects o ON o.[object_id] = p.[object_id]
WHERE o.[name] = 'heap_of_trouble'
+-------+----------+-------------+------------+-------------+------------+
| rows | index_id | type_desc | data_pages | total_pages | used_pages |
+-------+----------+-------------+------------+-------------+------------+
| 10000 | 0 | IN_ROW_DATA | 43 | 49 | 44 |
| 10000 | 0 | LOB_DATA | 0 | 5121 | 5118 |
+-------+----------+-------------+------------+-------------+------------+
Let's clear out some columns:
UPDATE heap_of_trouble SET lobby = NULL WHERE ID % 2 = 0;
And let's get the page count again:
+-------+----------+-------------+------------+-------------+------------+
| rows | index_id | type_desc | data_pages | total_pages | used_pages |
+-------+----------+-------------+------------+-------------+------------+
| 10000 | 0 | IN_ROW_DATA | 43 | 49 | 44 |
| 10000 | 0 | LOB_DATA | 0 | 5121 | 5117 |
+-------+----------+-------------+------------+-------------+------------+
No change, except for one page at the end. That's expected. So now let's reorganize and compact:
ALTER INDEX ALL ON heap_of_trouble REORGANIZE WITH (LOB_COMPACTION = ON);
+-------+----------+-------------+------------+-------------+------------+
| rows | index_id | type_desc | data_pages | total_pages | used_pages |
+-------+----------+-------------+------------+-------------+------------+
| 10000 | 0 | IN_ROW_DATA | 43 | 49 | 44 |
| 10000 | 0 | LOB_DATA | 0 | 3897 | 3897 |
+-------+----------+-------------+------------+-------------+------------+
You'll notice the number of pages is not half of what we started with: the LOB data has been reorganized, but not fully rebuilt.
If you try ALTER TABLE .. REBUILD instead, you will notice that the LOB data is not compacted at all:
+-------+----------+-------------+------------+-------------+------------+
| rows | index_id | type_desc | data_pages | total_pages | used_pages |
+-------+----------+-------------+------------+-------------+------------+
| 10000 | 0 | IN_ROW_DATA | 29 | 33 | 30 |
| 10000 | 0 | LOB_DATA | 0 | 5121 | 5117 |
+-------+----------+-------------+------------+-------------+------------+
Note how the IN_ROW_DATA has been rebuilt, but the LOB data has been left completely untouched. You can try this with a clustered index as well (simply make the ID a PRIMARY KEY to implicitly create one). However, this is not true for non-clustered indexes. Start over, but this time add another index:
CREATE INDEX IX_heap_of_trouble_ID ON heap_of_trouble (ID) INCLUDE (lobby)
Including LOB data in an index is not a normal setup, of course; this is just for illustration. And look what we get after ALTER TABLE REBUILD:
+-------+----------+-------------+------------+-------------+------------+
| rows | index_id | type_desc | data_pages | total_pages | used_pages |
+-------+----------+-------------+------------+-------------+------------+
| 10000 | 0 | IN_ROW_DATA | 29 | 33 | 30 |
| 10000 | 0 | LOB_DATA | 0 | 5121 | 5117 |
| 10000 | 2 | IN_ROW_DATA | 35 | 49 | 37 |
| 10000 | 2 | LOB_DATA | 0 | 2561 | 2560 |
+-------+----------+-------------+------------+-------------+------------+
Surprise (maybe), the LOB data of the non-clustered index is rebuilt, not merely reorganized. ALTER INDEX ALL .. REBUILD will have the same effect, but will leave the heap completely untouched. To sum up with a little table:
+----------------------+---------------+-------------------+----------------------+
| | TABLE REBUILD | INDEX ALL REBUILD | INDEX ALL REORGANIZE |
+----------------------+---------------+-------------------+----------------------+
| Heap in-row | Rebuild | - | - |
| Heap LOB | - | - | Reorganize |
| Clustered in-row | Rebuild | Rebuild | Reorganize |
| Clustered LOB | - | - | Reorganize |
| Non-clustered in-row | Rebuild | Rebuild | Reorganize |
| Non-clustered LOB | Rebuild | Rebuild | Reorganize |
+----------------------+---------------+-------------------+----------------------+
I am thinking about disabling all the non-clustered indexes on the
heap, rebuilding the heap and then re-enabling the non-clustered
indexes.
You do not need to separately re-enable non-clustered indexes; ALTER TABLE .. REBUILD rebuilds all indexes as well, and disabled indexes will be re-enabled as part of the rebuild.
Would this operation also re-claim/compact any unused space in the lob
column as well as removing the forwarded records and deleted but not
fully de-allocated rows?
Per our earlier results, no, not exactly. If you're satisfied with merely having the LOB data compacted with the rest of the table rebuilt, the procedure for that would be:
- Perform
ALTER INDEX ALL .. DISABLE to disable all non-clustered indexes;
- Perform
ALTER INDEX ALL .. REORGANIZE WITH (LOB_COMPACTION = ON) to compact LOB pages of the underlying heap (this will leave the disabled indexes alone);
- Perform
ALTER TABLE .. REBUILD to rebuild the in-row data of the heap, as well as all data of the indexes, and re-enable them.
If you really want to shrink the heap down to its minimum size, you'll have to create a new table and insert the data there, but that involves a lot more scripting and judicious use of sp_rename. It's also very expensive, since it requires copying all the LOB data (something which REORGANIZE avoids). If you do this without paying attention to filegroups and log space used, you can end up consuming more space than you seek to reclaim, and it's unlikely to help with performance.
