SQL SERVER – Data and Page Compressions – Data Storage and IO Improvement

The performance of SQL Server is primarily decided by the disk I/O efficiency. Improving I/O definitely improves the performance. SQL Server 2008 introduced Data and Backup compression features to improve the disk I/O. Here, I will explain Data compression.

Data compression implies the reduction in the disk space reserved by data. Therefore, data compression can be configured for a table, clustered index, non-clustered index, indexed view or a partition of table or index.

Data compression is implemented at two levels: ROW and PAGE. Even page compression automatically implements row compression. Tables and indexes can be compressed when they are created by using the CREATE TABLE and CREATE INDEX statements. To change the compression state of a table, index, or partition, use the ALTER TABLE.. REBUILD WITH or ALTER INDEX.. REBUILD WITH statements. When compression state of a heap is changed, all non-clustered index are rebuilt (not with the compression type of table but there own).

Let me tell you what happens during the compression of data. In Row compression, the following four methods are used to remove unused space:

  1. The metadata overhead of the record is reduced.
  2. All numeric (for example integer, decimal, and float) and numeric-based (for example datetime and money) data type values are converted into variable length values. By default, the values of these data types are stored as fixed length like integer in 4 bytes datetime in 8 bytes, but after compression all unconsumed space is reclaimed. For example, a value 100 is stored in an integer-type column. We know an integer value between 0 and 255 can be stored in 1 byte. However, it reserves 4 bytes (integer type takes 4 bytes) on disk. Here, after compression, 3 bytes are reclaimed.
  3. CHAR and NCHAR type values are stored in variable length format. After compression, no blank character is stored with this type of data. For example, a value “DBMS” is stored in CHAR(10) type column. By default, this value will reserve 10 bytes on disk, but after compression, it will reserve only 4 bytes.
  4. NULL and 0 values across all data types are optimized and take no bytes.

Page compression is implemented through the following three methods:

  1. Row compression: as discussed above
  2. Prefix compression: In each page, for each column, a common value from all rows is identified and stored in a row below the header. After that, from all the rows, that common value is replaced with the reference of header row.
  3. Dictionary compression: Dictionary compression is same concept as that of Prefix compression implemented the second time. In Prefix compression, a separate common value is identified for each column, but in Dictionary compression, common values are identified from all columns in that page and stored in second row below the header. Then, these common values are replaced with the reference of values in new row.

Below is an example. Even it is not a good practical scenario, but sufficiently explains the compression functionality.

Review your database for performance and implement this strategies. Let us run the following different row level and page level compression and observe the size of the table once compression is in place.

USE tempdb
GO
CREATE TABLE TestCompression (col1 INT, col2 CHAR(50))
GO
INSERT INTO TestCompression VALUES (10, 'compression testing')
GO 5000
-- Original
EXEC sp_spaceused TestCompression
GO
-- DATA_COMPRESSION = ROW
ALTER TABLE TestCompression
REBUILD
WITH (DATA_COMPRESSION = ROW);
GO
EXEC sp_spaceused TestCompression
GO
-- DATA_COMPRESSION = PAGE
ALTER TABLE TestCompression
REBUILD
WITH (DATA_COMPRESSION = PAGE);
GO
EXEC sp_spaceused TestCompression
GO
-- DATA_COMPRESSION = NONE
ALTER TABLE TestCompression
REBUILD
WITH (DATA_COMPRESSION = NONE);
GO
EXEC sp_spaceused TestCompression
GO

SQL SERVER - Data and Page Compressions - Data Storage and IO Improvement datacompression

Do you use this compression on your production server? If yes, it will be interesting if you share your feedback with us over here.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
SQLAuthority News – Hyderabad Techies February Fever Feb 11, 2010 – Indexing for Performance
Next Post
SQLAuthority News – Excellent Event – TechEd Sri Lanka – Feb 8, 2010

Related Posts

30 Comments. Leave new

  • Hi Experts,
    If I perform compression using alter table Or specify compression during create table statement, what is the difference.
    will using compression option in create table means an automatic compression in the table ?

    Reply
  • We have a page compressed table and i am looking to delete a subset of data from the table, Does delete work with compressed tables as normal tables?

    Reply
  • Hi Pinal,

    If table and indexes are compressed, then what will be effect of data compression on “where conditions”, Insert and select statement. Will it create slowness as data has to be uncompressed before comparison and providing the result to other application?

    Thanks

    Reply
  • I have a transactional replication setup and i want to enable data compression(page or row) only on the subscriber side. I tried setting this up and it works fine until you reinitialize the subscriber. Every time i reinitialize the subscription it wipes out the compression setting and set it to default(none). Just to be clear….publisher and subscriber are both SQL 2012 enterprise.

    If anyone experienced this before or know any solution please share with me. Thanks in Advance.

    Reply
  • Is it possible to use compression, when using sintaxe like SELECT * INTO TABLE2 FROM TABLE

    Reply
  • Hi

    I have implemented data compression in existing table. After the data compression NOn clustered index are not getting used by the table. Please suggest.

    Reply

Leave a Reply

Menu