Columnar databases have been kicking around the DB world for well over a decade, and the topic has gained momentum in more recent years. While not suitable for OLTP systems, they do offer some exciting performance opportunities in data mining scenarios when data updates can be managed. For an overview of columnar databases and the expected advantages, I like the following brief article from Ralph Kimball:

With SQL Server 2012, Microsoft has finally added this capability via columnstore indexes. I've been anxious to take a look at this new functionality using real data. In fact, this is the type of feature that begs for a test using records from a production database. I used a small test machine: 64-bit Windows 7, 4 GB RAM, and 4 cores @2.60 GHz. And by "small test machine," I mean my laptop.

The data table contains 4.1 million records and 14 fields, all of type smallint, int, or bigint. Each record represents what is essentially an end-user click event. I drafted three separate queries to represent different types of selection criteria. Each query contains one aggregation (sum) with some level of grouping.

Query # 1

Sum the user actions across all records by date, business code, and business user. This scenario isn't representative of the typical query against this data, but it is the simplest of the three examples.

Query #2

Sum the user actions across all records by date, business code, and business user, restricting to a single group of business codes. This query begins to capture more of how the data warehouse would typically be queried.

Query #3

Sum the user actions across all records, for certain business codes, regardless of user. This query very closely matches the logic of a current report in our production environment. Data is inserted from the fact table into 2 temp tables from which the final result set is selected.

Results

This first graph shows the logical reads for each of the three queries.

This second graph compares the CPU times:

Additional Thoughts

We'll definitely be looking at implementing columnstore indexes in certain scenarios in our data warehouse environment. I won't claim that these queries represent exhaustive (or scientific) tests, but they do get the mind thinking of new possibilities. Some additional thoughts:

The smallest difference between the scenarios (CPU times for query 2) still offered a 10x advantage.
Only the queries using the columnstore indexes performed physical reads in addition to the logical reads. I didn't include a breakdown of them above, but here are the physical reads reported: 6, 5, 4.
The queries utilizing the columnstore index queries also reported read-ahead reads.
I'd like to explore the effect of parallelization on the results. I used MAXDOP = 0 for each query, letting the optimizer determine how many cores to use. With the licensing costs of SQL Server 2012 driven by cores, we're doing a lot more analysis of CPU utilization/management.
I'm curious about utilizing columnstore indexes in temp tables. We currently utilize temp tables in several of our DW reports for on-the-fly building of parameterized data sets. While working through these queries, I noticed that the speed at which the columnstore indexes are created on the temp tables combined with certain of our use cases made this a compelling question.