James is a big data and data warehousing technology specialist at Microsoft. He is a thought leader in the use and application of Big Data technologies, including MPP solutions involving hybrid technologies of relational data, Hadoop, and private and public cloud. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence architect and developer. He is a prior SQL Server MVP with over 30 years of IT experience. James is a popular blogger (JamesSerra.com) and speaker, having presented at dozens of PASS events including the PASS Business Analytics conference and the PASS Summit. He is the author of the book “Reporting with Microsoft SQL Server 2012”. He received a Bachelor of Science degree in Computer Engineering from the University of Nevada-Las Vegas.
Columnstore indexes are built and processed completely in memory. You will receive an out-of-memory error if you do not have enough memory to build the columnstore index. Column store processing is optimized for in-memory processing, however, they do not reside in memory after they are created. The index data is initially stored on disk. It is loaded into memory just like a row index is, and will then have the benefit of being in cache. Only the columns needed must be read. Therefore, less data is read from disk to memory and later moved from memory to processor cache, thereby providing a big performance benefit. The whole index is not stored in memory like PowerPivot. A columnstore index is persisted on disk just like any other index.
SQL Server Columnstore Index FAQ
Video Overview of the columnstore feature