Calculating min/max values of a columnstore table with sys.column_store_segments

Question

I need to fetch the max and min values of an arbitrary column on a table. The table has a clustered columnstore-index, billions of rows, and hundreds of columns.

I'd like to leverage that fact that every columnstore segment already has its min and max value recorded in the sys.column_store_segments table's min_data_id and max_data_id columns.

It shouldn't be necessary to access the actual table, as the columnstore segment metadata has sufficient information. But it appears that the min_data_id and max_data_id columns cannot be trusted to contain the max and min values because they may also contain references into a dictionary instead.

Is there any way that I can determine whether min_data_id and max_data_id are real values vs dictionary references? If they are references, is it possible for me to access the underlying dictionary?

When I try using MIN and MAX, the queries can take minutes to complete, making me think that metadata table is not being used.

References:

https://learn.microsoft.com/en-us/archive/technet-wiki/5651.understanding-segment-elimination

https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-column-store-segments-transact-sql?view=sql-server-ver16

Answer 1

Even SQL Server doesn't use min_data_id or max_data_id directly when processing MIN or MAX aggregates, even when aggregate pushdown (SQL Server 2016 or later, Enterprise Edition or equivalent) is employed. Every row in qualifying buckets is processed, albeit using an efficient SIMD implementation. To illustrate, the following stack trace was obtained on SQL Server 2022:

This doesn't necessarily mean the metadata is unreliable, though it could be if the deleted bitmap is non-empty since the min or max row might be logically deleted. There is also the question of which rows are visible to the current transaction given the current isolation level. Whatever the exact reasons, the fact remains that SQL Server itself does not employ this potential optimisation.

You could decide that approximate results are good enough for your needs. Perhaps you know delta stores and the deleted bitmap will always be empty. You'll still need to decode the bigint values of min_data_id and max_data_id to the target data type when value-based encoding is used.

When hash-based encoding is used with a dictionary, you're simply out of luck. There's no supported way to access the dictionary from T-SQL, much less a way to decode a particular value to a correctly typed dictionary entry.

In your position, I would be more concerned with getting the best possible supported performance for your MIN and MAX aggregates. Aggregate pushdown is a must, but that does depend on the data types in use and your version and edition of SQL Server, among other factors.

You would start by looking for the Actual Number of Locally Aggregated Rows on the columnstore scan in a post-execution ('actual') execution plan. Beyond that, you would look into the amount of physical I/O involved and sizing of the columnstore memory pool, along with CPU utilisation. Under ideal conditions, SQL Server is capable of extremely good aggregate performance on columnstore data.