Key Lookup against Columnstore Index Causes Slow Query Execution
On tables with a clustered columnstore index and a b-tree index on the same table, some parameter sets get an execution plan with an index seek against the b-tree, and then key lookup (in row mode) against the columnstore. This is extremely expensive, and most of the queries against larger columnstores never complete. The remedy is to hint the columnstore index in the query. I can supply plans as needed, but apparently can't upload here.
Reuben Anderson commented
Phew. Not just me.
You might want to actually talk about what hints you are using, rather than just suggesting it