Bulk Load Processes Explained

Traditional transactional workloads are fully logged. When fully logged, enough metadata is written to the transaction log so that SQL Server can recover from a failed transaction. In addition, data is written to the transaction log to ensure that point-in-time recovery is possible.

Fully logged transactions in OLTP scenarios are ideal as they allow a database to be rolled back to a specific point in time in the event of application problems. In addition, point-in-time recovery allows for research and forensics into the details of transactions, when needed.

Sometimes, transactional writes are intentionally subdivided into smaller batches to ensure that each transaction is short and fast and causes minimal contention with other queries against the same data.

Analytic workloads differ greatly in their recovery needs as data loads tend to be larger, less frequent, and asynchronous. When analytic data loads fail, the most common recourse is to investigate the problem, resolve it, and then rerun the data load. Point-in-time recovery within data load processes is less important than simply having recovery available to a point in time prior to the data load. Therefore, OLAP data loads can benefit greatly from minimally logged insert operations.

Because point-in-time recovery can be critical to transactional systems, any process that utilizes bulk loading needs to be documented well enough to not accidentally be used in a scenario where it is not desired.

Columnstore indexes use built-in bulk load processes that automatically insert larger batches directly into rowgroups without using the delta store. This greatly improves insert speeds for large analytic data loads and reduces transaction log bloat resulting from those processes.

Bulk Loading into Columnstore Indexes

The breakpoint for deciding whether to use a bulk load process or to use the delta store is 102,400 rows. Inserts of less than 102,400 rows will always be written to the delta store via a fully logged process, whereas inserts of 102,400 rows or more will be written directly to rowgroups in the columnstore index, bypassing the delta store. This decision can be shown in Figure 8-1.

Unlike some of the other types of minimally logged insert operations in SQL Server, there are no prerequisites to take advantage of bulk loading data into a columnstore index. There is no need to adjust isolation levels, use explicit table locking, or adjust parallelism settings. Since bulk loading data will be the desired operation for large inserts into columnstore indexes, it is the default and will be used automatically when possible.

SQL Server can bulk load data into a columnstore index in multiple parallel threads so long as the data for each thread is targeting a different data file. This is automatic and requires no particular user action to accomplish. Columnstore bulk insert operations acquire exclusive locks against target rowgroups, and so long as parallel inserts target separate data files, they are guaranteed to not overlap the rowgroups they are inserting into.

When data is inserted into a partitioned columnstore index, that data is first assigned a partition and then each group of rows is inserted into their respective partitions. Therefore, whether bulk load processes are used will be dependent on the numbers of rows inserted into the target partition, rather than the total number of rows inserted by the source query. Typically, analytic tables will have a current/active partition that accepts new data, whereas the remaining partitions will contain older data that is no longer written to (outside of one-off data loads, software releases, or maintenance events).

Performance of Bulk Loading into Columnstore Indexes

The result is a single row that indicates the total log size for the insert transaction, as seen in Figure 8-2.

This insert takes less than a second to complete. A new clean table is used to ensure that there are no residual transactions from previous demonstrations that would pollute the log. The results from fn_dblog() are seen in Figure 8-3.

Note that the transaction log size for the insert is 118,192 bytes, or about 115KB. This constitutes a significant reduction in transaction size when compared to a page compressed rowstore index.

Note that the delta store needs to be referenced separately to be included in the results. The transaction log space consumed by each object is seen in Figure 8-4.

The insert into the delta store was significantly more expensive than any operation demonstrated thus far in this chapter, with a transaction size of 1,300,448, or about 1.2GB. As a result, there is a significant incentive to take advantage of bulk loading data into columnstore indexes when possible.

Trickle Insert vs. Staged Insert

While inserting into the delta store may seem expensive, it is nowhere near as costly as repeated inserts into a columnstore index would be. If an analytic table is often targeted with many smaller insert operations, there is significant value in collecting those rows into a temporary table first, prior to inserting into the columnstore index.

In workloads involving rowstore tables, large insert operations may be batched into small groups of rows in order to reduce contention and decrease the transaction size for each insert. Columnstore indexes do not benefit from micro-batching. When migrating code from rowstore tables to columnstore tables, resources can be saved by adjusting data load processes to operate on significantly larger batches. Batches of 2²⁰ (1,048,576) rows are optimal, though a batch size greater than or equal to 102,400 will ensure that the delta store is not needed. If insert operations contain tens of millions of rows, then those inserts can be broken down into more manageable subunits to prevent a staging or temporary table from becoming too large.

