Bert Swope
Clustered Columnstore Indexes store data on an intermediate storage location called deltastore, improving compression and performance by temporarily storing rows until a threshold is reached, then moving them into the columnstore segment.
The tuple-mover process also checks for closed row groups and compresses them. This background operation helps reduce fragmentation and optimize compression rates in a columnstore index.
Technology
Clustered Columnstore Indexes are column-based indexes that utilize column-oriented storage to accelerate queries and reduce I/O costs. They’re especially beneficial for data warehousing/analytics workloads and those utilizing compression.
This type of index stores data in a compressed row group, allowing rows to be moved between them for optimal performance. Furthermore, it reduces I/O requirements by enabling one single row to be read from multiple segments on disk.
Clustered columnstore indexes typically measure less than six million bytes, indicating the data has been compressed sufficiently to fit in memory. It should be noted, however, that column-based indexes offer better compression efficiency than row-based ones.
Columnstore index compression is 10 times better than a row-based index, saving up to ten times as much space as non-columnstore indexes. This is because data in columns tends to be more homogenous than in rows, making them easier to compress for storage.
Columnstore indexes can improve query performance by allowing the Database Engine to scan a large table with just one full table scan, helping avoid costly table seeks which are more expensive for database applications.
Performance can also be enhanced by eliminating unnecessary row groups, possibly using ordered column predicates. This feature is available starting with SQL Server 2022 (16.x).
A major advantage of using a columnstore index is that it can be accessed by all types of Transact-SQL queries, including SELECT, UPDATE, and DELETE statements and INSTEAD OF queries.
ETL jobs benefit from this, as it speeds up data processing in the Database Engine, leading to significant time savings when running an ETL job.
Create a clustered columnstore index on an existing table or add one to a new one using the CREATE COLUMNSTORE INDEX statement and specifying which filegroup to store it on. Alternatively, you could use the SET QUOTED_IDENTIFIER option to specify that the columnstore index should be created on the default filegroup.
Another method to create a clustered columnstore index is an index rebuild operation. This approach is preferred, as it eliminates the nonclustered index and creates a clustered columnstore index on all tables simultaneously.
If you need to create a clustered columnstore table with one column of an unsupported data type, consider leaving that column out of the nonclustered columnstore index. Doing this ensures all columns supported for clustered columnstore index are included within its own clustered index.
Which columns to include?
Columnstore Indexes are specialized indexes that utilize highly compressed data structures. This provides faster logical reads and can be utilized in many of the same queries that utilize traditional B-tree indices.
Finding the ideal location is the key to creating this type of index in your database. Generally, they’re ideal for large data warehouse workloads with many aggregating queries that will benefit from both compression and batch execution modes.
If you are creating a clustered columnstore index on a table, make sure all column data types are supported. Some examples include varchar(max), nvarchar(max), varbinary(max), ntext, text, image, uniqueidentifier, rowversion, and sql_variant; decimals with precision greater than 18 digits; datetimeoffset with scale greater than 2; CLR types including hierarchyid and spatial types as well as xml documents.
Consider compressing a column using the COLUMNSTORE_ARCHIVE option to reduce disk space requirements and storage time.
When creating your index, one important factor is the data size you wish to include. For instance, including many rows for one particular column may increase performance.
One challenge with this approach is finding an area in the table where extra columns can be added without overwhelming it with data. This issue becomes especially challenging when working with tables that contain billions of rows.
Here, the power of this new index can really shine. If your table contains a frequent filter, such as SaleDates that is frequently returned in a data warehouse, then a columnstore index can provide significant performance gains when querying it.
SQL Server 2022 now adds a filter predicate column to columnstore indexes, enabling you to selectively include certain data rows from your table into the index. The statistics generated by SQL Server for these rows can be quite impressive.
Additionally, your queries become much more flexible as you can now utilize different values in each table column instead of one value per row. This opens up many opportunities for reporting and analysis tasks in an OLTP environment with sparse data sets.
Advantages
Clustered Columnstore Index (CCI) is a column-oriented data storage solution designed to improve query performance for various data warehousing workloads. CCIs are commonly employed with large fact tables and dimension tables in large data warehouses, but can also be beneficial in other situations.
CCI indexes offer much improved query performance compared to traditional rowstore indexes due to their compressed data structure rather than sorted data, which drastically reduces I/O requirements and boosts buffer cache hit ratios. Furthermore, compression allows more data to be read from disk than feasible.
On a table with one million rows, CCI can significantly improve compression rates by up to ten times and provide two times better query performance than a rowstore index – especially when the table contains relatively low cardinality data.
CCIs boast a high compression rate that helps reduce I/O and memory requirements for queries and batch mode operations that simplify query processing. As such, CCIs can be an efficient solution for many kinds of inquiries – including analytical inquiries and transactional OLTP ones – due to their superior compression rates.
CCIs are more costly than standard rowstore indexes, but their high compression rates and reduced I/O expenses make them ideal for various scenarios. For instance, CCIs can be employed in HTAP (hybrid transactional analytical processing) scenarios when the DW system needs to support both transactional and operational business intelligence (BI).
Clustered columnstore indexes (CCIs) are an ideal choice for data warehousing, where high compression rates can improve query performance by reducing the size of each row group and avoiding I/O bottlenecks. For instance, when applied to a fact table within a data warehouse environment, CCI can offer up to ten times the performance of standard rowstore indexes when running analytics queries.
Another advantage of a CCI is that it can expedite queries that look for specific values or small ranges of values. Additionally, it helps expedite updates and deletions of specific rows – especially useful in data warehousing workloads where there are often many updates and deletions, as well as frequent table reorganizations to remove old, unused rows.
When selecting which columns to include in a clustered columnstore index, consider the type of data you plan to store and how frequently it will be accessed. For instance, selecting an ordered clustered columnstore index key for a string data table may benefit from segment elimination which helps improve query performance.
When a table has at least one million rows, and each partition contains at least one rowgroup, it is best to utilize a clustered columnstore index. If the table is too small to fill a rowgroup in each partition, compression, and improved query performance cannot be achieved; alternatively, use clustering for all tables instead.