Compressing all tables in a database in SQL Server

Views: 4.915
Reading Time: 7 minutes

In this post I will talk a little about the SQL Server data compression method and how we can use this feature to compress all objects in a database. Data compression can be configured for the following database objects:

  • For a table that is stored as a heap.
  • For a table that is stored as a clustered index.
  • For a nonclustered index.
  • For an indexed view.
  • For partitioned tables and indexes, the compression option can be set for each partition, and multiple object partitions can contain different compression settings.

Remember that compression is not available for system objects and the compression setting is not automatically applied to nonclustered indexes. Therefore, each nonclustered index must be set individually and manually.

ROW COMPRESSION LEVEL

This compression feature takes into account the type of variable data structures that define a column. Row Compression Level is a compression level that uses no compression algorithm.

Row Compression Level's main goal is to reduce fixed-type data storage, that is, when you are allowing Row Level Compression you are just changing the physical storage format of the data that is associated with a data type.

Row Level Compression extends the vardecimal storage format by storing data of all types of fixed length into a variable length storage format. This type of compression will remove any extra bytes in the fixed data type. There are absolutely no changes required in the app.

For example, we have a CHAR (100) column that is using Row Compression Level will only use the amount of storage defined by the data. As well? Let's store the phrase "SQL Server 2008" in the column, the sentence contains only 15 characters and only these 15 characters are stored unlike the 100 that were defined by the column, so you have 85% saving on storage space.

PAGE COMPRESSION LEVEL

In previous versions of SQL Server each value was stored on the page, regardless of whether the same value had already appeared in the same column for some other rows within a page. In SQL Server 2008, the redundant or duplicate value will be stored only once within the page and will be referenced in all other instances, so we have the Page Compression Level.

Basically, Page Compression Level is a superset of ROW compression and takes into account redundant data on one or more lines on a given page. It also uses prefix and dictionary compression.

The Page Compression Level method is more vital because it allows common data to be shared between the lines of a given page.
This type of compression uses the following techniques:

  • ROW COMPRESSION: Already seen above.
  • PREFIX COMPRESSION: For each column on a page duplicate prefixes are identified. These prefixes are stored in the Information Compression (CI) headers, which reside after the page header. A reference number is assigned to these prefixes and this reference number is used wherever these prefixes are being used, reducing the number of bytes used.
  • DICTIONARY COMPRESSION: Searches for duplicate values ​​outside the page and stores them in CI. The main difference between Prefix Compression and Dictionary Compression is that Prefix is ​​restricted to only one column while Dictionary is applicable to the full page.
    After Prefix Compression is completed, Dictionary Compression is applied and searches for repeated values ​​anywhere on the page and stores them in the CI area. Unlike Prefix Compression, Dictionary Compression is not restricted to one column and can override repeated values, which occur anywhere on a page.

Estimating Space Gain

In SQL Server 2008 there are two ways to estimate the space savings for storing tables and indexes. The first method is to use a system SP called sp_estimate_data_compression_savings and the second method is to use the Data Compression Wizard.
First let's use the sp_estimate_data_compression_savings where:
1th parameter is the name of the schema;
2th parameter is the name of the object;
3th parameter is the index ID;
4th parameter is Partition ID
5th parameter is the compression type;

SQL Server - Page Compression

Note the size_with_current_compression_setting (KB) and size_with_requested_compression_setting (KB) columns, these columns show the current value and the value after compression. This way we can know how much space we will gain by applying compression.

Now let's use the wizard.
Right click on the table choose Storege -> Manage Compression
SQL Server - Page Compression

The compression wizard screen will open.
SQL Server - Page Compression 3

In the next watch screen you can test the type of compression and before applying you can calculate the space to see which one got the highest data compression rate.
SQL Server - Page Compression 4

Following with the compression wizard, I chose the PAGE option, which usually gets the best result, and then asks what to do: Just generate the script, run the compression, or schedule the change to enable compression.
SQL Server - Page Compression 5

Finally, a message of data compression success will be displayed on the screen.
SQL Server - Page Compression 6

You can check all your objects and what levels of compression they are by right-clicking on the object, on the Storage tab, the Compression option tells you the type of compression or using the command:

SQL Server - Page Compression 7

Applying Compression to an Entire Database

In this post we already saw how beneficial data compression is, especially for storage. Well, let's put this into practice. I created a procedure that allows you to compress all objects of a database, easily and quickly. The procedure code is available here:

Example of use:

Note: I do not recommend using the @Fl_Rodar_Shrink option in production environments.

sql server, compress table, compact database, compress, compression, compact, shrink