1

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.

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);
2
  • 1
    Thank you for your efforts and answer. So total there are 23 columns, out of those these column4 to 6 are actually 7 columns. All these 7 columns are varchar with repeating values. About the percentage of rows return after queries, I cannot estimate, maybe 0.01-5%. – 9gagger07 Jun 5 at 0:22
  • @9gagger07 Hmm yeah, 23 columns isn't terrible but hard for me to say which way will benefit you most, without testing. 23 is likely too wide for an INCLUDE in general, and you may be better off with the key lookups that'll come from not including them, if you go with the rowstore indexes I recommended. In any case, please update your post with that information so other contributors see that information and may be able to offer even better advice than I provided. But at the end of the day, testing is your best way to find out what works for your use cases. :) – J.D. Jun 5 at 0:25
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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