Columnstore indexes and memory

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.

More info:

SQL Server Columnstore Index FAQ

Columnstore Indexes

Video Overview of the columnstore feature

About James Serra

James currently works for Microsoft specializing in big data and data warehousing using the Analytics Platform System (APS), a Massively Parallel Processing (MPP) architecture. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence/MDM architect and developer, specializing in the Microsoft BI stack. He is a SQL Server MVP with over 25 years of IT experience.
This entry was posted in Columnstore indexes, SQLServerPedia Syndication. Bookmark the permalink.

One Response to Columnstore indexes and memory

  1. Bill Anton says:

    James,
    Part of the benefit of ColumnStore indexing is that the data on the pages are highly compressed (corresponding to the cardinality of the values in the columns). This allows more data to be read into memory with each IO. Does the data remain compressed after being read in memory?

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>