Share via

SQL Server 2014 Columnstore Indexes: The Big Deck

  • Article

The History

Though Columnstore indexes were introduced in SQL Server 2012; they're still largely unknown.  In 2012, some adoption blockers remained; yet Columnstore was nonetheless a game changer for many apps.  In SQL Server 2014, potential blockers have been largely removed & Columnstore is going to profoundly change the way we interact with our data.

I’ve been working with Columnstore Indexes since Denali alpha bits were available.  As SQL CAT Customer Lab PM, I hosted over a half-dozen customers in my lab proving out our builds, finding & entering bugs, & working directly with the product group & our customers to fix them. 

The Why

Why Columnstore?  If we’re looking for a subset of columns from one or a few rows,  given the right indexes, SQL Server has long been able to do a superlative job of providing an answer.  But if we’re asking a question which by design needs to hit lots of rows—reporting, aggregations, grouping, scans, DW workloads, etc., SQL Server has never had a good mechanism—until Columnstore.  Columnstore was a competitive necessity—our Sybase & Oracle customers needed a solution to satisfy what was heretofore a significant feature & performance deficit in SQL Server.  Our leadership & product team stepped up & provided a superb response.

The Presentation

I’ve delivered my Columnstore presentation over 20 times to audiences internal & external, small & large, remote & in-person, including the 2013 PASS Summit, two major Microsoft conferences (TechReady 17 & TechReady 18), & several PASS user groups (BI Virtual chapter, IndyPASS, Olympia, PNWSQL, Salt Lake City, Utah County, Denver, & Northern Colorado).

The deck has evolved significantly & includes a broad overview, architecture, best practices, & an amalgam of exciting success stories.  The purpose is to educate you & convince you that Columnstore is a compelling feature, to encourage you to experiment, & to help you determine whether Columnstore could justify upgrading to SQL Server 2014.

The Table of Contents

Here’s my deck’s ToC:

  • Overview
  • Architecture
  • SQL Server 2012 vs. new! improved! 2014
  • Building Columnstore Indexes
  • DDL
  • Resource Governor
  • Data Loading
  • Table Partitioning
  • Scenarios & Successes
    • Motricity
    • MSIT Sonar
    • DevCon Security
    • Windows Watson
    • MSIT Problem Management
  • Room for Improvement
  • Learnings & Best Practices
  • More Info

The Demos

I’ve included several demos, all of which are exceedingly simple & include step-by-step walkthroughs.

  • Conventional Indexes vs. Columnstore Perf
  • DDL
  • Resource Governor
  • Table Partitioning

Let me know if you have any questions.  In the meantime, enjoy!

Columnstore Deck and Demo Scripts.zip

Comments

  • Anonymous
    March 27, 2014
    Just wondering if that columnsstore index for OLTP databases or also for OLAP databases ? And if it is for OLAP should ROLAP instead of MOLAP become intresting ? Is there still use for columnstore after the introduction of hekaton/in memory engine ?

  • Anonymous
    April 12, 2014
    @Enders:

  1.  Columnstore was originally designed for use in DW scenarios, i.e., relational non-OLTP non-transactional databases.  Especially with the optimizer enhancements introduced in SQL 2014, we’ve found them to be very compelling.  There's potential for use in OLTP databases, yet I urge caution in their implementation, care, & feeding.  Absent a thorough understanding of columnstore internals & failing to comply with best practices re: read/write tables can result in unexpected & unpleasant surprises.  See the "Batch Size Case Study" in the attached deck for such a surprise (& the easy fix).
  2. Though columnstore & SSAS share a common code-base, you can’t create a columnstore index per se on an SSAS cube.
  3. Absolutely, ROLAP vs. MOLAP is interesting.  The SQL Marketeers claim that columnstore (ROLAP) can in some cases replace SSAS cubes (MOLAP).  I was frankly skeptical—until I met a customer who used columnstore in a real-life application to replace their cumbersome SSAS infrastructure (MDX be gone!).  The columnstore implementation was more performant by ~10x for canned queries & by ~100x for ad hoc queries.  See the “DevCon Security” scenario in the attached deck for more info.
  4. I hope this helps.  Let me know if you have any questions.
  • Anonymous
    December 10, 2014
    Great article! And hands down greater* presentation on Sql Saturday in DC last week!