Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a generic log table, about 5m rows.
There's a "strongly typed" field that stores event type, and a bunch of "losely typed" columns that contain data relevant to the event. That is, meaning of those "losely typed" columns depends on the type of the event.

These columns are defined as:

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

Columns 1 and 2 in each type are heavily used, but starting from number 3, very few event types would provide this much of info. I therefore desided to mark columns 3-5 in each type as SPARSE.

I did some analysis first, and saw that, indeed, at least 80% of data in each of those columns is null, and in some 100% of data is null. According to the 40% savings threshold table, SPARSE would be a huge win on them.

So I went and applied SPARSE to columns 3-5 in each group. Now my table takes about 1.8Gb in data space as reported by sp_spaceused, whereas before sparsing it was 1Gb.

I tried dbcc cleantable, but it had no effect.
Then dbcc shrinkdatabase, no effect either.

Puzzled, I removed SPARSE and repeated the dbccs. The size of the table remained at 1.8Gb.

What gives?

share|improve this question
    
Will try and reproduce. Just incase it makes any difference is the table a heap or does it have a clustered index? –  Martin Smith Feb 4 '12 at 16:10
    
@MartinSmith Does have a clustered index rowid int not null identity(1,1) primary key clustered. –  GSerg Feb 4 '12 at 16:38

1 Answer 1

up vote 10 down vote accepted

You need to rebuild the clustered index after making the columns sparse. The dropped columns still exist in the data page until you do this as can be seen with a query against sys.system_internals_partition_columns or using 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 
share|improve this answer
1  
Awesome. Should we take it as a bug in the documentation? "The SQL Server Database Engine uses the following procedure to accomplish this change: 1) Adds a new column to the table in the new storage size and format. 2) For each row in the table, updates and copies the value stored in the old column to the new column. 3) Removes the old column from the table schema. 4) Rebuilds the table to reclaim space used by the old column." –  GSerg Feb 4 '12 at 17:07
3  
@GSerg - Ah right. Agree seems point 4 isn't correct then. Given that you are doing this for 12 columns then you wouldn't want the rebuild to happen implicitly for each column though so seems the behaviour is correct but not the documentation. –  Martin Smith Feb 4 '12 at 17:28
1  
@SQLKiwi - Thanks. Done –  Martin Smith Feb 5 '12 at 14:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.