主页 / dba / 问题 / 12256
Accepted
GSerg
Asked: 2012-02-05 07:49:24 +0800 CST

添加 SPARSE 使表变得更大

  • 772

我有一个通用日志表，大约 500 万行。
有一个存储事件类型的“强类型”字段，以及一堆包含与事件相关的数据的“松散类型”列。也就是说，那些“松散键入”列的含义取决于事件的类型。

这些列定义为：

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。

是什么赋予了？

sql-server sql-server-2008

1 个回答

  1. Best Answer

    使列稀疏后，您需要重建聚簇索引。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
    • 15

相关问题