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.