patternsqlMinor
Does SQL Server require more RAM to hold columnstore indexed tables than traditional rowstore?
Viewed 0 times
tablesrowstoretraditionalsqlcolumnstoremorethanindexedholdrequire
Problem
I would like to implement (SQL Server 2014) clustered columnstore indexes on some very large, wide tables. Will I need more RAM to support this? If so, how do I determine how much?
Solution
More RAM as opposed to what? Compared to a normal clustered index, the data is highly compressed, so should take less memory during querying. However, the process of building the CCI can be very memory intensive. See the product documentation
Plan for enough memory to create columnstore indexes in parallel
Creating a columnstore index is by default a parallel operation unless memory is constrained. Creating the index in parallel requires more memory than creating the index serially. When there is ample memory, creating a columnstore index takes on the order of 1.5 times as long as building a B-tree on the same columns.
The memory required for creating a columnstore index depends on the number of columns, the number of string columns, the degree of parallelism (DOP), and the characteristics of the data. For example, if your table has fewer than one million rows, SQL Server will use only one thread to create the columnstore index.
If your table has more than one million rows, but SQL Server cannot get a large enough memory grant to create the index using MAXDOP, SQL Server will automatically decrease MAXDOP as needed to fit into the available memory grant. In some cases, DOP must be decreased to one in order to build the index under constrained memory.
Plan for enough memory to create columnstore indexes in parallel
Creating a columnstore index is by default a parallel operation unless memory is constrained. Creating the index in parallel requires more memory than creating the index serially. When there is ample memory, creating a columnstore index takes on the order of 1.5 times as long as building a B-tree on the same columns.
The memory required for creating a columnstore index depends on the number of columns, the number of string columns, the degree of parallelism (DOP), and the characteristics of the data. For example, if your table has fewer than one million rows, SQL Server will use only one thread to create the columnstore index.
If your table has more than one million rows, but SQL Server cannot get a large enough memory grant to create the index using MAXDOP, SQL Server will automatically decrease MAXDOP as needed to fit into the available memory grant. In some cases, DOP must be decreased to one in order to build the index under constrained memory.
Context
StackExchange Database Administrators Q#153519, answer score: 6
Revisions (0)
No revisions yet.