The Cost of Modifying Data

In Chapter 5, columnstore compression was discussed in extensive detail. An important takeaway is that some forms of compression, such as run-length encoding, depend on the exact contents of the underlying data. Columnstore compression is exceptionally good at shrinking data by a considerable amount by applying a variety of encoding and compression algorithms and optimizations. Consider the sample data in Figure 9-1.

The sample data is encoded with dictionary compression, reordered with Vertipaq optimization, and further compressed with run-length encoding. If a process deletes the last two rows in the table, then it is necessary to decompress the data fully, delete the rows, and then recompress it. Figure 9-2 shows how this process would impact the data.

The resulting set of indexed ID groups contains only 4 rows, rather than 5 (as index ID 4 has been removed), and the count for the index ID 3 has been reduced.

If the table were larger and the deletion impacted more rows, then it is likely that many rowgroups would need to be decompressed, adjusted, and recompressed. In the process, many pages would need to be updated. This operation would quickly grow to be prohibitively expensive. A balance needs to be maintained between the speed of write operations and the speed of read operations, and in this scenario, the ability to load and modify data quickly needs to be prioritized.

Delete Operations

In a columnstore index, the cost to decompress rowgroups, delete rows, and recompress them is prohibitively high. The more rowgroups a delete operation targets, the greater this cost would become. To mitigate this cost, a structure called the delete bitmap is used to track deletions from the columnstore index.

The delete bitmap is a heap that references the rows within underlying rowgroups. When a row is deleted, the data within the rowgroup remains unchanged and the delete bitmap is updated with a reference to the deleted row. As such, deleted rows in columnstore indexes can be seen as soft deleted, where removed rows are flagged, but not physically deleted.

Note that delete operations against rows in the delta store do not need to use the delete bitmap as the delta store is a rowstore heap and rows can simply be deleted from it as needed, without the need for soft deletes.

When a query is executed against a rowgroup containing deleted rows, the delete bitmap is consulted and deleted rows are excluded from the results. The delete bitmap may be visualized as seen in Figure 9-3.

The underlying rows within the rowgroup are unchanged when a deletion occurs against them. Instead, the delete bitmap tracks which rows are deleted and is consulted when future queries are issued against this rowgroup. Because of this, deleting data in a columnstore index will not reclaim storage space.

The rowgroup metadata can be seen in Figure 9-4.

As is expected of a columnstore index that has had no delete operations executed against it, the deleted_rows column in sys.column_store_row_groups shows zero for each rowgroup. Executing the query in Listing 9-1 will delete a total of 15,400 rows from the columnstore index. Figure 9-5 shows the metadata details for the columnstore index after the deletion has completed.

Each rowgroup now contains some number of deleted rows, depending on the number of rows within them that happen to have been invoiced on 1/1/2016. Note that the total rows in each rowgroup and the size in bytes have not changed. This is expected and reflects the fact that rows were soft deleted via the delete bitmap.

Because rows were removed from all rowgroups in the index, a deletion without the aid of the delete bitmap would have required a rebuild of the entire index, which would be a prohibitively expensive operation to perform while a process waits for a single day’s worth of rows to be deleted. Instead, the delete of 15,400 rows completed exceptionally quickly, in under a second, thanks to the delete bitmap!

The only way to clean up deleted rows and free up the space consumed within rowgroups is to perform an index rebuild on the columnstore index or on any partitions impacted by the deletion. This is generally not needed unless the amount of deleted data becomes large with respect to the overall size of the index. Like with classic rowstore indexes, fragmentation is not problematic until there is too much of it, at which time a rebuild can resolve it. Note that index reorganize operations do not remove deleted rows from columnstore indexes! Chapter 14 dives into index maintenance in detail, discussing how and when it is needed, and best practices for its use.

Update Operations

This means that an update operation will need to write to both the delete bitmap and the delta store in order to complete successfully. It is also important to note that the insert operations that result from an update operation against a columnstore index will exclusively use the delta store and cannot take advantage of bulk load processes.

The results in Figure 9-6 show a clean columnstore index with no deleted rows or entries in the delta store.

Returning to the rowgroup metadata query in Listing 9-4, the results of the UPDATE statement can be reviewed, with a sample seen in Figure 9-7.

The metadata after the UPDATE was executed shows that every rowgroup in the columnstore index was impacted as 18,150 rows were deleted and 18,150 rows were then inserted. Sys.internal_partitions shows a delete bitmap and delta store object, each containing 18,150 total rows. The rowgroup detail illustrates how many rows were updated per rowgroup. In addition, the new rowgroup (number 25) shows the new open delta store that was created for the newly inserted rows.

The resulting structure underscores the fact that an UPDATE against a columnstore index executes as a combination of discrete delete and insert operations. Performing each of those operations sequentially would yield similar results.

When updating 148,170 rows, the first thing to note is that it took 5 full seconds to execute! The previous update of 18,150 rows completed almost instantly after being executed. Viewing the metadata reveals the reason for this, as seen in Figure 9-8.

The key takeaway from the metadata following the larger update is that the delta store contains all 148,170 rows updated in the operation. Normally, any INSERT operations of 102,400 rows or more will benefit from a minimally logged bulk insert process, but UPDATE operations cannot benefit from it. Because the UPDATE is composed of both an INSERT and DELETE in the same transaction, it is not possible to fork the single transaction into a fully logged DELETE and a minimally logged INSERT.

Bulk insert processes on columnstore indexes can save immense resources when used, but are not allowed to violate the ACID (Atomic, Consistent, Isolated, and Durable) properties of a SQL Server database. Attempting to splice together a fully logged and minimally logged transaction into a single larger transaction would require the creation of a transaction that would provide point-in-time restore capabilities for the DELETE, but not the INSERT. While it is possible to conceive of ways for SQL Server to architect its way around that limitation, doing so would be confusing to anyone using a columnstore index and could result in unexpected results when restoring databases containing columnstore indexes that are subject to frequent UPDATE operations.

The key takeaway is that an UPDATE against a columnstore index will be comprised of a DELETE operation and a fully logged INSERT into the delta store. As the number of rows updated increases, the performance of those operations will decrease dramatically. The delta store was built to handle small numbers of rows – either trickle loads or the residual rows from a larger data load process. It was not built for large volumes of rows at one time and as a matter of course will perform poorly for those applications.

The update of 1,159,180 rows took almost a minute to complete. Figure 9-9 shows the resulting columnstore metadata immediately after this large UPDATE operation completes.

Note that there are multiple delta stores present. The open delta store (number 26) will remain open to accept future inserted data. The closed delta store will be processed by the tuple mover asynchronously and be compressed into a permanent columnstore rowgroup. Figure 9-10 shows the metadata for this columnstore index after a minute has passed and the tuple mover has executed.

This is a significant amount of work for a single UPDATE statement, and it scales poorly with increasing row counts.

In general, updates should be limited to small row counts or avoided altogether. There is value in rethinking how code is written to refactor an UPDATE against a columnstore index into a set of deletes and inserts or to manage an update via intermediary processes that avoid it altogether. The performance of UPDATE operations on columnstore indexes will be unpredictable and a large enough row count will result in transactions large enough to create resource pressure on the SQL Server.

Chapter 15 (best practices) will discuss in greater detail how to avoid updates and a variety of tactics that can be used when migrating UPDATE code from rowstore indexes into columnstore indexes.