Want to use columnstore indexes? Take the ColumnScore test.

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

17 Comments. Leave new

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

  • 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!

  • 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.


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.