Database Reference
In-Depth Information
Archival compression is a great choice for static, rarely accessed, data. It is common for Data Warehouses to
retain data for a long time, even though historical data is rarely accessed. You may wish to consider applying archival
compression on partitions that store historical data and benefit from the disk space saving it achieves.
Clustered Columnstore Index Maintenance
Clustered columnstore indexes require maintenance similar to regular B-Tree indexes, even though the reasons for
doing the maintenance are different. Columnstore indexes do not become fragmented; however, they can suffer from
a large number of partially populated row groups. Another issue is the overhead of delta store and deleted bitmap
scans during query execution.
Let's run several tests and look at the issues involved in detail.
Excessive Number of Partially Populated Row Groups
For this test, I created two tables of a similar structure to the table shown in Listing 35-5, and I bulk imported almost
62 million rows with the
bcp
utility using 1,000,000 rows batches and 102,500 rows batches respectively.
Figure
35-8
illustrates the row groups in both tables after the import.
Figure 35-8.
Row groups after bulk import
Table
35-4
illustrates the execution time and number of I/O operations for the query from Listing 35-6 running
against both tables. As you can see, the query against a table with partially populated row groups took a considerably
longer time to execute.
Table 35-4.
Execution statistics for the tables with fully and partially populated row groups
Fully populated row groups
Partially populated row groups
Elapsed/CPU time
2,093ms / 6,612ms
3,762ms / 8,953ms
Logical reads
177,861
192,533
Physical reads
568
9,725
It is worth noting that the performance of batch inserts was also affected by smaller batch sizes. In the case of
1,000,000-row batches, my system was able to insert about 103,500 rows per second, contrary to 94,300 rows per
second in the case of the 102,500-row batches.
