VARCHAR(MAX) 中的空字元串佔用無法恢復的空間

我有一個包含不可為空VARCHAR(MAX)列的表，其中每一行都包含一個空字元串（第三方數據庫，所以我無法更改它）。我看到大量的空間使用，超過 15GB 用於 100 萬行。

如果我執行以下查詢，我會看到 LOB_DATA 的大量 used_pa​​ges，即使該列中沒有數據。不包括VARCHAR(MAX)行大小低於 8000 字節。

SELECT o.name AS table_name,p.index_id, au.type, au.type_desc, au.total_pages, au.used_pages, au.data_pages
FROM sys.allocation_units AS au
  JOIN sys.partitions AS p ON au.container_id = p.partition_id
  JOIN sys.objects AS o ON p.object_id = o.object_id
WHERE o.name = 'table
ORDER BY o.name, p.index_id;

type    type_desc             total_pages   used_pages  data_pages
1   IN_ROW_DATA 23258             23252           23188
2   LOB_DATA              1880733             1880455             0
3   ROW_OVERFLOW_DATA   0             0           0

來自評論的更新：

• 該表是存檔表，因此數據永遠不會更改（僅插入）
• 我在單獨的數據庫（同一伺服器）中有一個相同的表，其中包含類似的數據，但沒有出現此問題
• 重建索引沒有幫助
• large_value_types_out_of_row 為 0
• DBCC CLEANTABLE 沒有幫助

是否有可能該列已填充並且已被清除，或者數據類型最近更改為 varchar(max)？您是否嘗試過重建聚集索引（與某些 ALTER 不同，它將觸及每一行）？

編輯

由於重建聚集索引沒有幫助，我很茫然，而且由於現有數據的大小如此之小，我建議只創建一個新版本的表並將數據移過來。您可以通過顯式重新創建表然後複製數據來執行此操作，或者您可以使用SELECT INTO以最小化這些步驟。

SELECT ... other cols ...,
   varchar_max_col = CONVERT(VARCHAR(MAX), NULL) 
   INTO dbo.newtable FROM dbo.oldtable;
CREATE CLUSTERED INDEX ... ON dbo.newtable( ...cols... );
CREATE INDEX ... ON dbo.newtable( ...cols... );
...
GO
BEGIN TRANSACTION;
EXEC sp_rename 'dbo.oldtable', 'oldtable_backup', OBJECT;
EXEC sp_rename 'dbo.newtable', 'oldtable', OBJECT;
COMMIT TRANSACTION;

如果根據您的最新更新，所有值都是空字元串並且您的架構中沒有其他 LOB 類型，則不應分配任何 LOB 頁。

一個空值 (MAX) 列將佔用與普通 varchar(x) 列相同的空間 - 2 個字節 + 數據本身 - 在這種情況下總共兩個。LOB 列可以使用的最小可能結構，如果它需要指向一個 lob 頁，則在引用的 LOB 頁上佔用 24 個字節 + 額外字節。因此，SQL Server 將僅在無法容納原始頁面上的數據時才分配 LOB 頁面，因為否則它不具有成本效益。（有關更多資訊，請參閱我的部落格上的“BLOB Inline Data”與“BLOB Inline Root” ）

話雖如此，在這 5 個 LOB 頁面之一上執行 DBCC PAGE 會很有趣，只是為了看看那裡實際儲存了什麼。

引用自：https://dba.stackexchange.com/questions/4282