Database Reference
In-Depth Information
Figure
35-1
illustrates the structure of a clustered columnstore index in a table that has two partitions. Each
partition can have a single delete bitmap and multiple delta stores. This structure makes each partition self-contained
and independent from other partitions, which allows you to perform a partition switch on tables that have clustered
columnstore indexes defined.
Figure 35-1.
Clustered columnstore index structure
It is worth noting that delete bitmaps and delta stores are created
on-demand
. For example, a delete bitmap
would not be created unless some of the rows in the row groups were deleted.
Every time you delete a row that is stored in a row group (not in a delta store), SQL Server adds information about
the deleted row to the delete bitmap. Nothing happens to the original row. It is still stored in a row group. However,
SQL Server checks the delete bitmap during query execution, excluding deleted rows from the processing.
As already mentioned, when you insert data into a columnstore index, it goes into a delta store, which uses a
B-Tree format. Updating a row that is stored in a row group does not change the row data either. Such an update
triggers the deletion of a row, which is, in fact, insertion to a delete bitmap, and insertion of a new version of a row to
a delta store. However, any data modifications of the rows in a delta store are done the same way as in regular B-Tree
indexes by updating and deleting actual rows there. You will see one such example later in this chapter.
Each delta store can be either in
open
or
closed
state. Open delta stores accept new rows and allow modifications
and deletions of data. SQL Server closes a delta store when it reaches 1,048,576 rows, which is the maximum number
of rows that can be stored in a row group. Another SQL Server process, called
tuple mover
, runs every five minutes and
converts closed delta stores to row groups that store data in a column-based storage format.
Alternatively, you can force the conversion of closed delta stores to row groups by reorganizing an index with the
ALTER INDEX REORGANIZE
command. While both approaches achieve the same goal of converting closed delta stores
to row groups, their implementation is slightly different. Tuple mover is a single-threaded process that works in the
background, preserving system resources. Alternatively, index reorganizing runs in parallel using multiple threads.
That approach can significantly decrease conversion time at a cost of extra CPU load and memory usage.
■
Note
We will discuss clustered columnstore index maintenance in more detail later in this chapter.
