添加 SPARSE 使表更大

我有一個通用日誌表，大約 5m 行。

有一個“強類型”欄位儲存事件類型，還有一堆“弱類型”列，其中包含與事件相關的數據。也就是說，那些“類型鬆散”的列的含義取決於事件的類型。

這些列定義為：

USER_CHAR1 nvarchar(150) null,
USER_CHAR2 nvarchar(150) null,
USER_CHAR3 nvarchar(150) null,
USER_CHAR4 nvarchar(150) null,
USER_CHAR5 nvarchar(150) null,

USER_INTEGER1 int null,
USER_INTEGER2 int null,
USER_INTEGER3 int null,
USER_INTEGER4 int null,
USER_INTEGER5 int null,

USER_FLAG1 bit null,
USER_FLAG2 bit null,
USER_FLAG3 bit null,
USER_FLAG4 bit null,
USER_FLAG5 bit null,

USER_FLOAT1 float null,
USER_FLOAT2 float null,
USER_FLOAT3 float null,
USER_FLOAT4 float null,
USER_FLOAT5 float null

每種類型的第 1 列和第 2 列都被大量使用，但從第 3 列開始，很少有事件類型會提供這麼多資訊。因此，我決定將每種類型中的第 3-5 列標記為SPARSE.

我先做了一些分析，發現確實，每一列中至少 80% 的數據是null，而大約 100% 的數據是null。根據40% 的儲蓄門檻表，SPARSE這對他們來說將是一個巨大的勝利。

所以我去申請SPARSE了每組的3-5列。現在，我的表佔用了 1.8Gb 的數據空間sp_spaceused，而在稀疏之前是 1Gb。

我試過dbcc cleantable了，但沒有效果。

然後dbcc shrinkdatabase，也沒有效果。

困惑，我刪除SPARSE並重複了dbccs。表的大小保持在 1.8Gb。

是什麼賦予了？

您需要在使列稀疏後重建聚集索引。刪除的列仍然存在於數據頁面中，直到您執行此操作，如查詢sys.system_internals_partition_columns或使用DBCC PAGE

SET NOCOUNT ON;
CREATE TABLE Thing 
(
ThingId int IDENTITY CONSTRAINT PK PRIMARY KEY,
USER_CHAR1 nvarchar(150) null,
USER_CHAR2 nvarchar(150) null,
USER_CHAR3 nvarchar(150) null,
USER_CHAR4 nvarchar(150) null,
USER_CHAR5 nvarchar(150) null
)
INSERT INTO Thing
SELECT REPLICATE('A',150),
      CASE WHEN number % 5 = 1 THEN REPLICATE('A',150) END,
      CASE WHEN number % 5 = 2 THEN REPLICATE('A',150) END,
      CASE WHEN number % 5 = 3 THEN REPLICATE('A',150) END,              
      CASE WHEN number % 5 = 4 THEN REPLICATE('A',150) END
FROM master..spt_values   

EXEC sp_spaceused 'Thing'

ALTER TABLE dbo.Thing ALTER COLUMN USER_CHAR2 ADD SPARSE
ALTER TABLE dbo.Thing ALTER COLUMN USER_CHAR3 ADD SPARSE
ALTER TABLE dbo.Thing ALTER COLUMN USER_CHAR4 ADD SPARSE
ALTER TABLE dbo.Thing ALTER COLUMN USER_CHAR5 ADD SPARSE

DECLARE @DynSQL NVARCHAR(MAX);

SELECT @DynSQL =  'DBCC TRACEON (3604);
                  DBCC PAGE(0, ' + LEFT(file_id,10) + ', ' + LEFT(page_id,10) + ', 3); 
                  DBCC TRACEOFF(3604); ' 
FROM Thing
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%)
WHERE ThingId=76

EXEC(@DynSQL)    

SELECT pc.*
FROM sys.system_internals_partition_columns pc
JOIN sys.partitions p on p.partition_id=pc.partition_id
WHERE p.object_id = object_id('Thing')
AND pc.is_dropped=1

EXEC sp_spaceused 'Thing'

ALTER INDEX PK ON Thing REBUILD;    

SELECT @DynSQL =  'DBCC TRACEON (3604);
                  DBCC PAGE(0, ' + LEFT(file_id,10) + ', ' + LEFT(page_id,10) + ', 3); 
                  DBCC TRACEOFF(3604); ' 
FROM Thing
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%)
WHERE ThingId=76

EXEC(@DynSQL)    

SELECT pc.*
FROM sys.system_internals_partition_columns pc
JOIN sys.partitions p on p.partition_id=pc.partition_id
WHERE p.object_id = object_id('Thing')
AND pc.is_dropped=1

EXEC sp_spaceused 'Thing'

DROP TABLE Thing 

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