Optimize table using columnstore index?

Question

I am new to table performance tuning. I have a flat table with millions-billions of records, with no delete-update operations. Only insert and read operations.

Its structure is like this:

Column1	Column2	Column3	Column4	Column5	Column6	Column7
col1val1	col2val1	col3val1	col4val1	col5val1	col6val1	col7val1
col1val1	col2val2	col3val2	col4val2	col5val1	col6val2	col7val1
col1val2	col2val1	col3val2	col4val2	col5val2	col6val2	col7val3

Current index:
PRIMARY CLUSTERED ON (Column1, Column2) --> because uniqueness based on these 2 columns is important.

Queries will be any of these:

-- query 1
select <all columns> 
from dbo.this_table 
where column1 = 'col1val1'

-- query 2
select < all columns > 
from dbo.this_table 
where Column4= 'col4val1' 
and Column5='col5val2' 
and Column7='col7cval3'

-- query 3
select < all columns > 
from dbo.this_table 
where Column4= 'col4val1' 
and Column6='col6val2' 
and Column7='col7cval3'

-- query 4
select < all columns > 
from dbo.this_table 
where Column4= 'col4val1'

How can I optimize my table for column4, column5, column6, column7? (last 3 queries) Storage is not a issue in my case but speed to read data must be minimum, so I am thinking of columnstore.

Can someone suggest what can be the indexes and in what combination?

In total I have 23 columns, out of which columns 4 to 6 are actually 7 columns. All these 7 columns are varchar with repeating values. Expect maybe 0.01% to 5% of the table to result from each query.

Answer 1

What are the data types of column4, column5, column6, and column7 and in general how unique are the values of each of those columns? Columnstore indexes might be useful for your use case if there's a high level of potential compression within in each column because there isn't a high variability in the values of each column. Often tends to be the case with larger datasets such as your table, but it really depends, and your best bet is to test.

Additionally how wide is your table (how many columns does it have)? As this will be a factor in if it makes sense to use traditional rowstore indexes and INCLUDE the rest of the columns you'd be selecting against as opposed to columnstore indexes.

Finally, for each of your example queries, what percentage of the table's total number of rows would you estimate would be returned?

These are all factors to consider and affect how well different indexes perform. Regarding columnstore indexes, you may also find ColumnScore.com helpful in determining if your table meets the criteria for a columnstore index. The fact that you don't DELETE or UPDATE the data is a good start (though isn't a hard requirement either).

Since you're limited to only one columnstore index per table, I think it's worth trying to optimize for your slowest use case. Barring that you can try CREATE NONCLUSTERED COLUMNSTORE INDEX ix_TableName_ColumnNames ON TableName (Column4, Column5, Column6, Column7); as a general columnstore index, but without testing, it's hard to say how well this would meet each of your use cases.

Additionally, if you wanted to experiment with some additional rowstore indexes, then these would be my recommendation as a starting point (and depending on how many total columns your table has, possibly adding an INCLUDE to the additional columns of your table on these as well):

CREATE NONCLUSTERED INDEX ix_TableName_ColumnNames_2 ON TableName (Column4, Column5, Column7);
CREATE NONCLUSTERED INDEX ix_TableName_ColumnNames_3 ON TableName (Column4, Column6, Column7);

Answer 2

There are no such things as a seek in a columnstore index. There are only scans. The column order in the "key" you define for the index is totally irrelevant.

What SQL Server can do, though, is to eliminate rowgroups. When the columnstore index is built, SQL Server reads the data and builds the index 1 million rows at a time - this is called a rowgroup. SQL Server has metadata of the lowest and the highest value for each column in each such rowgroup.

Imagine a query having

WHERE col = 23

SQL Server will look at the meta-data to determine the lowest and highest value for that column, per rowgroup. If the column value in the WHERE clause cannot exist in the rowgroup, then the rowgroup doesn't have to be read. This is a rowgroup elimination.

You can try to make sure that the data is "aligned" in such a matter so that you get a nice rowgroup grouping for come column (lowest values in one rowgroup, next lowest values in another rowgroup, etc). This isn't done by the order for the column in the key, you have to be creative in what way the order the data happens to be read when the index is created.

So, based on above, you might get some nice rowgroup elimination for query 2, 3 and 4, all of them having column4 in the where clause. Assuming you got nice aligment for that column when you built the index. As for query 1, unless there is an order-correlation between col1 and col4, then you are likely in for a full scan of the columnstore index for that query.

The next thing to consider is whether the columnstore index covers your query (has all columns that the query refers to). If it does, you are done after above operation (scanning rowgroups to find the rows). If not, then you are in for lookups to fetch each row. If selectivity is low, then there will be lots of lookups and it might be more efficient to use some other strategy for the optimizer.

Above is a brief explanation of how things work. The rest is up to you, since we won't have your data and queries to play with.

Also, further inserts might in the end "degrade" the quality of the index (in this case the alignment for column4), and you would consider if it is feasible to schedule rebuilds of that columnstore index.