Reducing the size of the database has a number of benefits, it:
Improves performance by reducing the amount of I/O required by the database
Improves cache performance because SQL Server can hold more data in its memory
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 cyprus mobile phone numbers 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’.