When I was in high school, Dad and I lived with his mom, my Grandma Ozar, for a couple of years. We took care of things around the house and made sure her coffee pot was always full. She could really down that coffee – at least two pots a day. (Looking back, if we could have reduced her caffeine consumption, she probably wouldn’t have needed so much Valium.)
I Can Quit Anytime I Want*
Grandma and some friends took a road trip to Las Vegas, and while they were gone, I threw an epic party. We’re talking 30 gallon garbage cans filled with homemade Jungle Juice. (I’m one of those reasons she couldn’t completely eliminate the Valium.) A couple hundred of my closest friends had a good old time.
A couple of my less-than-closest friends had a little too good of a time at my expense and started trashing the house. They walked up the staircase smashing the picture frames of every family photo, then started to throw a couch off the second floor balcony. My security guys (I’m telling you, it was that good of a party) carried them out before they got too carried away.
Cleaning Up After The Party
The next morning, the very-closest-friends did a fantastic job of getting things back to normal. We replaced all the picture frame glass, got the dirt out of the sofa, vacuumed the place top to bottom, and finished the Jungle Juice. I thought we’d hidden all our tracks, but we got busted by the tiniest of clues.
Someone had left beer bottle caps on top of door jambs all around the house.
Amazing! Who thinks of this stuff? You know they did it on purpose, too – they were just itching to get me into trouble. One beer cap on one door jamb, I could understand, but all over the house? Damn.
I was disinherited for that particular shindig.
I learned a valuable lesson: if you’re not absolutely sure you can clean up every trace of everybody else’s messes, you shouldn’t throw parties. Sooner or later, somebody you can’t trust is going to show up at your party, and they’re going to do something that’ll get you in trouble. Come to think of it, it’s just like being a DBA.
SQL Server 2008 Data Compression: No Inheritance Either
Microsoft SQL Server 2008′s Data Compression feature lets you compress objects – not just tables, but individual indexes. This compression does incur a little extra CPU power to handle the compression, but that extra overhead is more than offset by increased IO performance. Generally speaking, the database server is sitting around waiting on disk subsystems. Adding a little CPU work while dramatically reducing IO needs results in faster query return times. You need to test compression to see if it works well in your environment, because it may not work well in heavy-insert databases.
Today, though, I’m going to focus on the dark side of compression: a complete lack of inheritance.
When you compress tables and indexes, it’s a one-time action. You’re only taking care of what exists today. If someone (or even you) turns around and creates an index on that same table tomorrow, it won’t be compressed by default. Whoever creates the index has to make sure that it’s compressed, and there’s nothing in SQL Server Management Studio that will hint to them that other parts of that same object are compressed.
To make matters worse, your development, QA and production environments might all have different compression settings, and you’d never notice it at a glance. Compression is transparent to applications, so your developers won’t know why one environment performs much differently than another even though they have the same hardware, same indexes, same statistics.
Implementing compression is a multi-step process:
- Figure out what objects you should compress
- Plan to handle all of your environments (dev, QA, production)
- Compress them during a low-activity window
- Regularly patrol your environments checking for added objects that weren’t compressed
- Keep your environments in sync
If you don’t stay on top of all of these, you’ll need Valium too.
* – Yes, about that picture. No, it’s not mine. Yes, it’s licensed with Creative Commons. No, it wasn’t even the worst jungle juice picture I could find licensed with Creative Commons. Yes, I too am amazed that people upload their party pictures to Flickr, let alone license them with Creative Commons.
Would it be feasible to schedule semi-regular decompression and recompression times to “clean up” these stragglers?
Is there a basic DB size range where you would recommend that a DBA look at compression?
About the decompression/recompression times – no, actually, you just need to run queries regularly to see what objects aren’t compressed, and then decide whether or not to compress ‘em. Unfortunately, there’s no built-in tools to do that – the DBA has to roll their own.
I would recommend that anybody using SQL Server 2008 Enterprise look at compression, regardless of the size. If you care enough to spend extra for Enterprise Edition, you should check out that feature, because it can be free performance for no extra cost.
What a great way to start the blog post! Well done, my friend, well done. Who said data compression had to be a dry subject. hahahaha….
Thanks, sir. Turns out all these lessons I learned in my youth still help me to this day.
Too bad you have to by Enterprise version to get compression. You know I had a few parties like that many years ago. I remember it’s hard to hide holes in the wall also.
Great points! Didn’t think about inheritance. I wrote on this subject with several code examples on SSC last year, with even a presentation given in Vermont, last Spring.
It was early 2009 I posted, after a great project in previous Spring of 2008 using SQL 2005 Entreprise Ed.’s VarDecimal conversion – highly effective if you are still in SQL 2005 and your databases are heavy financial decimal data type dependant.
If you are using 2005/8 Developer Edition, you have all the features of Enterprise, therefore you can compress with ease – u just cannot use Standard Ed. for this function.
More code examples for SQL Server 2008.
Hugo – yep, and you also have to make sure you don’t have Standard Edition in your disaster recovery environment. That makes for an ugly surprise when you try to recover from a production outage only to find you don’t have the right edition available, and you can’t restore your databases. Whoops!
At what cost is compression ?
Being in this field for a number of years, there is one thing I learned “You get nuttin’ for free, there is always a cost”. I am ready to migrate one of our risk systems to 2008 from 2005, and compression looks like gold (gold, Jerry gold – Banya), especially for some of the huge, flattened out fact tables that are in the system, I’m talking a 7K row. I did not realize that you can use BOTH row and page compression on a table.
Paul – howdy, sir. The cost is usually heavy on highly written tables. If something’s being constantly inserted/updated/deleted, the CPU cost might overcome the IO benefits. The best scenario is large tables that get rarely updated, like data warehouse fact tables.