SQL Server 2016 SP1 page compression update

Discover the benefits of page compression to Apteco PeopleStage.

The release of SQL Server 2016 SP1 contained a nice surprise: Microsoft enabled a number of features that were previously only available in the Enterprise edition of SQL Server, for all editions.

A particularly beneficial feature of the Enterprise Edition was page compression and this is now available on all editions of SQL Server (including the free Express edition).

Our analysis of Page Compression has shown that using it can bring substantial benefits to Apteco PeopleStage and reduce the size of data and indexes by over 60%.

Reducing the size of the database has a number of benefits, it:

1. Reduces the amount of disk space required by the database
2. Improves performance by reducing the amount of I/O required by the database
3. Improves cache performance because SQL Server can hold more data in its memory
4. Effectively increases the 10Gb limit imposed by the SQL Server Express Edition

There will be an additional CPU load to handle the compression/decompression but this is usually compensated for by the reduced I/O.

For example implementing Page Compression on a 100m row StateHistory table reduced it down to 35% of its original size. This reduced the storage required from 58Gb to 20Gb.

If you have a large amount of data in your PeopleStage database some of these index rebuild operations may take a long time. Apteco recommend that the FastStats service is stopped while these operations are carried out offline.

Enabling page compression on an index will prevent you from moving to a SQL Server database that does not support compression, i.e. you cannot go back to SQL Server 2016 RTM (Express or Standard Editions).

Step 1: Prepare the tables for compression

There are 2 tables in the PeopleStage database that specify the use of a ‘Sparse’ column. Sparse columns are not supported in compressed indexes and so before compression can be applied to these tables the columns must be converted to non-sparse columns.  The sparse column in the PeopleStage database is the CommunicationKey that exists on two tables: ‘Pool’ and ‘StateHistory’.

Removing the sparse attribute on the ‘Pool’ table is straight-forward but may take some time to process:
(Download .txt file of code here)

Step 2 – Compress the indexes

This query will output a script containing statements that can be run to compress the indexes in the PeopleStage system. It includes all indexes that are over 20000 pages (150Mb) and aren’t already compressed:
(Download .txt file of code here)

Run these statement batches to compress the indexes over 150Mb in size. Compression should occur on the smallest index first to maximise available temporary disk space before the largest indexes are compressed.

Discover how you can offer your customers what they want, before they ask for it; download Best Next Offer now!