Why Don’t People Use Columnstore Indexes?

I saw this question on SQL Server Central the other day and had an immediate, visceral reaction. I know why.

Now, before I explain my answer, please, let me reassure you. I get it. You’re busy. What I’m about to suggest is not meant as a direct critique of you. It’s just an observation of the human condition. Heck, maybe I’m wrong. So, before you write the angry screed about how busy you are and why you can’t possibly do what I’m about to suggest, believe me, I already understand. I’m still going to suggest something that’s going to make some of you angry.

Common Knowledge

If you’ll permit me, I want to talk about Extended Events before we talk about Columnstore.

SIDE NOTE: Standing invitation, any time I’m at an event, if there’s time, ask me, and I’ll do an impromptu class on Extended Events, why you should be using them, how to use them properly. Just ask.

Extended Events were introduced in 2008.

They sucked.

Seriously. They were not quite ready for prime time. Believe me. I tried to use them, but they just weren’t functional. 2008R2 didn’t improve the situation. Others, such as Jonathan Kehayias, saw the inherent potential, and started to use them. I, on the other hand, stayed firmly with #TeamTrace.

Then, Microsoft fixed Extended Events. 2012 just rearranged the landscape. They were awesome. I started using them. Now I’m on #TeamExEventsand I couldn’t be happier about it.

However, it was too late. Common knowledge was firmly established. Extended Events suck (so very wrong). They’re hard to use (they’re not). Extended Events don’t capture as much data as Trace (yeah, they capture more). They don’t have as much functionality (ok, with the exception of marrying Perfmon data to Trace Data, Extended Events actually have a lot more functionality). The GUI is horrible (well, it is, until you understand how to use it properly, see my invite above).

Columnstore And Common Knowledge

Columnstore indexes were introduced in SQL Server 2012.

They sucked.

OK, that’s actually strong. They were awesome. However, the fact that they were read only seriously limited their adoption. Common knowledge was very quickly established that most of us would never use these. They’re a horror to maintain. Yeah, for certain problem spaces (you know, that tiny problem space called “data analysis”), they’re useful, yet, most of us won’t benefit and the pain is too high.

Then, in SQL Server 2014, updateable columnstore indexes were introduced. The world changed, but no one noticed. Why?

Common knowledge.

It was already common knowledge that columnstore indexes didn’t work for most of us.

Fact is, that’s not true. Now that we have clustered columnstore and non-clustered columnstore, you can go nuts. Most of your data access is through analytical channels? Awesome, use a clustered columnstore. Sometimes though, you need point lookups. Not a problem, add a nonclustered b-tree index to the clustered columnstore. Go here to learn more about Columnstore Indexes.

In short, today, we can completely orient our data storage with our principal data access. Yet, most people are not using these things at all.

Because, common knowledge is already established.

It’s Common Knowledge

Common knowledge is a trap. Don’t believe me?

Everyone Knows

• It’s common knowledge that your Page Life Expectancy has to be greater than 300 or there’s a problem on your server.
• Everyone knows that the best way to deal with excessive parallelism is to set the MAXDOP = 1 on your servers.
• CXPACKET waits are a completely useless wait statistic and should be ignored
• Never write a query with more than three or four joins

I can keep going, but here’s the point. Every one of these statements is either utterly false, or has huge caveats attached. For example, CXPACKET waits aren’t useful… prior to SQL Server 2016 SP1 (or maybe a CU, I forget). After that, in other words, with all the modern versions of SQL Server and Azure SQL Database, CXPACKET waits are strong indicators of issues with your system. Don’t ignore them.

Yet, all this “common knowledge” is constantly being perpetuated. I still can’t believe I see the suggestion that three joins are too many. This was a myth in SQL Server 6.5, let alone today. Yet, it’s out there.

Why?

You!

It’s you.

You’re not spending your time learning. You’re learning once. You’re evaluating a technology once. You’re picking up the common knowledge one time, then never assessing it, ever again.

I get it. You have work to do. Learning isn’t a part of your work. Oh, but there you’re wrong. It is. Technology moves. You are a technologist. You must learn in order to move with the technology. You must get in the habit of reassessing assumptions and reevaluating that the common knowledge that you have, might be wrong, old, not applicable.

Because, the next thing most people do after they learn something is repeat it. “Hey boss, found the answer to the problem. Put FAST 1 on every query and it’ll run faster.” or whatever myth we’re perpetuating today. Then, it gets added to our “Best Practices” documents, built into the monitoring tool and repeated every time you answer a question from a Junior DBA or online.

Conclusion

As I said at the top, I understand. There’s not enough time in your day/week/month/year to keep up with the changes to technology. I’m sorry, but you’re just going to have to make some time. Carve out a piece of your day/week/whatever to get some learning in. Get to your local user group on your own time. Go to SQLSaturday. Read blogs by trusted industry experts who are keeping up with the technology shifts. Do what is necessary to reevaluate the “common knowledge” you have in your head.

Do this because, when you don’t, you’re not simply hurting yourself. It’s not that, oh, you’re not keeping up with the cool kids. It’s because you’re misusing, abusing, not using, tools that would improve your organization. Extended Events is an amazing technology. Learn it (see my invite). Columnstore fundamentally changes the data access and performance tuning game. Figure out how you can use it. Your organization will benefit. Further, you, and your career, will benefit.

Done. You can be mad at me now.