THE SQL Server Blog Spot on the Web

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

Alberto Ferrari

New Whitepaper from SQLBI: Vertipaq vs ColumnStore

At the end of June 2012, I was in Amsterdam to present some sessions at Teched Europe 2012 and, while preparing the material for the demos (yes, the best demos are the ones I prepare at the last minute), I decided to make a comparison between the two implementations of xVelocity of SQL 2012, one is the VertiPaq engine in SSAS Tabular and the other one is the ColumnStore index in SQL Server. After some trials, I decided that ColumnStore was a clear loser, because I was not able to see a real improvement in performance for my test queries, involving complex calculations and many-to-many relationships.

But, hey, I am not a SQL expert, I work mostly on the BI side, might it be the case that I don’t know how to write a query that takes advantage of ColumnStore?

Being in a place full of SQL gurus from Microsoft and MVPs, I decided to ask for help. Hugo Kornelis was there and I had a chance to talk with him about ColumnStore performance. It turned out that he perfectly knew how to improve the performance of my queries and I was amazed to see that, after his intervention on SQL code, the two engines did run at a comparable speed. Moreover, sometimes ColumnStore was faster than VertiPaq. I suddenly decided to investigate more on the topic. I did not have time to prepare the material for the demos, but, at that point, a whitepaper was born.

At the end, I discovered (many thanks Hugo!) that the first implementation of the ColumnStore indexes in SQL2012 is not perfect and hopefully will be improved in the future. There are several limitations that reduce the effectiveness of ColumnStore indexes, as stated in the following post: http://msdn.microsoft.com/en-us/library/gg492088.aspx.

If you want to join me in this amazing trip on performance analysis of two incredible query engines, take a look at the whitepaper on “Vertipaq vs ColumnStore” on sqlbi.com and provide me all the comments you have, I always enjoy reading your thoughts, I learn a lot by comparing with you.

Published Friday, August 17, 2012 4:11 PM by AlbertoFerrari

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About AlbertoFerrari

Alberto Ferrari is a Business Intelligence consultant. He his interests lie in two main areas: BI development lifecycle methodologies and performance tuning of ETL and SQL code. His main activities are with SSIS and SSAS for the banking, manufacturing and statistical sectors. He is also a speaker in international conferences like European PASS Conference and PASS Summit.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement