Database Reference
In-Depth Information
■
Loading data in smaller batches puts new data into the delta store and produces fully populated row groups
afterwards. however, insert performance is seriously affected. For example, when I inserted data in 99,999-row batches,
my system was able to insert only 37,500 rows per second.
Note
Large Delta Stores
For the next step, let's look at how large delta stores affect the performance of queries. SQL Server needs to scan these
delta stores during query execution.
For this test, I inserted 1,000,000 rows in small batches into the delta store of the first table from the previous test
(the table that had row groups fully populated). After that, I rebuilt the columnstore index, comparing the execution
time of the test query before and after the index rebuild.
The index rebuild process moved all data from the delta store to row groups. You can see the status of row groups
and the delta store before (on the left side) and after (on the right side) the index rebuild in Figure
35-9
.
Figure 35-9.
Row groups and delta store after insertion of 1,000,000 rows
Table
35-5
illustrates the execution times of the test query in both scenarios, and it shows the overhead
introduced by the large delta store scan during query execution.
Table 35-5.
Execution time and delta store size
Empty delta store
(Elapsed/CPU time)
1,000,000 rows in delta store
(Elapsed/CPU time)
2,148ms / 6,815ms
4,177ms / 8,453ms
Large Delete Bitmap
Finally, let's see how delete bitmaps affect query performance. For that test, I deleted almost 30,000,000 rows from a
table.
You can see the row groups' information in Figure
35-10
.
