4

On a partitioned table with 17 billion rows, I'm getting a very high number of row_groups.
I've heard that I should try to get 1.048.576 rows in each rowgroup. How do I achieve that?

The trim_reason is DICTIONARY_SIZE, as reported by sys.dm_db_column_store_row_group_physical_stats.

I get on average 100.000 rows per rowgroup, when my batch job is inserting 1,048,576 rows per batch.

I'm fairly new to columnstore indexes, but the only reference I can find to DICTIONARY_SIZE is for string columns. My table only has bit, bigint, and numeric columns.

My table looks like this (column names obscured), and I know that a lot of the columns contain mostly NULLs. If there are values, they are likely to vary a lot (large standard deviation). It is vibration data.

CREATE TABLE fct.MeasureV2
(
MeasureV2ID bigint NOT NULL IDENTITY(1, 1),
DT1ModelID int NOT NULL CONSTRAINT DF_MeasureV2_DT1ModelID DEFAULT ((0)),
DT1TID int NOT NULL CONSTRAINT DF_MeasureV2_DT1TID DEFAULT ((0)),
TimeStampUTC datetime NOT NULL,
AChkSum bigint NULL,
OChkSum bigint NULL,
SChkSum bigint NULL,
ATp numeric (18, 10) NULL,
ATpAvg numeric (18, 10) NULL,
AWDAbsAvg numeric (18, 10) NULL,
G1TMF numeric (18, 10) NULL,
G2Ps_1TMF numeric (18, 10) NULL,
G2Ps_2TMF numeric (18, 10) NULL,
/* about 600 more numeric (18, 10) NULL columns */
WdSdv numeric (18, 10) NULL,
UpdatedDate datetime NOT NULL,
UpdatedID bigint NULL,
IsCorrected bit NOT NULL CONSTRAINT DF_MeasureV2_IsCorrected DEFAULT ((0)),
TRATransformJobID int NOT NULL
) ON PS_FctMeasure (TimeStampUTC)
GO
CREATE CLUSTERED COLUMNSTORE INDEX CCI_MeasureV2 ON fct.MeasureV2 ON PS_FctMeasure (TimeStampUTC)
GO

There are a lot of columns, but the alternative attribute-value table would contain >200 billion rows, and be almost impossible to query. This format is slow, but not too slow.
We've also tried SPARSE columns, but that took a lot of disk space.

0

3 Answers 3

8

I've heard that I should try to get 1,048,576 rows in each row_group.

That's an ideal, but it's not always achievable in practice. It's not often a primary factor you should concentrate on in any case.

...the only reference I can find to DICTIONARY_SIZE is for string columns

Dictionary pressure is easier to demo with long string columns, that's all.

Dictionary encoding is available for all data types. The alternative encoding, value-based, is only available for numeric types (in the broadest sense).

Value encoding works by scaling and rebasing numeric values. For example, the values {1100, 1200, 1300} can be stored as (0, 1, 2) by first scaling by a factor of 0.01 to give {11, 12, 13}, then rebasing from 11 to give {0, 1, 2}.

With dictionary encoding, each unique value is stored in a dictionary and assigned an integer id. The segment data then references id numbers in the dictionary instead of the original values.

When dictionaries are used, a primary dictionary stores values for the entire column. An additional secondary dictionary may be created for each segment of the column (i.e. per rowgroup).

A dictionary has a maximum in-memory size of 16MB. It is compressed when persistently stored. The size of rowgroup is automatically reduced during columnstore building if a dictionary becomes full.

Note though, the same secondary dictionary may be referenced by multiple row groups if the same thread was responsible for building them. If that dictionary hits its maximum size, all rowgroups referencing it will also be limited in size.

If sharing secondary dictionaries is causing your rowgroup size limitation, you can try rebuilding the columnstore index with global trace flag 11612 enabled:

DBCC TRACEON (11612, -1);

This trace flag prevents secondary dictionary sharing.

You can find the type of encoding used in sys.column_store_segments.

The type of dictionary and the data it is associated with can be found from sys.column_store_dictionaries.

With a fixed schema and data set, there's little you can do to affect the type of encoding and compression scheme SQL Server uses.

Even if you can change the schema or data set, it's hard to say what you should do, given the information provided and without knowing the types of query you run. It's possible your data isn't optimally suited to columnstore compression, but that doesn't mean it's not one of the better solutions. You might look into using a data lake for example. If you want design advice, I suggest you ask a new question about that, with as much relevant detail as you are able to share.

If your columnstore is already well-partitioned and performing well enough in practice, I wouldn't worry too much about the smaller rowgroups due to dictionary size limits.

Avoiding delta stores, achieving partition/rowgroup elimination at runtime (where possible), and aggregate pushdown is usually much more important.

1
  • The test server ran out of TX log, so I have only managed to rebuild one partition with 146 million rows. The number of row_groups went from 4545 to 463. Average rows up from 32332 to 317474 rows. Oct 5, 2022 at 12:16
4

I'm guessing not all 600+ columns are used in every query. Also I'm guessing some columns have many more distinct values than other columns and so fill up the dictionaries sooner. If you're lucky these high cardinality columns are seldom used.

So I'd suggest vertically partitioning the big table. Each new sub table will have the primary key column and some of the columns. The idea is to keep together the columns which will be used together commonly. There is an implicit one-to-one relationship between subtables should a join be necessary. Of course columns can be duplicated between subtables if that helps.

A rowgroup is smaller if any of its dictionaries hits the limit. So the rowgroup is small if col 1 has high cardinality or col 2 has high cardinality or col 3 ... or col 610 has high cardinality or col 611 has high cardinality. That's a lot of ways to fill a dictionary. If there are only, say, 20 columns your odds are so much better.

Of course some table has to have the high cardinality column(s). It will still have small rowgroups. But it will be no worse than the current situation.

Building these subtables requires data to move on disk. So it won't be fast with that many rows, and will require lots of spare capacity. Perhaps doing one partition at a time would work?

1
  • Yes, we've tried vertical partitionning, on other data tables, where we had more than 2000 columns. Fact to Fact joins do not perform well, so the most needed columns were copied to each table. Thank you for helping. Aug 30, 2022 at 9:00
3

It sounds like you're storing different types of data in your table (perhaps identified by the DT1ModelID column) and each type is going to have a mostly different set of columns that contain non-NULL values. Perhaps type 1 has 30 non-NULL columns, type 2 has a different set of 30 non-NULL columns, and so on. It also sounds like data isn't loaded into your table in real time. You mention that you have a batch job that inserts 1048576 rows at a time. Consider changing your insert scheme given that your batch job isn't having the desired outcome of getting compressed rowgroups of 1048576 rows.

I would try delaying the inserts as much as possible and loading data by type. Performing one insert per type or inserting data ordered by type are both methods that could work depending on your data and your requirements. This way of inserting data could result in higher quality compression.

You could also check which columns are causing the dictionary pressure to occur. If it's only a few columns, moving those columns to a different table or splitting up the NUMERIC(18,10) columns into INT/BIGINT paird may help resolve the dictionary pressure. NUMERIC is a pretty terrible data type for columnstore, but whether or not the tradeoff is worth it depends on your data and how you query it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.