SQL 2014 : Clustered Column store Index (CCI)

Posted: 12/09/2015 in SQL 2014
Tags: ,

In my previous post we had discussed about column store index (non clustered), after 2 years in Microsoft’s new version SQL 2014 they have introduced the clustered column store index. The main limitation of the column store indexed tables were ready only (it was not apt for OLTP database), in the latest version Microsoft has introduced clustered column store index with eliminating data manipulation restriction.

Let us discuss how Microsoft achieved this feature.

Delta storage (Insert):  The data insert operations are costly in case of column store index, to over come the scenario SQL has introduced this new storage space called Delta storage. In delta store data would be stored as a traditional method (row store method) and data would be moved column store method using a mechanism called tuple mover. Tuple mover is mechanism that runs like a job in every 5 minutes and move all the rows to column store index without taking the table offline. Delta store is not used  in case of bulk insert.

The tuple mover waits delta storage to reach a limit of 1048576 rows, if the delta storage reached this number of rows the particular delta store would be closed and that would be ready to move column store index. If tuple mover finds a delta storage like this, tuple mover pull data from the storage and compress the data and then move to column store index. Mean while data can be inserted to another available delta storage so this process can be performed online.

Delete Bitmap(Delete) : To perform a efficient delete from column store index is a hard task, to over come SQL has introduced a segment called Delete bitmap this contains the information of deleted rows. When user requests for rows SQL retrieve records which are not exist in Delete bit map. So basically there is no changes happened in column store index when delete operations perform and the deleted data exist in the index pages. Index pages are get refreshed only when we rebuild or tuple mover.

Update : We have already discussed about how delete and insert works in column store indexes,for update SQL uses both delete and insert operations. SQL delete existing row using delete bit map method (virtual delete) and insert rows to the delta stores as usual insert operation. When next time select request for the row, delete bit map hides the old row and show the new row from Delta store.

ColumnStore2014_01

Now we have understood how the insert delete operations are taking place in column store index, in SQL 2014 we can create only one column store index on a table.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s