Compress Big Tables

Storing data in SQL Server can be expensive. Not necessarily because disk is expensive; it isn’t and is generally getting cheaper all the time. Data costs money because it consumes RAM, requires CPU resources to process, time to back up, etc, etc. Obviously, reducing costs associated with SQL Server can reap major benefits for your business. When you compress big tables with SQL Server Data Compression, you can dramatically reduce costs, and simultaneously increase performance.

Two Hermits in a Rock Cave - Unknown German Artist, wholly unconcerned with page life expectancy.  What a life!

Two Hermits in a Rock Cave – Unknown German Artist, wholly unconcerned with page life expectancy. What a life!

Why Compress?

Data compression for tables in SQL Server has been around for a long time. Since SQL Server 2012, to be precise. The feature is enabled by defining a table using the WITH ( DATA_COMPRESSION = [PAGE | ROW | NONE] ) clause. Compressing large tables has several benefits, including:

  1. Reduced storage space on disk (well, duh!). Compressing data can reduce the on-disk storage requirements by 50 to 75%; for a large table that might equate to many gigabytes of regained space on your precious SAN or local SSD storage.
  2. Reduced memory consumption. Once table compression is enabled, the data remains compressed in memory until SQL Server needs to read that data. To read compressed data, SQL Server uncompresses it on-the-fly. This means you might be able to reduce the amount of memory used by SQL Server, and get higher utilization if your environment is virtualized. Higher utilization equals lower costs. Sounds like a Win to me.
  3. Increase in Page Life Expectancy. Page Life Expectancy (PLE) is how long the average data page will remain in memory. Higher PLE means higher performance, up to a point. If you have a server with 100 second PLE, and compressing data bumps PLE up to 1000, that’s a Major Win™.
  4. Reduced I/O congestion. Since compressed data occupies less space, reading a compressed table from disk into memory requires less I/O bandwidth than reading an uncompressed table into memory. For a table that is seeing 50% compression, it’ll be twice as fast to read it into memory. Big Win™.
  5. On SQL Server Express Edition, more space to store your data (SQL Server 2017+). If you’re using the Express edition of SQL Server, you’ll realize at some point there is a 10GB limitation on the size of SQL Server Databases. Compression on large tables in SQL Server Express can mean you don’t need to step up to a paid edition of SQL Server. Very Big Win™.
  6. Increased CPU utilization. Assuming you’re on a paid edition of SQL Server, you’re paying for CPU, you may as well use it. Compression does increase CPU utilization, so you’ll probably not want to compress tables on a system that is already CPU bound. Aside from that, you’re golden if you’ve got spare CPU capacity. May as well use it to your advantage!

The Script

The script below will compress big tables, and their indexes, if they are consuming more space than the specified cutoff. It uses the sys.sp_estimate_data_compression_savings system stored procedure to get the expected compression ratio of each table. If the expected compression ratio is above a certain target, the table or index is compressed. The estimation prevents wasting time compressing tables that are un-compressible, such as ones containing JPG images that are already compressed.

Note, compression affects SQL Server Replication and SQL Server Partitioning in various ways. If you are using either of those technologies, you should read the Microsoft docs linked above to ensure you don’t cause problems.

I’m out!

Check out the rest of our posts on SQL Server Performance, and please, let me know what you think of this post!

Ads by Google, Paying the Rent: