Column-based query accelerator in SQL Server 2012

Question!

I have been researching, SQL Server 2012 (aka Denali) and Microsoft has a pre-release available. The pre-release is located here with some information on key features. As I have downloaded the pre-release and installed on a VM. I have been curious about the following key feature mentioned. But Im not sure of its ability.

Column-based query accelerator

Column-Based Query Accelerator will help dramatically increase query
performance ~10x and reduce
performance tuning through interactive
experiences with data for near instant
response times and streamlined setup
which removes the need to build
summary aggregates.

What I would like is to see some explanation of the performance enhancement and perhaps an example, as I do not understand What "Column-based query" acceleration is? Any Insight would be helpful.



Answers

Sounds like a Business Intelligence thing.

Query aren't "interactive" and don't generally have "summary aggregates".

MS has put a lot into Analysis Services.

Edit: it's also possible that it's already known and blogged about, but the marketing monkeys changed the name :-)

By : gbn


Columnar storage is a physical layout optimization where data is stored by columns, and not rows. In some use cases, the advantages here are many:

1) less read time - need to compute an aggregate on a value - no need to read the rest of the row - so less read time

2) data compression - as the column data is likely similar, you can get greater compression ratios

3) ordinal indexing (sometimes)

this approach falls apart when data is inserted and updated, but for read-only and append use-cases the performance benefits can be astounding.

Update Refs

http://en.wikipedia.org/wiki/Column-oriented_DBMS http://www.globaldataconsulting.net/articles/theory/columnar-databases-and-data-warehouse



This video can help you solving your question :)
By: admin