25 Data compression

 

When troubleshooting SQL Server performance issues, I/O-related issues are often at the top of the suspect list. If your workload is I/O intensive—meaning it reads and writes a lot of data—you’ll often discover bottlenecks that lead to poor performance. The easy fix is to improve the hardware by either adding more resources or getting faster disks. If this isn’t an option, then data compression could be just the tool you need in your toolbox.

Data compression has been around since SQL Server 2008, and, barring a few enhancements to add compression for additional datatypes, no major changes to how the technology works have been made. However, the most impactful change came with SQL Server 2016 SP1 when the feature was made available in all editions of SQL Server. Previously, it was an Enterprise-only feature. This change opened the door for a greater audience to take advantage of data compression. Data compression also isn’t going anywhere anytime soon. It’s supported in Azure for both Azure SQL Databases and Azure SQL Managed Instances.

25.1 Types of compression

Three types of compression are available within SQL Server: rowstore compression, columnstore compression, and backup compression.

25.2 How does rowstore data compression work?

25.3 Why use data compression?

25.4 It can’t all be rainbows and unicorns: Compression drawbacks

25.5 What’s compressed?

25.6 What should we compress?

25.7 What makes a good candidate for compression?

25.8 dbatools, what should I compress?

25.9 Compressing objects the old-fashioned way

25.10 dbatools to the rescue!

25.11 Specifying the compression level

25.12 Advanced settings

25.13 Hands-on lab