How much space can be saved using SQL Server 2008 Data Compression feature ?

Leave a comment (0) Go to comments

If you have the questions like

  1. How much space can be saved using SQL Server 2008 Data Compression feature ?
  2. How much space can be saved using Page Level compression ?
  3. How much space can be saved using Row Level compression ?
  4. How much space can be saved using Row Level compression as well Page level compression?
  5. Does Data Compression saves storage space ?
  6. Any POC for SQL Server Data Compression ?
  7. Should I used SQL Server Data Compression, to save space ?
  8. Will there be any performance degradation, if I used  SQL Server Data Compression ?

then, you are at right place, Today, I did some testing to test how, Data Compression (SQL Server 2008 feature), can help to save some storage space. 

Data Compression Test Conclusion : Data Compression can help you to save a lot of space in your system, it might compress the table size upto 99.5 %  but at the same time it might can increase the space utilization too. Unbelievable, but YES, this is true, this is what I leaned in my POC. How much space you are going to save that totally depends on your data.  To come up with conclusion, we used used two tables : 


  • First table, which is Optimised_Table, has 4039 bytes in a row, this row size can fit two rows in page.
  • Second table, which is UNOptimised, has 4040 bytes , this row size doesn’t fit 2 rows in a page, thus every page stores a single record.
Test Case 1 : When the Rows have very less Data, which is just a single char
S.No Cases name rows reserved data index_size unused
1 Without any Compression Optimised_Table 10000 40072 KB 40000 KB 8 KB 64 KB
UNOptimised 10000 80072 KB 80000 KB 8 KB 64 KB
2 With Page Level Compression Optimised_Table 10000 208 KB 128 KB 8 KB 72 KB
UNOptimised 10000 208 KB 136 KB 8 KB 64 KB
3 With Row Level Compression Optimised_Table 10000 272 KB 136 KB 8 KB 128 KB
UNOptimised 10000 208 KB 128 KB 8 KB 72 KB
4 With Row Level Compression and then Page Level Compression Optimised_Table 10000 208 KB 128 KB 8 KB 72 KB
UNOptimised 10000 208 KB 128 KB 8 KB 72 KB
               
Test Case 2 : When the Rows has 100% filled Data
S.No Cases name rows reserved data index_size unused
1 Without any Compression Optimised_Table 10000 40008 KB 40000 KB 8 KB 0 KB
UNOptimised 10000 80072 KB 80000 KB 8 KB 64 KB
2 With Page Level Compression Optimised_Table 10000 80080 KB 80016 KB 8 KB 56 KB
UNOptimised 10000 80080 KB 80016 KB 8 KB 56 KB
3 With Row Level Compression Optimised_Table 10000 80080 KB 80016 KB 8 KB 56 KB
UNOptimised 10000 80080 KB 80016 KB 8 KB 56 KB
4 With Row Level Compression and then Page Level Compression Optimised_Table 10000 80144 KB 80016 KB 8 KB 120 KB
UNOptimised 10000 80080 KB 80016 KB 8 KB 56 KB
               
Test Case 3 : When the Rows has 50% filled Data
S.No Cases name rows reserved data index_size unused
1 Without any Compression Optimised_Table 10000 40072 KB 40000 KB 8 KB 64 KB
UNOptimised 10000 80008 KB 80000 KB 8 KB 0 KB
2 With Page Level Compression UNOptimised 10000 80008 KB 80000 KB 8 KB 0 KB
UNOptimised 10000 336 KB 168 KB 8 KB 160 KB
3 With Row Level Compression Optimised_Table 10000 26768 KB 26688 KB 8 KB 72 KB
UNOptimised 10000 26768 KB 26688 KB 8 KB 72 KB
4 With Row Level Compression and then Page Level Compression Optimised_Table 10000 336 KB 176 KB 8 KB 152 KB
UNOptimised 10000 336 KB 168 KB 8 KB 160 KB

  
Script which I used to produce this testing result,

  • In test Case 1, I have used  replicate('*',1)
  • In test Case 2, I have used  replicate('*',1000),replicate('*',1039) for last table and replicate('*',1040) for last column, UnOptimised Table
  • In test Case 3, I have used  replicate('*',500),replicate('*',519) for last table and replicate('*',520) for last column, UnOptimised Table

use tempdb

go

create table Optimised_Table

(

col1 char(1000),

col2 char(1000),

col3 char(1000),

col4 char(1039)

)

create table UNOptimised

(

col1 char(1000),

col2 char(1000),

col3 char(1000),

col4 char(1040)

)

declare @i int

set @i = 0

begin tran

 while @i < 10000

 begin

      insert into Optimised_Table values (replicate('*',500),replicate('*',500),replicate('*',500),replicate('*',519))

      insert into UNOptimised values (replicate('*',500),replicate('*',500),replicate('*',500),replicate('*',520))

      select @i = @i + 1

 end

commit

go

sp_spaceused Optimised_Table

go

sp_spaceused UNOptimised

go

/*

Drop table Optimised_Table

Drop table UNOptimised

*/

go

EXEC sp_estimate_data_compression_savings 'dbo', 'Optimised_Table', NULL, NULL, 'ROW' ;

go

EXEC sp_estimate_data_compression_savings 'dbo', 'Optimised_Table', NULL, NULL, 'PAGE' ;

go

EXEC sp_estimate_data_compression_savings 'dbo', 'UNOptimised', NULL, NULL, 'ROW' ;

go

EXEC sp_estimate_data_compression_savings 'dbo', 'UNOptimised', NULL, NULL, 'PAGE' ;

GO

sp_spaceused Optimised_Table

go

sp_spaceused UNOptimised

ALTER TABLE Optimised_Table REBUILD WITH (DATA_COMPRESSION = ROW);

ALTER TABLE Optimised_Table REBUILD WITH (DATA_COMPRESSION = PAGE);

ALTER TABLE UNOptimised REBUILD WITH (DATA_COMPRESSION = ROW);

ALTER TABLE UNOptimised REBUILD WITH (DATA_COMPRESSION = PAGE);

go

Conclusion : Data Compression can help you to save a lot of space in your system, if might compress the table size up to 99.5 %  but at the same time it might can increase the space utilization too. Specifically in Test 2, we just doubled up the storage size, if we compare the size with uncompressed table, because compression, also add some bytes to store compression information.

So Prior to enable a Data Compression on your systems, please perform a rigorous testing, as it might can harm your I/O badly.

Tags : SQL Server 2008 Data Compression,Creating Compressed Tables and Indexes,Implementing Data Compression in SQL Server,Types of data compression in SQL Server,An Introduction to Data Compression in SQL Server 2008,SQL Server 2008 and Data Compression,Compression Features in SQL Server 2008,SQL Server 2008 Database Compression,Quick overview of Data Compression SQL server 2008,SQL Server Data Compression,Data Compression Commands for SQL Server 2008 and 2008 R2, how to enable data compression on sql server, enable data compression,Using Data Compression with SQL Server 2008 and 2008 R2,Using Data Compression with SQL Server,Microsoft SQL Server Data Compression,Data Compression Strategy Capacity Planning ,sql server Data Compression Strategy,Page compression in SQL Server,row compression in SQL Server,SQL Server 2008 Data Compression,Compressing SQL SERVER DAtabase,Compress SQL SERVER DAtabase,sql server Types of Data Compression,How SQL Server 2008 Data Compression works,SQL Server 2008 backup compression pros and con, SQL Server backup compression pros and con,Data Compression Using the SQL Server Vardecimal Storage Format,SQL SERVER – Data and Page Compressions,Data and Page Compressions,SQL Server White Paper Data Compression,SQL Server Compression , how to enable compression in sql server ,  sql server compression,  row compression,  page compression,  sql server compression,  implement sql server compression

EOF - How much space can be saved using SQL Server 2008 Data Compression feature ?, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.