为何SQL Server聚集主键索引占用空间异常庞大？

占用大量空间的部分是什么？ #

大概率是LOB数据页（LOB_DATA）或行溢出数据页（ROW_OVERFLOW_DATA）。
因为聚集索引会包含表中所有列，若表内存在大字段（如VARCHAR(MAX)、NVARCHAR(MAX)、VARBINARY(MAX)，或旧版的TEXT/NTEXT/IMAGE类型），这类数据会被存储在独立的LOB页中。即使删除了包含大字段的行，SQL Server有时不会立即释放这些LOB页的空间；且常规的索引重建/重组操作对LOB数据空间的回收效果有限，因为LOB数据的存储机制与普通行数据不同。

你可以先运行以下查询确认具体的空间占用类型：

SELECT 
    i.name AS IndexName,
    s.page_type_desc,
    SUM(page_count * 8) AS SizeKB
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.SessionSignIn'), NULL, NULL, 'DETAILED') AS s
JOIN sys.indexes AS i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
WHERE i.name = 'PK_SessionSignIn'
GROUP BY i.name, s.page_type_desc;

如何清除该部分空间？ #

方法1：数据迁移重建（最彻底） #

如果表中存在大量已删除的大字段数据，可通过迁移数据的方式彻底回收空间：

1. 创建临时表并导出数据：

   SELECT * INTO #TempSessionSignIn FROM dbo.SessionSignIn;
   

2. 清空原表（注意：若表存在外键约束，需先禁用或删除外键）：

   TRUNCATE TABLE dbo.SessionSignIn;
   

3. 将数据导回原表：

   INSERT INTO dbo.SessionSignIn SELECT * FROM #TempSessionSignIn;
   

4. 重建聚集索引：

   ALTER INDEX PK_SessionSignIn ON dbo.SessionSignIn REBUILD;
   

方法2：收缩数据文件+重建索引 #

如果是未释放的空闲LOB空间，可通过收缩文件回收：

1. 查询数据文件的逻辑名称：

   SELECT name FROM sys.database_files WHERE type = 0;
   

2. 收缩数据文件（仅释放未使用的空闲空间，不会移动数据）：

   DBCC SHRINKFILE (N'你的数据文件逻辑名', TRUNCATEONLY);
   

3. 重建索引修复收缩产生的碎片：

   ALTER INDEX PK_SessionSignIn ON dbo.SessionSignIn REBUILD;
   

方法3：业务层面优化 #

如果大字段是业务必需的，考虑拆分大字段到独立的关联表，主表仅存储关联ID，减少聚集索引的整体体积。

内容的提问来源于stack exchange，提问作者AngryHacker