Database Reference
In-Depth Information
Table 35-1.
Batch size and data distribution during bulk insert
Batch size
Rows added to row groups
(column-based storage)
Rows added to delta store
(row-based storage)
99,000
0
99,000
150,000
150,000
0
1,048,577
1,048,576
1
2,100,000
1,048,576; 1,048,576
2,848
2,250,000
1,048,576; 1,048,576; 152,848
0
SQL Server loads columnstore data to memory on a per-segment basis and, as you remember, segments
represent data for a single column in a row group. It is more efficient to load and process a smaller number of fully
populated segments as compared to a large number of partially populated segments. An excessive number of partially
populated row groups negatively affect SQL Server performance. I will provide an example of this later in the chapter.
If you bulk load data to a table with a clustered columnstore index, you will achieve the best results by choosing
a batch size that is close to 1,048,576 rows. This will guarantee that every batch produces a fully populated row group,
reduces the total number of row groups in a table, and improves query performance. Do not exceed that number,
however, because the batch would not fit into a single row group.
Batch size is less important for non-bulk operations. Trickle inserts go directly to a delta store. In some cases, SQL
Server can still create row groups on the fly in a similar manner to bulk insert when the size of the insert batch is close
to or exceeds 1,048,576 rows. You should not rely on that behavior, however.
Delta Store and Delete Bitmap
Let's analyze the structure of delta stores and delete bitmaps and look at the format of their rows. As a first step, let's
create a table, populate it with data, and define a clustered column store index there. Finally, we will look at segments
and row groups with the
sys.column_store_segments
and
sys.column_store_row_groups
views.
Listing 35-1 shows the code that does just that. I am using
MAXDOP=1
option during the index creation stage to
minimize the number of partially-populated row groups in the index.
Listing 35-1.
Delta store and Delete bitmap: Test table creation
create table dbo.CCI
(
Col1 int not null,
Col2 varchar(4000) not null,
);
;with N1(C) as (select 0 union all select 0) -- 2 rows
,N2(C) as (select 0 from N1 as T1 cross join N1 as T2) -- 4 rows
,N3(C) as (select 0 from N2 as T1 cross join N2 as T2) -- 16 rows
,N4(C) as (select 0 from N3 as T1 cross join N3 as T2) -- 256 rows
,N5(C) as (select 0 from N4 as T1 cross join N4 as T2) -- 65,536 rows
,N6(C) as -- 1,048,592 rows
(
select 0 from N5 as T1 cross join N3 as T2
union all
select 0 from N3
)
