Value Encoding

While this example may seem extreme, it is not that unusual as dimensions often contain repeated values that fall into limited ranges.

Note that real-world transformations of data that occur in columnstore indexes are more complex than this, but the process to determine their details is essentially the same.

The original data is stored as DECIMAL(4,2), which consumes 5 bytes (40 bits) per value. By converting to integers prior to compression, multiple transformations are applied that reduce storage from 200 bits to 26 bits. Decimals may not seem the most likely candidates for effective compression, but once they are converted into integer values, they can take advantage of any of the transformations that can be applied to integers.

Dictionary Encoding

Since the segment contains five distinct values, the index ID for the dictionary will consume 3 bits. The resulting mapping in Figure 5-2 shows the resulting dictionary compression of this data.

The original data required 132 bytes in storage, whereas the encoded data required only 30 bits (3 bits per value), plus the space required to store the lookup values in the dictionary. The dictionary itself consumes space for each distinct value in the segment data, but as a bonus, a dictionary may be shared by multiple rowgroups. Therefore, a table with 100 rowgroups may reuse dictionaries for each column across all of those rowgroups.

The key to dictionary compression is cardinality. The less distinct values present in the column, the more efficiently the data will compress. A CHAR(100) column with 5 distinct values will compress significantly better than a CHAR(10) column with 1000 distinct values. The dictionary size can be roughly estimated as the product of distinct values and their initial size. Lower cardinality means that there are less string values to store in the dictionary and the index used to reference the dictionary will be smaller. Since the dictionary index IDs are repeated throughout rows in the table, a smaller size can greatly improve compression.

The results of this query are shown in Figure 5-3.

Note that if a table is partitioned or has many columns, the result set could become quite large, so this query is filtered by both table and column to focus on a single set of segments, for demonstration purposes. The results help to fill in the blanks for dictionary metadata, as seen in Figure 5-4.

This detail differentiates between local and global dictionaries, as well as adds in some column-level detail. Based on the results, the integer column [Bill To Customer Key] compresses quite well as it contains only three distinct values in its dictionary. Sixty-eight bytes for a dictionary is tiny and will provide immense savings for a column that was previously 4 bytes per row across 25 million rows. Each value is reduced from 4 bytes to 2 bits, allowing it to be compressed at a ratio of 16:1!

Numeric columns with high cardinality will typically use value compression to avoid this problem, whereas string columns that are long and/or have a high cardinality may not be able to avoid this issue. Sixteen megabytes may sound small when compared to massive OLAP tables, but dictionaries themselves are compressed and typical analytic data will not approach the dictionary size limit. Testing should always be used to confirm either of these scenarios, rather than assuming they will or will not happen.

Should String Data Be Normalized?

Before advancing to a discussion of the remainder of the compression process, a question that arises from the efficiency of dictionary compression is whether or not normalization of dimensions is necessary or useful for columnstore compressed data that uses dictionary compression.

In the world of transactional data, dimensions are often normalized. This saves space and memory and allows for relational integrity, uniqueness, and other constraints to be implemented at runtime. In analytic data, though, data validation is often implemented via validation, rather than the use of triggers, keys, or check constraints. As a result, normalization was often used more to save resources or provide convenient lookup tables for use by visualization or analytics applications.

When populated with data, the Dimension.Sale_Description table is loaded first with any new distinct Description values, and then Fact.Sale_CCI_Normalized is loaded with data that joins the dimension table to retrieve values for Description_Key. Figure 5-5 shows the side-by-side comparison of the storage of these tables.

The table on the left is the original table, whereas the table on the right has the Description column normalized into a SMALLINT. In this specific example, normalization allowed for a significant space savings, about 15:1 compression savings from the original table!

Generally speaking, normalization will help most when the number of distinct values is low and the length of the columns is high, as dictionary encoding for a SMALLINT column will produce superior compression than dictionary encoding for a large VARCHAR column.

The downside to normalization is that data load processes will take longer. Joining additional dimension columns at runtime to load data requires time and resources. The decision as to whether to normalize or not should be carefully considered and testing conducted to determine if sufficient gains are realized to justify the change.

Ultimately, business logic and organizational needs will drive this decision at a high level, whereas technical considerations will allow for further tuning as needed. There is no right or wrong answer to this question; therefore, consider either approach valid until hands-on testing proves otherwise.

Row Order (Vertipaq) Optimization

Because a columnstore index is organized by column, rather than by row, it is possible for SQL Server to rearrange the order of rows within each rowgroup at the time each segment is compressed. This optimization is used whenever possible and attempts to arrange rows such that like values are adjacent to each other.

Columnstore compression is highly sensitive to repeated sequences of values; therefore, this optimization can provide exceptional compression and performance improvements. Vertipaq optimization is the algorithm that seeks to rearrange rows in the order that results in the most effective compression of the underlying data. This algorithm is used in other Microsoft storage technologies, such as SQL Server Analysis Services, PowerPivot, and PowerBI.

Vertipaq optimization is an expensive process, and the more columns a columnstore index has, the longer it takes to find the optimal row order. Therefore, SQL Server will limit the search time to ensure that building the index doesn’t take an excessive amount of time. As a result, Vertipaq optimization will be somewhat less effective on wider tables. This fact should not affect database architecture decisions, but can be a useful troubleshooting step if a columnstore index on a wide table begins to experience suboptimal compression as more columns continue to be added.

