SQL Server 2014 Columnstore Indexes: The Big Deck

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