As with all things in life, you seek solutions based on a need – SQL Server is no different. That said, compression is no different than anything else in life – a solution based on a need. Welcome to the introductory post of a blog series I am writing on SQL Server data compression. I hope you enjoy the ride!
So what does table compression do? It decreases your storage requirements on disk by compressing your data. “So it saves disk space?” you may be asking. It sure does! But it also means that more data fits in a page both on disk and in memory. It helps you squeeze more space out of that buffer pool. One other cool thing about compression is that it is pretty darn flexible in terms of being able to pick and choose the objects to compress.
So here are the steps you’ll take. First you’ll need to find out if you would benefit from compression. If you would benefit from compression, then it’s time to decide what kind of compression is going to work best (aka – most bang for the buck). Lastly, you’ll implement compression and determine what kind of downtime you’ll need or performance impacts you’ll see.
I often consider using table/index compression as a solution when I hear about these needs:
- Disk space pressure
- Disk I/O performance issues
- Memory performance issues
There are several different ways you can identify these symptoms. Everyone has their own preferences for how they make their determinations, but I am going to talk about how I like to do my investigation.