Database is an important part of any application or business. If the performance of the database server decreases, then it generates an adverse effect on all systems. So tuning of a database system is very crucial. System tuning is not a single-step task, it is a series of tasks (steps) that help you to increase the performance of your database. Now, I will try to post a series of articles related to this topic and try to explain, “How do we tune up the performance of SQL Server?”.

Today, we will learn about data compression in SQL Server.

What is Data Compression?

By taking advantage of SQL Server's data compression feature, we can increase SQL Server's performance without changing any application code. Generally, the performance of SQL Server is decided by the disk I/O efficiency so we can increase the performance of SQL Server by improving the I/O performance.

Generally, data compression reduces the space occupied by the data. Data compression can help improve the performance of I/O intensive workloads because the data is stored in fewer pages and queries need to read fewer pages from disk. Data compression can be performed for a table, clustered index, or non-clustered index.

Facts about Data Compression

Types of Data Compression

SQL Server supports two types of data compression: row compression and page compression.

Row Compression

Row compression is an inner level of compression that stores the fixed character strings by using variable-length format by not storing the blank characters. The following steps are performed in row-level compression.

Page Compression

Page compression is an advanced level of data compression. A page compression also implements the row level compression automatically. Page compression is categorized into two types prefix compression and dictionary compression.

Prefix Compression

In prefix compression for each page, for each column in the page, a common value is retrieved from all rows and stored below the header in each column. Now in each row instead of a common value a reference of that value is stored.

Dictionary Compression

Dictionary compression is similar to prefix compression but common values are retrieved from all columns and stored in the second row after the header. Dictionary compression looks for exact value matches across all the columns and rows on each page.

We can perform row and page-level compression for the following database objects.

Note. We can perform data compression either at the time of creation like CREATE TABLE, CREATE INDEX, or after the creation using ALTER command with REBUILD option like ALTER TABLE REBUILD WITH.

Let us take some examples and check how data compression reduces the size of data.

Firstly, create a table.

CREATE TABLE Compress_Table
(
    Id int,
    Name [char](50)
);

Now insert some data into the table.

INSERT INTO dbo.Compress_Table
(
    Id,
    Name
)
VALUES
(
    125,
    'Demo Data'
)
GO 10000

Insert

Now we check the space used by the data of the table.

Space

Now first we perform the ROW level compression and check how much data is compressed.

ROW level

We can see that the reserved space of the table is reduced from 712KB to 344KB and unused space is reduced from 48KB to 40KB.

Now we perform the page-level compression and check the difference between row-level and page-level compression.

Page-level compression

The above image shows that in page-level data compression, the size of the data is less than compared to row-level data compression.

Limitations of Data Compression

Conclusion

Data Compression is a very efficient technique to reduce the size of data, reduced data requires fewer I/O processes but when data is inserted in the compressed table then it requires more effort compared to uncompressed tables So before bulk insertion is uncompressed the data performs an insertion operation after that. After insertion, the operation again compresses the data.

Thanks for reading the article, if you have any queries or doubts, then don’t hesitate and ask your query in the comment section.

Read More