Just like free money, for SQL server

I just discovered something today which got me very excited, like finding a $20 bill, laying on the ground.  It must be my lucky day!

Each time a new (or upgraded) development tool or server product comes out, I like to read-up on the new features. For example, when C# 5.0 came out, I read about the new features like .? which is very cool and solves OO problems that I’ve had to overcome my entire career.  Knowing about the new features is great because those things usually save you a lot of time or headaches.  You just have to know they exist and how to use them.

Today, I stumbled upon a feature in SQL server called “Data Compression”. Imagine this: if you could pick some (maybe not all) tables in your SQL database and have them compressed (eg. Zipped).  It would save space, right?  That alone would be really great.  My mind is already racing with a list of tables that I would apply this to.  Then I started thinking, “There must be a trade-off.  It would slow things down a little, while it zipped/unzipped the data”.  Therefore, I would have to apply it judiciously.

I’m glad I read more about it, because I nearly overlooked the bigger picture. If you have ever done performance studies on databases (DB servers), you will quickly find out that most databases are not CPU constrained.  They are hard drive constrained.  No matter how fast your SAN is, it is never enough.  So, imagine enabling a feature in your database which could increase your disk throughput by a factor of 8!  Aye-carumba!  Right?  Yeah, so it turns out that when you compress your SQL tables, many will compress to (approx.) 1/10th their size (YMMV, depending on the content).  It also stays compressed during reads and writes.  Which means you are moving compressed data from disk to memory, and only de-compressing it in-memory.   Of course, if you have a query which does a table-scan, it would be noticeably slower, but if it is indexed, the index scan is still really fast.  You can even compress your indexes (for storing really big indexes which don’t change often). Indexes also are compressed on disk and decompressed in memory (only).

The concept of 1) using less disk, 2) using less disk bandwidth and 3) applying it at a per-table level, is just extremely compelling. I was like “Heck yes, I MUST upgrade to SQL 2016 immediately!”  Then I discovered that this feature has been there since SQL 2008.  So, if you have been hiding under a rock for the past 9 years, like I have, it is time to climb out, take a fresh breath of air, and pick up all of that free money laying on the ground in front of you.  Cha-ching!

To learn more, check out this great article. It does a magnificent job of explaining this free money and the various ways to apply it and the various tradeoffs: https://technet.microsoft.com/en-us/library/dd894051(v=sql.100).aspx


About Tim Golisch

I'm a geek. I do geeky things.
This entry was posted in Optimization and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s