Stuff I Learned In Joe Obbish’s Precon On Column Store

Joy Of Joys

---

Speaking daily with Joe is one of the best choices I’ve ever made. Not only do I regularly learn things about SQL Server that I would probably not run into, but he’s quite a character. That’s why when I started to help organize SQL Saturday NYC, I was more than happy to offer him a precon.

While it may be impractical to have more Joes in the world, it’s very practical to have people learn from him.

So what did I learn from his precon?

Column store is Different

---

How you load data into tables matters a lot. Not only is the number of rows you insert at a time crucial, but all sorts of things from the column data types to the inserted values can work for or against you. This can affect compression, query performance, and how well data loading scales.

The other place where column store differs from row store indexes is that maintenance is important. Yes, the very maintenance that’s an absurd crutch to row store indexes can be make or break to column store performance. Right now, the only two solutions that have special rules for column store indexes are Niko Neugebauer’s CISL scripts, and the Tiger Team’s Adaptive Defrag. Other maintenance solutions aren’t taking the right things into account.

Modifications Are Harsh! Deleting and Updating rows in column store indexes can have some nasty side effects — the kind that make maintenance necessary.

Performance Tuning Is Wild

---

If you’re dealing with column store indexes, chances are you’re dealing with some really big tables. While that by itself doesn’t change how you tune queries, you do have to remind yourself that “missing indexes” aren’t going to be the answer. You also need to keep in mind that some operators and query constructs don’t allow for good segment/row group elimination. You can think of those like partition elimination in a partitioned table. You can skip large amounts of data irrelevant to your query.

Strings Are The Devil

---

That’s all. Avoid them.

Details Are In The Devil

---

I’m not giving away too much here, because I hope Joe will deliver this again. If you see it pop up in your area, buy tickets ASAP.

Thanks for reading!

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.