Sql-server – Table compression on 500GB table using Enterprise edition with online=on

compressionsql-server-2016table

I am using MS SQL2016 enterprise edition and we have several tables that need to be compressed to save memory and backup space. Right now they are each around 500GB and I am trying to get some space to do test runs, but unfortunately we are not able to try this out. My question is first – I was thinking of using the Online=On option to allow the table to still be used while the compressing is happening, but how will that effect the backup? We have diff occurring 2x daily and a backup, with the table so large I imagine it will take days not hours to use the online approach. What have other done in this case, bit the bullet and take the table offline for 1.5 days over a weekend?

Best Answer

Agreed with everything sepupic said, especially reinforcing the fact you won't be saving space on backups if you're using standard compressed backups anyways.

I also wanted to specifically add you aren't guaranteed to save Memory either. Compression is a way to save space on Disk. When the data is loaded into Memory from a compressed table, it may first use CPU to decompress the data before loading it into Memory. Visa-versa when writing to a compressed Table as well (the CPU runs the compression algorithm on the decompressed data in Memory and hardens it to the compressed data on Disk). (This isn't always the case though, and in some cases it does stay compressed in Memory as well.)

Basically data compression is great for reducing how much Disk space is being consumed by a particular Table, and it helps reduce I/O bottlenecks from the Disk itself (which is usually the slowest piece of the hardware pie). That's about it for its main uses.

You can read more about this in this great article Compression And Its Effects On Performance.

Related Question