Is columnstore indexes not a good fit for this for performance

GokhanVarol 2017-05-28 11:47:03

I am testing some of our queries with columnstore indexes. The tables already have primary keys and other indexes. I am testing this on SQL 2016 SP1 Developer edition. I added nonclustered columnstore indexes to all tables. I am testing the same query using Columnstore indexes versus using the old way not using the columnstore indexes. Using columnstore indexes runs about 3 times slower, is that mean the kind of workload we have is not a good fit for columnstore indexes?
Thank you in advance.

link text

SQLkiwi 2017-05-28 16:51:34
> Using columnstore indexes runs about 3 times slower, is that mean the kind of workload we have is not a good fit for columnstore indexes?

Not necessarily, but it does mean you'll need to do more than just slap a load of nonclustered columnstore indexes on your current schema to see benefits.

For whatever reason (impossible to say when anonymized) the plan you showed is using row mode processing for almost all operations, which eliminates most of the potential benefit of using columnstore indexes (in this case). The large number of row mode exchanges (with high row counts) are a sure sign that this plan will not go well.

Ideally, the plan would feature batch mode (mostly hash) processing almost exclusively, and only use row mode where that can't be avoided, and even then only on a relatively small number of rows.

I suggest you take a step by step approach. On a test system, replace all the existing indexing and partitioning with clustered columnstore. Take a representative query, and see how it performs, adding nonclustered b-tree indexes where justified. You may also find it useful and instructive to start with smaller queries with just a few major operations and joins, and work methodically from there.

Take some time to review the documentation on columnstore best practices before you begin. You may need to adjust some syntax in the source query to work well with columnstore.

SQLkiwi 2017-05-28 19:25:54
Only the columnstore scans are using batch mode, every other operator in the plan is row mode. Add Actual and Estimated Execution Mode to the plan tree view, or open the plan in SSMS. I'm not sure why Plan Diagram view doesn't show this information, I'll ask.
Aaron Bertrand 2017-05-31 00:03:52
I believe we currently only bother to show est/actual execution mode on the tooltip when we know the underlying has Storage="ColumnStore" and therefore batch mode is possible. I realize this might not be wholly intuitive, and it is not future-proof. I am thinking about an enhancement to just show "Execution Mode" on the tooltip when RelOp/EstimatedExecutionMode matches RunTimeCountersPerThread/ActualExecutionMode, and both when they differ. Does this seem reasonable? I think the concern was the wasted space on the tooltip when 99% of the plans were seeing always said Row/Row for both. But now that we've taken the query text off the tooltip, and more people are starting to use ColumnStore, and batch mode will likely expand to more operators, it might make sense to revisit.
GokhanVarol 2017-05-28 18:31:33
We have timestamp datatyped columns on most of the tables, few tables have varchar(max) columns, I cannot create Clustered Columnstore index on them 🙁