Upgrade Performance with SQL Server Data Compression

When to Use Data Compression Savings in SQL Server

Here, we will cover the situations where using data compression can be helpful for your SQL Server database.

• When the database size of users is large and growing quickly.
• Subsequently, to reduce storage space usage.
• When the workload involves more read operations than updates.
• Moreover, when tables have repeated or similar data values.
• Also, when you need smaller and faster database backups.

Users should always test compression in a non-production environment before applying it to a live database.

Types of SQL Server Data Compression

For a better understanding, we will discuss how each compression type functions inside SQL Server. Just go through the given types of SQL Server Compression: 

1. Row Compression

Row compression helps users to store fixed-length data types in a more flexible format so that only the necessary storage space is used. It removes the extra unused bytes but keeps the original data unchanged. This helps the users to reduce the table size without affecting how the applications access the data.

Pro Tip: Row compression is a good choice for tables with many fixed-length columns and moderate update activity

2. Page Compression

Page compression is first applied to row compression and then searches for repeated values within the same data page. It helps to store the duplicate values only once and then replace them with a smaller reference. This usually saves users more space than row compression but may use slightly more CPU resources.

Pro Tip: Page compression works best for large tables that have repeated or similar data. Now, you have better understanding about the types of Compression in SQL Server on the basis of page. 

3. Columnstore Compression

Columnstore compression helps users to store their data column-wise instead of row-wise. As similar values are grouped together in each column, SQL Server can compress them more efficiently. It also helps the users to improve the query performance, especially for analytical workloads.

Pro Tip: Columnstore compression is used in large reporting databases where read operations are more common than frequent updates. 

How to Enable Row or Page Compression in SQL Server

Now we will walk through the process to apply row or page compression to a table and check the storage savings before enabling it. Data compression in SQL Server is configured at the index level. If you want to compress an entire database, then apply compression individually to each index. Also, you can also use the SSMS Data Compression Wizard to configure compression without writing T-SQL queries.

Step 01: Create a Sample Table 

Firstly, you have to create a test table and insert sample data into it.

CREATE TABLE dbo.TestCompression ( m_id INT NULL, text NVARCHAR(3000) NULL ); INSERT INTO dbo.TestCompression SELECT message_id, text FROM sys.messages;

Step 02: Estimate Compression Savings

Before applying compression, you can use the built-in procedure in order to calculate the estimated reduction in size:

EXEC sp_estimate_data_compression_savings  ‘dbo’, ‘TestCompression’, NULL, NULL, ‘ROW’;

Step 03: Measure Current Performance

Now, you can check how many logical reads are required by enabling IO statistics and running a query:

SET STATISTICS IO ON; SELECT m_id, text FROM dbo.TestCompression; SET STATISTICS IO OFF;

Step 04: Apply Row Compression

After that, you have to rebuild the table with row compression enabled:

ALTER TABLE dbo.TestCompression REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ROW);

Step 05: Finally, Compare the Results

Now you have to run the same SELECT query again with IO statistics enabled. You will usually observe fewer logical reads and reduced storage usage, which indicates improved efficiency.

To apply page compression instead, you simply have to replace ROW with PAGE in the compression option.

Also Read: Recover SQL Server Database from Corrupt Backup File.

End Note

In this blog, we have discussed the concept of SQL Server Data Compression and its different types. Choose the appropriate compression method in order to get better storage management. However, proper testing and monitoring are important before you apply it in live performance. Also, if you ever face database corruption issues while you manage the compressed data, DataRecovery Help SQL Database Recovery Software can be helpful.

Frequently Asked Question

Realted Post