Want to use columnstore indexes? Take the ColumnScore test.

Columnstore Indexes

When columnstore indexes first came out in SQL Server 2012, they didn’t get a lot of adoption. Adding a columnstore index made your entire table read-only. I often talk about how indexing is a tradeoff between fast reads and slow writes, but not a lot of folks can make their writes quite that slow.

Thankfully, Microsoft really poured a ton of work into improving columnstore indexes over time. The What’s New in Columnstore Indexes page of the SQL Server documentation keeps getting more “yes” answers over newer versions.

Today, with widespread SQL Server 2016 adoption, at least some of your databases could safely implement columnstore indexes to make reports go faster. But should you? And will columnstore indexes backfire and kill non-reporting-query performance? And will your data loading techniques come back to haunt you over time?

Take the 7-question test at ColumnScore.com to find out.

Previous Post
Announcing a New Class: Fundamentals of Columnstore
Next Post
Free Webcast on Wednesday: The New Robot DBAs in SQL Server 2017, 2019, and Azure

19 Comments. Leave new

  • A survey? Why? What’s wrong with a single T-SQL script that does the same for you?

  • Curtis Browne
    October 1, 2020 1:22 pm

    Is Brent Ozar Unlimited the force behind ColumnScore? The website has no info that would allow someone to know if it’s trustworthy or a humor site!

  • Koen Verbeeck
    October 2, 2020 3:48 am

    I’d argue columnstore indexes can be useful even if your data isn’t over 1 terabyte and over 1 billion records. I’d set the limits rather at 100 million rows and 100GB. Also, in the survey, there’s no option for 10-100 million rows.

  • One more unintended benefits of creating even nonclustered columnstore is you start getting more batch mode operators in your execution plans (in 2016 and 2017 I believe). We’ve created nonclustered columnstore on some of the fact tables ranging from 50 M to 150 M rows. This got us improvements in more places than we were hoping for

    • Yes, but on the flip side, that also has unintended side effects. Your OLTP-style queries can go dramatically slower if they get batch mode where it’s not appropriate, especially in 2016-2017. I demo that in my Mastering Query Tuning class.

  • The ColumnScore quiz is a cool idea – but it’s a bit aggressive. I put in a handful of scenarios where Columnstore indexes were 100% a good idea, and consistently got Cs and Ds.

    The biggest factors I’ve used in this decision are the size of the table and how it is read (ordered by some potentially partitionable key column, ie a date). For writes, I mostly care about there being zero update statements and little/no unordered deletion. I’ve found that actual storage size has impacted my decision making less than most other considerations. Rowgroup elimination occurs based on row counts, not gigabytes, and segment elimination cares less either way.

    Weighting the questions a bit differently would help. The quiz may inadvertently tell someone to not use a columnstore index when it may be a superior solution to a classic OLTP solution. Future growth is also worth a callout as a table with 500mil rows that will add 500mil a year is essentially a 1bil row table in training : )

    That was a long comment, but thanks for listening : )

    • Rowgroup elimination also flat out doesn’t work over time as indexes get rebuilt – but I’m sure you knew that from my Fundamentals of Columnstore class, right? 😉


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.