When it comes to SQL Server performance, disk I/O efficiency plays a crucial role. The faster the disk I/O, the better the performance. In SQL Server 2008, Microsoft introduced Data and Backup compression features to enhance disk I/O efficiency. In this article, we will focus on Data compression and how it can significantly improve performance.

Data compression in SQL Server refers to the reduction in disk space reserved by data. It can be configured for various database objects such as tables, clustered indexes, non-clustered indexes, indexed views, or partitions of tables or indexes. SQL Server offers two levels of data compression: ROW and PAGE. Page compression automatically includes row compression.

To enable data compression, you can use the CREATE TABLE and CREATE INDEX statements when creating tables and indexes. Additionally, you can change the compression state of a table, index, or partition using the ALTER TABLE.. REBUILD WITH or ALTER INDEX.. REBUILD WITH statements.

So, what happens during the compression of data? Let’s take a closer look at row compression:

• The metadata overhead of the record is reduced.
• Numeric and numeric-based data types (e.g., integer, decimal, datetime) are converted into variable length values. This reclaims any unused space. For example, an integer value between 0 and 255 can be stored in 1 byte, but by default, it reserves 4 bytes on disk. After compression, 3 bytes are reclaimed.
• CHAR and NCHAR type values are stored in variable length format. After compression, no blank characters are stored with this type of data. For example, a value “DBMS” stored in a CHAR(10) type column will reserve only 4 bytes after compression.
• NULL and 0 values across all data types are optimized and take no bytes.

Page compression, on the other hand, utilizes two additional methods: prefix compression and dictionary compression.

• Prefix compression identifies a common value from all rows in each column and stores it in a row below the header. Then, from all the rows, that common value is replaced with a reference to the header row.
• Dictionary compression is similar to prefix compression but identifies common values from all columns on a page and stores them in a row below the header. These common values are then replaced with references to the values in the new row.

Implementing data compression can have a significant impact on the size of your tables. Let’s consider an example:

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

By running the above code, you can observe the size of the table before and after applying different levels of compression. This can help you evaluate the impact of compression on your database’s performance.

If you are already using data compression on your production server, we would love to hear your feedback. Share your experiences and insights with us in the comments section below.

Implementing data compression in SQL Server can be a game-changer for improving performance. By reducing disk space and optimizing data storage, you can enhance disk I/O efficiency and ultimately boost the overall performance of your SQL Server environment.