While this guidance may make it seem as though the delta store should be avoided at all costs, the goals of an analytic data store should not be compromised for this purpose. Delaying reporting data to prevent delta store usage is not worthwhile and will not save enough resources to be meaningful. Actively avoiding the insert of repeated small batches will ensure that insert performance is quite good. If the delta store is needed for the tail end of a data load process, then there is no reason to avoid it.

Other Data Load Considerations

Vertipaq optimization, which can greatly improve the effectiveness of columnstore index compression, is not used when a nonclustered rowstore index is present on a clustered columnstore index. Over time, this will result in rowgroups that will consume more resources than they would otherwise.

For scenarios where nonclustered rowstore indexes are required to support analytic processes, consider the following options for managing performance on the columnstore index.

Columnstore Reorganize Operations with Each Data Load

When a columnstore index is read, its contents plus the delta store are read together to produce the necessary output. While the delta store cannot get excessively large, columnstore indexes with periodic data loads can improve read speeds slightly by forcefully compressing the contents of the delta store into the columnstore index. The query in Listing 8-6 examines the contents of the small columnstore index created earlier.

SELECT

       tables.name AS table_name,

       indexes.name AS index_name,

       partitions.partition_number,

       column_store_row_groups.row_group_id,

       column_store_row_groups.state_description,

       column_store_row_groups.total_rows,

       column_store_row_groups.size_in_bytes,

       column_store_row_groups.deleted_rows,

       internal_partitions.internal_object_type_desc,

       internal_partitions.rows,

       internal_partitions.data_compression_desc

FROM sys.column_store_row_groups

INNER JOIN sys.indexes

ON indexes.index_id = column_store_row_groups.index_id

AND indexes.object_id = column_store_row_groups.object_id

INNER JOIN sys.tables

ON tables.object_id = indexes.object_id

INNER JOIN sys.partitions

ON partitions.partition_number = column_store_row_groups.partition_number

AND partitions.index_id = indexes.index_id

AND partitions.object_id = tables.object_id

LEFT JOIN sys.internal_partitions

ON internal_partitions.object_id = tables.object_id

WHERE tables.name = 'Sale_CCI_Clean_Test_2'

ORDER BY indexes.index_id, column_store_row_groups.row_group_id;

Listing 8-6

Query to Return Details About the Structure of a Columnstore Index

The results are found in Figure 8-5.

Note that the entire contents of the columnstore index (102,399 rows) reside in the delta store. The delete bitmap exists as a default and is currently empty. If the operator wishes to move the contents of the delta store into columnstore rowgroups, this can be accomplished by the index maintenance command in Listing 8-7.

ALTER INDEX CCI_Sale_CCI_Clean_Test_2 ON Fact.Sale_CCI_Clean_Test_2 REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

Listing 8-7

Index Reorganize Command to Compress Delta Store

Once complete, the delta store would be compressed and ready to move into the columnstore index, as seen in Figure 8-6.

The results show that the delta rowgroup affected by the index maintenance is now in an intermediary state. A new object has been created and compressed with the contents of the delta rowgroup inserted into it. The previous delta rowgroup (an uncompressed heap) is left in a tombstone state for the tuple mover to remove at a later point in time. Note the significant size difference between an uncompressed delta rowgroup and a compressed rowgroup.

At this time, running the same ALTER INDEX statement as before will force the tuple mover to complete this cleanup. Alternatively, waiting for a short amount of time to pass will achieve the same results. After 5 minutes have passed, the contents of this columnstore index are as seen in Figure 8-7.

Once the tuple mover completes its cleanup process, all that remains is a single compressed columnstore rowgroup.

Performing maintenance like this is not necessary but can improve query read speeds against a columnstore index after a data load is completed. Index maintenance will be discussed in far more detail in Chapter 14, including its use as part of data loads and other columnstore processes .

Summary

When loading data into a columnstore index, bulk loading ensures the fastest possible load speeds while minimizing server resource consumption. Bulk loading large numbers of rows in fewer batches is far more efficient than using trickle or small batch inserts.

By focusing data load processing around maximizing the use of compressed columnstore rowgroups and minimizing delta store usage, overall columnstore index performance can be improved, both for the data load processes and analytics that use the newly loaded data.