Admin: Data Compression Storage Savings

Data compression was introduced in SQL2008 (though sadly just in Enterprise/Developer Editions) and recently I was asked what sort of benefits you can get from it. So I thought i'd jot down a quick example to illustrate the reductions in space the different types of compression give you and also, an unexpected behaviour you get from using the REBUILD command.

Here i'm only speaking about storage savings. As with most things, there is a cloud to this silver lining although just how gloomy the cloud is will be the subject of a different post. Essentially though, compression gains disk space and IO read performance at the cost of CPU. Anyway, on to the example:

-- create the object
CREATE TABLE [dbo].[tblOutput](
   [Output_ID] [int] NULL,
   [T1] [int] NULL,
   [T2] [int] NULL,
   [Value] [varchar](255) NULL,
   [Run_ID] [int] NULL,
   [Date_Created] [datetime] NULL
) ON [PRIMARY]

GO
CREATE NONCLUSTERED INDEX [IDX_tblOutput] ON [dbo].[tblOutput]
(
   [Output_ID] ASC
) ON [PRIMARY]
GO

-- I populate this table with a 3rd party application which throws in plenty of rows and "random" values.

-- lets take a look at how much space this takes
EXEC sp_spaceused 'tblOutput'
GO

/*
name      rows        reserved    data        index_size  unused
--------- ----------- ----------  ----------  ----------  -------
tblOutput 19240000    1666896 KB  1025216 KB  641440 KB   240 KB
*/

-- and look into the physical stats DMV
SELECT page_count, record_count, compressed_page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('tblOutput'), NULL, NULL, 'DETAILED')
WHERE index_id = 0

/*

NB: WOW CHECK OUT THAT FRAGMENTATION ON THE NONCLUSTERED INDEX!!

index_type_desc     avg_page_space_   avg_fragmentation                            compressed_
                    used_in_percent   _in_percent        page_count  record_count  page_count
------------------- ----------------- -----------------  ----------- ------------  ------------
HEAP                99.8007659995058  6.26560159760359   128152      19240000      0
NONCLUSTERED INDEX  53.5752656288609  99.4513134653689   79827       19240000      0
NONCLUSTERED INDEX  68.7832592043489  100                344         79827         0
NONCLUSTERED INDEX  50.9574993822585  100                2           344           0
NONCLUSTERED INDEX  0.531257721769212 0                  1           2             0

*/

-- first lets try ROW compression
ALTER TABLE tblOutput
REBUILD
   WITH (DATA_COMPRESSION = ROW)
GO

/*
name      rows        reserved    data        index_size  unused
--------- ----------- ----------  ----------  ----------  -------
tblOutput 19240000    1153064 KB  808888 KB   343896 KB   280 KB
*/

/*

NB: THE NONCLUSTERED INDEX HAS BEEN DEFRAGMENTED!! WASN'T EXPECTING THAT!

index_type_desc     avg_page_space_   avg_fragmentation                           compressed_
                    used_in_percent   _in_percent        page_count record_count  page_count
------------------------------------  -----------------  ---------- ------------  -----------
HEAP                99.7074623177662  0.126572264852464  101112     19240000      0
NONCLUSTERED INDEX  99.8241907585866  0.01               42852      19240000      0
NONCLUSTERED INDEX  99.2432666172473  2.34375            128        42852         0
NONCLUSTERED INDEX  37.929330368174   0                  1          128           0
*/

-- now lets enable some PAGE compression (remember, this includes ROW compression)
ALTER TABLE tblOutput
REBUILD
   WITH (DATA_COMPRESSION = PAGE)
GO

/*
name      rows        reserved    data        index_size  unused
--------- ----------- ----------  ----------  ----------  -------
tblOutput 19240000    904104 KB   559920 KB   343896 KB   288 KB
*/

/*
index_type_desc     avg_page_space_   avg_fragmentation                           compressed_
                    used_in_percent   _in_percent        page_count record_count  page_count
------------------  ----------------  -----------------  ---------- ------------  -----------
HEAP                99.8160612799605  0.136798905608755  70149      19240000      70145
NONCLUSTERED INDEX  99.8241907585866  0.01               42852      19240000      0
NONCLUSTERED INDEX  99.2432666172473  2.34375            128        42852         0
NONCLUSTERED INDEX  37.929330368174   0                  1          128           0
*/

-- and just revert the compression on the table
ALTER TABLE tblOutput
REBUILD
   WITH (DATA_COMPRESSION = NONE)
GO

/*
name      rows        reserved    data        index_size  unused
--------- ------------ ----------  ----------  ----------  -------
tblOutput 19240000    1369384 KB  1025232 KB  343896 KB   256 KB
*/

/*
index_type_desc     avg_page_space_   avg_fragmentation                           compressed_
                    used_in_percent   _in_percent        page_count record_count  page_count
------------------  ----------------  -----------------  ---------- ------------  -----------
HEAP                99.7992092908327  0.118586942953439  128154     19240000      0
NONCLUSTERED INDEX  99.8241907585866  0.01               42852      19240000      0
NONCLUSTERED INDEX  99.2432666172473  3.125              128        42852         0
NONCLUSTERED INDEX  37.929330368174   0                  1          128           0
*/


You can view the documentation on the ALTER TABLE REBUILD command here but doesn't mention that nonclustered indexes will be rebuilt as a result of a REBUILD command.