White Papers

49 BP1062 | Best Practices for Decision Support Systems with Microsoft SQL Server 2012 using Dell EqualLogic PS Series
Storage Arrays
of parallelism). Generally the memory requirements increase as the proportion of columns that
are strings increases. Therefore, decreasing the DOP can reduce the memory requirements for
building the columnstore index. Creating a columnstore index is a parallel operation, subject to
the limitations on the number of CPUs available and any restrictions set on MAXDOP setting.
• CPU: Creating a column store index might take slightly longer than creating clustered row store
index on the same set of data, as extra CPU cycles are required for compression. From the tests
in section 6.3.4
, there was an increase in the percentage CPU utilization while running TPC-H
queries on tables with columnstore index. To avoid any CPU bottlenecks while using
columnstore index, it is necessary to set the needed CPU cores at the server running SQL Server
database. The new batch mode processing enhancement in the Query Optimizer, optimized for
multicore CPUs and increased memory throughput of modern hardware architecture would be
beneficial in reducing the CPU time.
• Does not support SEEK: If the query is expected to return a small fraction of the rows, then the
optimizer is unlikely to select the columnstore index. If the table hint FORCESEEK is used, the
optimizer will not consider the columnstore index.
• Cannot be combined with the following features:
- Page and row compression, and vardecimal storage format (a columnstore index is already
compressed in a different format.)
- Replication
- Change tracking
- Change data capture
- Filestream