This does not mean that nonclustered rowstore indexes should never be used on clustered columnstore indexes. Nor should memory-optimized columnstore indexes be avoided. Instead, the fact that Vertipaq compression will not operate on these structures should be an additional input into architectural decision-making process. If a nonclustered rowstore index is required to service a specific set of critical queries, then it should be used. If its use is optional, then consider the negative impact it may have on compression.

The column has_vertipaq_optimization in sys.dm_db_column_store_row_group_physical_stats indicates if that rowgroup was compressed using the Vertipaq optimization or not. In general, expect to see this column return a value of 1, unless the columnstore index is either on a memory-optimized table or is used in conjunction with nonclustered rowstore indexes. If has_vertipaq_optimization is unexpectedly zero, further research is warranted to understand why that is the case and determine what changes were made that prevented this optimization from being used. Figure 5-6 shows a sample of the results of the previous query.

In this scenario, Vertipaq optimization is in use for all rowgroups in this columnstore index.

Other Compression Algorithms

After segments have been encoded and rowgroups have been reordered, the final step in columnstore compression is to apply any remaining compression algorithms to the resulting data. At this point, it is possible that no further compression gains are possible for a given segment. This will be most often true with values that do not repeat. The details of how columnstore indexes compress data are provided here to assist in more advanced optimization of compression and performance, when possible.

One form of compression used by columnstore indexes is run-length encoding , which seeks to group together like values and a count, rather than listing repetitive values in a segment. Vertipaq optimization is a prerequisite to this step, and without it, run-length encoding will be significantly less effective.

Consider the example of foods provided earlier in Figure 5-2. The encoded data has five different values that have been dictionary encoded to 3-bit numeric values. Vertipaq optimization would take the resulting data set and order it, as shown in Figure 5-7.

Note that the encoded values are now reordered so that each value is grouped next to the same value. Run-length encoding groups together each like value logically to reduce the overall amount of storage required for the segment. Logically, the resulting structure can be illustrated as shown in Figure 5-8.

Each value has been provided with a count , shown in parenthesis after it. There is a single instance of the value 0, followed by two instances of 1, three instances of 2, three instances of 3, and one instance of 4. This algorithm thrives on data that is repetitive and works exceptionally well in conjunction with dictionary encoding and Vertipaq compression.

Bit array compression is used when a segment contains a small number of distinct values and the data cannot benefit significantly from Vertipaq compression and run-length encoding. A bit array is an array in which each distinct value is assigned a column and each row is mapped to that column using bits. Figure 5-9 illustrates how this compression would look when used on an unencoded and unsorted data set.

While this may seem like a complicated transformation, the resulting ones and zeros are quite compact and provide a structure that can be quickly decompressed.

There are a handful of other more complex compression algorithms that may be used to progressively shrink the size of each segment of data. The algorithms used can vary by segment and may be used often, sometimes, or not at all. When this process is complete, the final level of compression is applied by SQL Server, which utilized its xVelocity compression algorithm.

The result of this query is shown in Figure 5-10.

Clicking the Messages tab as shown in the results provides additional information on the reads and writes performed by the query, as seen in Figure 5-11.

There is quite a bit of information returned, but for the moment, the LOB logical reads circled in red provide an indication of the reads required to satisfy this query. Columnstore indexes will not report reads as logical reads , but instead as LOB logical reads, which indicates that their segments are stored as Large Objects and not as traditional SQL Server data structures. If this number of reads seems high, that is because it is! Later chapters in this book will provide additional optimizations that will reduce storage and memory footprint, as well as greatly improve query performance.

Columnstore Archive Compression

Columnstore archive compression uses the Microsoft Xpress compression algorithm. While this algorithm is complex, it is publicly documented and is not a Microsoft trade secret. This algorithm relies on compressing data with repeated byte sequences and combines the LZ77 and Huffman compression algorithms to further shrink the size of columnstore data. The details of these compression algorithms are too extensive for the pages of this book, but are publicly available to anyone interested.

The type of compression used in a columnstore index may be specified on a partition-by-partition basis, allowing older/less used data to effectively use archive compression, whereas newer data can use standard columnstore compression. Chapter 11 delves into how partitioning can be combined with columnstore indexes to improve data storage, maintenance, and performance.

Once populated with data, this table can be compared to the previous version that used standard columnstore compression, as seen in Figure 5-12.

Columnstore archive compression provided an immense space savings when compared to standard columnstore compression. This may seem absurdly impressive, but some of this savings can be achieved by optimizing data order, which will be discussed in detail in Chapter 10.

In practice with typical production data loads, archive compression will deliver an additional 15–30% savings over standard columnstore compression. For a large analytic table, this can equate to a significant reduction in its data footprint! In general, only consider archive compression for data that will not be written to or read often, as the cost to compress and decompress it is nontrivial. For data that is rarely accessed, it is an easy way to save on both storage and memory resources.

The Compression Life Cycle

A key to all compression within SQL Server is that compressed pages remain compressed until needed by a query. Therefore, pages are compressed on storage and remain compressed there for the lifetime of an index. When pages are read into memory, they remain compressed. Therefore, any storage space saved by the use of compression will equate into memory savings when the data is needed.

This fact is critical to the efficiency of columnstore indexes. Compressed segments remain compressed on disk as well as in memory and are not decompressed until a query requires their data. Any optimizations that further decrease the size of compressed segments will translate directly into memory savings. The remainder of this book will focus on optimizations, best practices, and features that allow columnstore indexes to be used as efficiently as possible, thus making the most of their compression and ensuring they can scale for any data, regardless of how big it gets.