sql server - Multiple composite index vs single non-clustered column store index

Question

We have a OLTP system and we have got a grid, which is containing close to 20 columns, coming from multiple tables. The grid loaded based on search parameters involving around 6 columns. The data is huge with 100M rows coming from background tables.

To improve the performance of the grid loading, we have created indexed view with single unique clustered index. We are currently want to see how we can improve the performance of search parameters: a,b,c,d,e,f

The search can be based on any of the combination: (a), (a,c),(d,e), (a,b,c) ... (a,b,c,d,e,f)

We are thinking of either going for one of the below options:

• Multiple composite indexes on indexed view with specific access patterns like (a,b), (b,d), (a,b,c) etc.
• Single non-clustered columnstore index on indexed view, which will be helpful to satisfy all different access patterns like (a,b), (b,d), (a,b,c) etc. with included column of 20 columns

Can you please suggest, which is better approach ?

UPDATE: Just read that, non-clustered column store index does not support include columns. Will try further and update the answer, either by comments, if question is closed or by answering it.

question from:https://stackoverflow.com/questions/65913023/multiple-composite-index-vs-single-non-clustered-column-store-index

Answer 1

Since there are so many combinations to consider, you would need a lot of indexes.

So, at the most, I would do single or two column indexes (depending how selective the first column is) for a few popular columns. Anything wider is a waste of time, because the index will be selective either way.

You most definitely can do INCLUDE columns on non-clustered indexes. In this instance, it's only worth it if there are just those columns brought back, otherwise key lookups will be necessary in any case.

You should also consider something like OPTION (RECOMPILE) on your query. See #BackToBasics: An Updated Kitchen Sink Example by Aaron Bertrand.