Columnstore Indexes: What you Need To Know About This SQL 2016 Feature

Ok, I’ll admit, Columnstore indexes aren’t new.

They were introduced in Microsoft SQL Server 2012 and have been making large report queries (the big problematic ones across millions of rows) much faster ever since. You really should know what they do, how to use them, and what impact they’ll have on your applications and reporting analytics systems.

Not Exactly New

This feature, though useful, had some key limitations in SQL 2012 and SQL 2014. Adding Columnstore indexes forced developers to take some workarounds that got painful if you had a data warehouse or reporting server that updated more than once a day. Some of our clients that have 15-minute refreshes really felt some pain, and here’s why: while a Columnstore index made your report queries 5x-10x faster or better, it made your data read-only.

Not any more. In SQL 2016 (released to the public in Q2 2016, we hear), Columnstore indexes can be added to big table and remain writeable. This means their full potential is now unlocked.

You may have heard of Columnstore indexes already, they’ve been around, but had the gotcha around being read-only. No more. More about how Columnstore indexes have evolved here.

Where to Use Columnstore?

Nonclustered Columnstore indexes can be added to existing tables in your database to dramatically improve the performance of queries that read large amounts of data (inception-to-date, year-to-date, period-to-date, etc.) By nature, Microsoft SQL Server is very good at looking for needles in haystacks, but when you want to do the heavy lifting for the whole bale of hay, you need to change your approach and use different tools. SQL Server has always made these tools available to the database developer, but Columnstore is really a big new difference maker, especially in SQL Server 2016.

Let’s not also forget the storage benefit of this highly compressed data, which can reduce footprint ondisk (and in memory!) by up to 10x. This means your disk and your server memory will go a lot farther.

How Does It Work?

Columnstore data is compressed up to 10x using a new data structure that Microsoft acquired and implemented prior to SQL Server 2012. It’s not new, and has proven benefits. Your SQL Server tables and indexes you’re used to are rowstore data, that is, logically organized in a table with rows. Incolumnstore, data is stored physically in column-wise bands that are highly compressed. The compression is far more efficient than traditional PAGE or ROW compression that combines heterogenous columns in a row. (Rowstore compression is also on disk and in-memory, and is almost always a win-win for performance and storage.)

Will I Have To Rewrite My Reports?

Nope.

There will always be a need for rowstore indexes and tables for your heavy transactional (OLTP) databases, so Columnstore certainly isn’t a replacement. Nonclustered columnstore indexes, like existing nonclustered rowstore indexes, can be added to SQL Server tables transparent to the application – no code changes required at the application or reporting analytics platform.

Columnstore indexes can be added to large existing tables and – transparent to the developers and end users of the report – dramatically improve read performance. We’re not just talking about reporting copies or snapshots or dimensional database designs either. Any high-value query (report) that pulls from a large table could benefit, now that Columnstore indexes are read/write in SQL Server 2016.

Performance tuning is one of the fun thing we get to do as consultants, our favorite type of work. If you‘d like more information on this important feature of SQL Server and how it can help your reporting and analytical performance today, let us know. We’d be happy to discuss how to plan, performance test and implement this feature, all without any changes to your existing reports or analytics platforms.

Information and material in our blog posts are provided "as is" with no warranties either expressed or implied. Each post is an individual expression of our Sparkies. Should you identify any such content that is harmful, malicious, sensitive or unnecessary, please contact marketing@sparkhound.com