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.
Joey Dantoni
shared this idea
8 comments
-
Peter Guerndt
commented
Same problem here. We had to remove the non-clustered index because we would need to use index hints at too many places. For now we can live without it, but it would be nice to have a nc index for some of our queries.
Has anyone tested if the optimizer behaves differently in SQL Server 2019?
-
Michael Barrett
commented
Agreed. It seems to optimizer is not completely "balanced" wrt. the cost of the "key lookup" in the clustered columnstore index. It should favor the clustered columnstore index directly in many cases.
-
Michael Barrett
commented
Agreed. It seems to optimizer is not completely "balanced" wrt. the cost of the "key lookup" in the clustered columnstore index. It should favor the clustered columnstore index directly in many cases.
-
Reuben Anderson
commented
"Maybe that says more about Slow Query Execution overall?"
Huh?
Executing the same query without the b-tree index is dramatically faster. That means the query optimizer is making the wrong choice.
My scenario;
- clustered columnstore index for general, large batch queries
- nonclustered index covering a specific query- the nc index severely damages the performance of other queries.
The cost estimation is wrong. There's no reason I can see why it should necessarily pick the nc index if the query is so much quicker when the nc index isn't there at all.
-
Anonymous
commented
Maybe that says more about Slow Query Execution overall? What exatly are your suggestions?
https://theessayservice.org/ -
Reuben Anderson
commented
Phew. Not just me.
-
Angus
commented
You might want to actually talk about what hints you are using, rather than just suggesting it
-
Joey Dantoni
commented
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.