Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
We can compress some tables to reduce the size of the table. I / O performance increases as data size decreases. It also increases CPU utilization rate.
When the data is compressed, application will use fewer pages to read the data. However, it must decompress the data to read, and compress to write. Therefore there will be more CPU usage. If your bottleneck is caused by I/O and your CPU is strong enough, compression may be a good option for you.
We can also use compression in columnstore structures in addition to the normal tables and indexes that we know.
As an important note, you do not need to change the application after compression.
Compression Types
There are two types of compression:
- Row Compression
- Page Compression
Row Compression
In Row Compression,
- Variable-length storage format is used for some numeric data types (integer, decimal, float)
- Blank characters in fixed-length character data types are not stored.
- Null and 0 values do not occupy space.
For detailed information about how much space is occupied by data types, you can visit the following address.
Page Compression
Consists of 3 stages:
- Row Compression
- Prefix Compression
- Dictionary Compression
We mentioned Row Compression above. Page Compression includes Row Compression, as can be seen in the sequence above. In addition, it performs Prefix and Dictionary Compression operations. Now let’s refer to these concepts.
Prefix Compression: Stores the repeated values in columns, in the compression information (CI) structure immediately after the page header in the page. In this way, the same values that are repeated are prevented from taking up extra space.
Dictionary Compression: Occurs immediately after Prefix Compression. Like Prefix Compression, it stores the repeating values in the compression information (CI) structure. Unlike Prefix Compression, it is not limited to 1 column. It performs this operation anywhere on Page.
When To Use Row or Page Compression
We mentioned about compression types of SQL Server.
Which one is the best for you?
Generally I use Page Compression if I need to compression. But if you want to calculate which option is the best for your system, you can look at the below link.
https://technet.microsoft.com/en-us/library/dd894051(v=sql.100).aspx
Example
Let’s examine page and row compression by doing an example.
We will perform compression on the Person.Person table in the AdventureWorks database. To get detailed information about the size of the table before performing the process, we run a script as follows.
1 | sp_spaceused 'Person.Person' |
As you can see in the picture below, there are 85840 KB reserved data. 30504 KB of this data was used for data, 53192 KB was used for index, 2144 KB was not used yet.
Right click on the table and click Manage Compression as below.
We are proceeding by clicking next on the information screen. On the next screen, we select Row Compression as Compression type and click on Calculate.
It shows the current size of the data in Current Space and the estimated size after compression in the requested compressed space. Click Next to proceed.
We get the script of the process we will perform by selecting the following options on the screen.
It creates a script as follows.
1 2 3 4 5 | USE [AdventureWorks2014Yeni] ALTER TABLE [Person].[Person] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ROW ) |
If your table is partitioned, I do not recommend “REBUILD PARTITION = ALL”. Because partitioned tables are usually large. Therefore, anyone can not access the table for a long time. This means that your users cannot access the application. If you perform partition-based compression in partitioned tables as follows, you can compress different partition in different times. You can perform this operation as follows for a table with 3 partitions.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | USE [AdventureWorks2014Yeni] ALTER TABLE [Person].[Person] REBUILD PARTITION = 1 WITH (DATA_COMPRESSION = ROW) USE [AdventureWorks2014Yeni] ALTER TABLE [Person].[Person] REBUILD PARTITION = 2 WITH (DATA_COMPRESSION = ROW) USE [AdventureWorks2014Yeni] ALTER TABLE [Person].[Person] REBUILD PARTITION = 3 WITH (DATA_COMPRESSION = ROW) |
If you want to do compression without interruption, you should change your script as follows.
1 2 3 4 | USE [AdventureWorks2014Yeni] ALTER TABLE [Person].[Person] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ROW,ONLINE=ON) |
Check the sizes of the table after Compression.
In the same script, write page instead of row to perform page compression and check the sizes again.
1 2 3 4 5 6 | USE [AdventureWorks2014Yeni] ALTER TABLE [Person].[Person] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE,ONLINE=ON) GO sp_spaceused 'Person.Person' |
As you can see below, our compression ratio is better this time. This ratio will change according to the structure of the data and the design of the table.
You can find the list of table sizes in the article “How To Find Table and Index Sizes in SQL Server“.
Calculate Estimated Compression Saving
If you want to calculate estimated compression saving via script, you can use the below script.
1 2 3 | USE your_db_name; GO EXEC sp_estimate_data_compression_savings 'schema_name', 'table_name', NULL, NULL, 'compression_type'; |
