THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Jimmy May

Columnstore Case Study #2: Columnstore faster than SSAS Cube at DevCon Security


This is the second in a series of posts documenting big wins encountered using columnstore indexes in SQL Server 2012 & 2014.  Many of these can be found in my big deck along with details such as internals, best practices, caveats, etc.  The purpose of sharing the case studies in this context is to provide an easy-to-consume quick-reference alternative. See also Columnstore Case Study #1: MSIT SONAR Aggregations

Why Columnstore?

As stated previously, If we’re looking for a subset of columns from one or a few rows, given the right indexes, SQL Server can do a superlative job of providing an answer. If we’re asking a question which by design needs to hit lots of rows—DW, reporting, aggregations, grouping, scans, etc., SQL Server has never had a good mechanism—until columnstore.

Columnstore indexes were introduced in SQL Server 2012. However, they're still largely unknown. Some adoption blockers existed; yet columnstore was nonetheless a game changer for many apps.  In SQL Server 2014, potential blockers have been largely removed & they're going to profoundly change the way we interact with our data.  The purpose of this series is to share the performance benefits of columnstore & documenting columnstore is a compelling reason to upgrade to SQL Server 2014.

The Customer

DevCon Security provides home & business security services & has been in business for 135 years. I met DevCon personnel while speaking to the Utah County SQL User Group on 20 February 2012. (Thanks to TJ Belt (b|@tjaybelt) & Ben Miller (b|@DBADuck) for the invitation which serendipitously coincided with the height of ski season.)

The App: DevCon Security Reporting: Optimized & Ad Hoc Queries

DevCon users interrogate a SQL Server 2012 Analysis Services cube via SSRS. In addition, the SQL Server 2012 relational back end is the target of ad hoc queries; this DW back end is refreshed nightly during a brief maintenance window via conventional table partition switching.


Conventional relational structures were unable to provide adequate performance for user interaction for the SSRS reports. An SSAS solution was implemented requiring personnel to ramp up technically, including learning enough MDX to satisfy requirements.

Ad Hoc Queries

Even though the fact table is relatively small—only 22 million rows & 33GB—the table was a typical DW table in terms of its width: 137 columns, any of which could be the target of ad hoc interrogation. As is common in DW reporting scenarios such as this, it is often nearly to optimize for such queries using conventional indexing.

DevCon DBAs & developers attended PASS 2012 & were introduced to the marvels of columnstore in a session presented by Klaus Aschenbrenner (b|@Aschenbrenner)

The Details

Classic vs. columnstore before-&-after metrics are impressive.



Conventional Structures




10 - 12 seconds

1 second


Ad Hoc

5-7 minutes
(300 - 420 seconds)

1 - 2 seconds


Here are two charts characterizing this data graphically.  The first is a linear representation of Report Duration (in seconds) for Conventional Structures vs. Columnstore Indexes. 


As is so often the case when we chart such significant deltas, the linear scale doesn’t expose some the dramatically improved values corresponding to the columnstore metrics.  Just to make it fair here’s the same data represented logarithmically; yet even here the values corresponding to 1 –2 seconds aren’t visible. 


The Wins

  1. Performance: Even prior to columnstore implementation, at 10 - 12 seconds canned report performance against the SSAS cube was tolerable. Yet the 1 second performance afterward is clearly better. As significant as that is, imagine the user experience re: ad hoc interrogation. The difference between several minutes vs. one or two seconds is a game changer, literally changing the way users interact with their data—no mental context switching, no wondering when the results will appear, no preoccupation with the spinning mind-numbing hurry-up-&-wait indicators.  As we’ve commonly found elsewhere, columnstore indexes here provided performance improvements of one, two, or more orders of magnitude.
  2. Simplified Infrastructure: Because in this case a nonclustered columnstore index on a conventional DW table was faster than an Analysis Services cube, the entire SSAS infrastructure was rendered superfluous & was retired.
  3. PASS Rocks: Once again, the value of attending PASS is proven out. The trip to Charlotte combined with eager & enquiring minds let directly to this success story. Find out more about the next PASS Summit here, hosted this year in Seattle on November 4 - 7, 2014.

DevCon BI Team Lead Nathan Allan provided this unsolicited feedback:

“What we found was pretty awesome. It has been a game changer for us in terms of the flexibility we can offer people that would like to get to the data in different ways.”


For DW, reports, & other BI workloads, columnstore often provides significant performance enhancements relative to conventional indexing.  I have documented here, the second in a series of reports on columnstore implementations, results from DevCon Security, a live customer production app for which performance increased by factors of from 10x to 100x for all report queries, including canned queries as well as reducing time for results for ad hoc queries from 5 - 7 minutes to 1 - 2 seconds. As a result of columnstore performance, the customer retired their SSAS infrastructure.

I invite you to consider leveraging columnstore in your own environment. Let me know if you have any questions.

Published Monday, May 26, 2014 12:07 PM by aspiringgeek



Chris Adkin said:

Hi Jimmy,

I'm presenting a session on column store indexes and batch mode at SQL Bits. In this material I cover two broad scenarios, spinning disk based storage which leads to core starvation, the sweet spot of compression and storage and the new world in the form of flash/PCIe which can keep up with / swamp the available CPU capacity. For straight sequential scans with no joins the second scenario using heaps out performs any type of compression. Its only when batch model is introduced that the net affect of using column stores on low latency / high IOPS storage is positive. I suspect for certain types of storage heaps or conventional indexes plus batch mode would out perform column stores and batch mode, if the run time allowed for this that is.


May 29, 2014 7:59 AM

Garrett Edmondson said:


Thanks for this series of posts. It is nice to see that someone else is having similar results with Clustered ColumnStore Indexes. In one recent project that I worked on we replaced SSAS Multidimensional with Clustered ColumnStore Indexes and saw as good if not much better performance improvements.

The performance differences were so large that I had put them on a logarithmic scale as well.

June 22, 2014 5:18 PM

aspiringgeek said:

@Garrett: Yes, Columnstore is a game changer for many of the apps with which we work.  Practically each day I see another example of the way in which it's changing the way we interact with our data.  It's truly one of the unheralded gems in SQL 2014.

June 23, 2014 11:49 AM

Dave said:

Thanks for the article.

My question is how can a columnstore index help with SSAS?

Are they using ROLAP?

If they are using MOLAP then the cube would be processed after the DW updating completes, so perhaps the columnstore helps reduce cube processing time but it would have zero benefit for MDX queries.

January 24, 2015 6:52 AM
New Comments to this post are disabled
Privacy Statement