Database Reference
In-Depth Information
Summary
The clustered columnstore indexes introduced in SQL Server 2014 address the major limitation of nonclustered
columnstore indexes, which prevent any modifications of the data in the table. Clustered columnstore indexes are
updateable, and they are the only instance of the data that is stored in the table. No other indexes can be created on a
table that has a clustered columnstore index defined.
Clustered and nonclustered columnstore indexes share the same storage format for column-based data. Two
types of internal objects support data modifications in clustered columnstore indexes. A delete bitmap indicates what
rows were deleted. A delta store stores new rows. Both delta stores and delete bitmaps use a B-Tree structure to store
the data.
Updating rows stored in row groups is implemented as the deletion of old and insertion of a new version of the
rows. Deletion and modifications of the data in a delta store deletes or updates rows in the delta store B-Tree.
Delta stores can store up to 1,048,576 rows. After this limit is reached, the delta store is closed and converted to a
row group in column-based storage format by a background process called tuple mover. Alternatively, you can force
this conversion with the ALTER INDEX REORGANIZE command.
A large amount of data in delta stores and/or delete bitmaps negatively affects query performance. You should
monitor their size, and rebuild the indexes to address performance issues. You should partition tables to minimize
index maintenance overhead.
Bulk insert operations with a batch size that exceeds approximately 100,000 rows creates new row groups
and inserts data there. A large number of partially populated row groups is another factor that negatively affects
query performance. You should import data in batches with a size of close to 1,048,576 rows to avoid this situation.
Alternatively, you can rebuild indexes after ETL operations are completed.
Columnstore indexes do not support any access methods with exception of an Index Scan . They are targeted
at Data Warehouse workload, and they should be used with extreme care in OLTP environments. In some cases,
however, you can use them in tables with historical data, storing active OLTP data in B-Tree tables combining all data
with partitioned views.
 
Search WWH ::




Custom Search