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?
There are a few aspects to consider:
- columnstore objects aren't cached in the buffer pool but in their own area http://www.nikoport.com/2014/08/11/clustered-columnstore-indexes-part-38-memory-structures/ but SQL Server manages the allocation of space to either the buffer pool or the columnstore object pool
- columnstore objects must be processed as the data is loaded into rowgroups and this is memory-intensive operation
- since the data is highly compressed, more data can be cached in RAM when the data is stored in columnstore than when stored as rows
As to your question of 'will I need more RAM' then the answer is probably no, since the engine will use whatever RAM is available when you're creating the index, unless you're severely constrained. http://www.nikoport.com/2014/06/21/clustered-columnstore-indexes-part-31-memory-pressure-and-row-group-sizes/
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.
The main point has already been stated in answer by mendosi: the Columnstore Object Pool is located outside the Buffer Pool. See the blog he linked.
The question is a bit unclear.
Do you want the Buffer Pool to have the same memory available when you start using columnstore indexes? If yes, then:
- Don't change the MAX memory setting for the instance (assuming it's already set to a sensible value, taking into account what else is on the machine).
- Increase memory on the machine, since you'll need it for the Columnstore Object Pool. How much? There is no memory limitation for the Columnstore Object Pool on Enterprise Edition. On SQL 2016 SP1+ Standard Edition, the Columnstore Object Pool is limited to 32GB per instance (see SQL Server 2016 SP1: Know your limits).
On the other hand, if you're fine with Buffer Pool getting less memory and/or you can't increase memory on the machine - decrease the MAX memory setting on the instance, since you'll need memory for the Columnstore Object Pool.
In any case, I recommend monitoring memory usage and tuning machine memory and/or MAX memory setting accordingly. As a starting point, some DMVs:
SELECT * FROM sys.dm_os_sys_memory; SELECT * FROM sys.dm_os_process_memory; SELECT * FROM sys.dm_os_memory_broker_clerks; --undocumented DMV! SELECT name ,type ,memory_node_id ,pages_kb ,page_size_in_bytes ,virtual_memory_reserved_kb ,virtual_memory_committed_kb ,shared_memory_reserved_kb ,shared_memory_committed_kb FROM sys.dm_os_memory_clerks ORDER BY pages_kb DESC;