Research Paper Week: Query Execution in Column-Oriented Database Systems

Columnstore Indexes
1 Comment

This week, I’m sharing some of my favorite papers that I’ve read. Sometimes they’re about future technologies that haven’t shipped yet – and may never ship! Sometimes, like this one, they’re not from Microsoft at all, but from someone else in the industry who’s explaining a problem that we face in SQL Server.

SQL Server 2012 introduced non-clustered columnstore indexes, but they didn’t catch on much because they forced the entire table to become read-only. Thankfully, Microsoft kept investing in columnstore indexes, and by SQL Server 2016-2017, they became pretty doggone good defaults for data warehouses.

If you’re a performance tuning internals geek who’s just starting your columnstore learning journey today, start with this: PDF: Query Execution in Column-Oriented Database Systems by Daniel J. Abadi.

Query Execution in Column-Oriented Database Systems

It’s a ~130-page monster from 2008, before SQL Server’s implementation shipped (but probably around the same time Microsoft was designing it.) You don’t really need Microsoft specifics in here – this white paper is just a thought-provoking foundation that covers:

  • The differences between rowstore and columnstore indexes
  • How query execution is different with columnstore indexes
  • How performance can be better if you can preserve batch mode longer through the query plan
  • When data should be turned from columns into rows, like for joins
  • How Abadi implemented columnstore himself to understand the implementation and tradeoffs

If you understand these concepts, you can transfer this knowledge pretty well over to SQL Server’s columnstore indexes and execution plans.

It’s not a light read, but it doesn’t require familiarity with columnstore indexes at all. Abadi takes you from zero to hero in here, but you’re probably not going to knock it out in one sitting. I certainly didn’t – it’s been on my iPad for a couple of years, and I’ve repeatedly revisited it, learning new things each time.

Folks who liked this white paper will also like these:

Previous Post
Research Paper Week: Plan Stitch: Harnessing the Best of Many Plans
Next Post
Research Paper Week: Automatic Indexing in Azure SQL DB

1 Comment. Leave new

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.