When bulk loading data into clustered columnstore indexes – either to populate them initially or during subsequent loads – an important design consideration is the resulting performance of the load.
But the way in which the table is queried after the load is also a factor to consider during the design stage. This is because the load strategy can have a direct effect on the ability of the clustered columnstore index to perform rowgroup elimination. I’ll demonstrate how this is so in this post.
I’m not going to explain the details of columnstore indexes since there are plenty of good introductions available (e.g. see here), but the high level structural view is that a columnstore index consists of 1 or more rowgroups – each ideally containing 1,048,576 rows – and where each rowgroup contains a segment for each column that’s represented in it.
Each segment contains metadata about the values contained within its column portion, namely the minimum and maximum data values for that column. When a selective query is executed on the clustered index, each rowgroup is considered in turn, where the segment min/max metadata for each column referenced in the WHERE clause is compared with the column values in the query that need to be satisfied (e.g. a particular date range or a particular store ID). If the metadata shows that no rows in the current rowgroup can match the query predicate then the rowgroup can be skipped in its entirety – no rows need to be accessed. This is rowgroup elimination, and it can result in considerable performance improvements.
The segment metadata can be viewed via the sys.column_store_segments catalog view. So using this view to look at column 3 (StoreKey) of my 8 million row FactInventory table (from Microsoft’s ContosoRetailDW database):
select segment_id, object_name(p.object_id) as table_name, s.column_id, s.min_data_id, s.max_data_id
from sys.column_store_segments s, sys.partitions p
where p.object_id = object_id(‘FactInventory’)
and column_id = 3
and p.hobt_id = s.hobt_id
order by segment_id
The column headers here are slightly confusing because what Microsoft originally termed segments are now called rowgroups, so the first column here shows that we have 8 rowgroups and where the min/max data for the column 3 (StoreKey) segment in each of them spans the entire StoreKey range. This provides for no possibility of rowgroup elimination on selective StoreKey queries. E.g. if data for StoreKey 120 is being retrieved, no rowgroups can be eliminated because the min to max range for the column 3 segment in every rowgroup includes this target value.
Rowgroup elimination during columnstore queries can be detected using the column_store_segment_eliminate XEvent. This XEvent is not available via the SSMS Extended Events creation GUI, but it can be set via T-SQL:
create event session SegmentEliminationSession on server
add event sqlserver.column_store_segment_eliminate
(action (sqlserver.database_id, sqlserver.session_id,sqlserver.sql_text, sqlserver.plan_handle))
add target package0.asynchronous_file_target
(set filename = ‘C:\XEvents Logs\segelim.xel’, metadatafile = ‘C:\XEvents Logs\segelim.xem’)
Its output can be parsed using:
select CONVERT(xml, event_data).value(‘(event[@name=”column_store_segment_eliminate”]/@timestamp)’,’datetime’) as Execution_Time,
CONVERT(xml, event_data).value(‘(event[@name=”column_store_segment_eliminate”]/data[@name=”rowgroup_id”]/value)’,’int’) as RowGroupID_Elim
from sys.fn_xe_file_target_read_file(‘C:\XEvents Logs\segelim*.*’, ‘C:\XEvents Logs\segelim.xem’, null, null)
order by execution_time
Given the segment metadata shown previously, if we execute:
select * from FactInventory where StoreKey = 120
we get no segment elimination XEvents and we see the following statistics:
Table ‘FactInventory’. Scan count 4, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 122058, lob physical reads 56, lob read-ahead reads 60070.
SQL Server Execution Times:
CPU time = 1062 ms, elapsed time = 3594 ms.
In the situation where a particular selective query access pattern is common (e.g. queries by StoreKey), performance for these columnstore queries can be improved by changing the table load order. For the above example I’m going to drop the clustered columnstore index and build a B-tree clustered index on the table instead, based upon the StoreKey column:
create clustered index ix_cci on dbo.FactInventory(StoreKey)
This gives the table a physical ordering on that column. Then we can rebuild the table as a clustered columnstore index:
create clustered columnstore index ix_cci on dbo.FactInventory
with ( drop_existing = on, maxdop = 1 )
The maxdop = 1 option is recommended for this rebuild on a Microsoft blog here and also by Niko Neugebauer in his SQLBits session on ETL Patterns with Clustered Columnstore Indexes. Without it the original ordering may be lost.
If we then rerun the sys.column_store_segments query we get:
This is excellent news for StoreKey queries! If the selective StoreKey = 120 query from above is run on this, the columnstore query can eliminate all rowgroups except rowgroup 2 from consideration, since only rowgroup 2 includes 120 within its min/max range.
Rerunning that query (after emptying the buffer pool) the stats are now:
Table ‘FactInventory’. Scan count 4, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 29048, lob physical reads 18, lob read-ahead reads 8937.
SQL Server Execution Times:
CPU time = 393 ms, elapsed time = 1745 ms.
where the time is almost halved compared to the previous run, and where the number of physical and logical LOB reads also decreased substantially (columnstore segments are implemented as LOBs).
Running the XEvents SELECT query from above on the XEvents event file (and deleting duplicate rows) we see:
So indeed all rowgroups were eliminated except rowgroup 2.
In this example I’ve just looked at the initial table load – subsequent incremental loads would benefit from incoming data being sorted by StoreKey also, so that loads that span more than one new rowgroup can position their new rowgroups as beneficially as possible for rowgroup eliminatinon.
For the purposes of simplicity the example above used a non-partitioned table, whereas in practice partitions are likely to be used for any clustered columnstore index of significant size. Partitioning adds another level of elimination, and where it would be possible to partition by Quarter (for example) and then still have the StoreKey physical ordering within each partition.
For detailed specifics on incremental loads into partitioned clustered columnstore indexes, see the “Data Loading Scenarios: Partitioned table” section of this SQLCAT article.
The above shows that incurring some penalty at load time – sorting the incoming data – has a noticeable effect on queries that are selective on the sort column, as a result of columnstore rowgroup elimination.