Optimizer Not using Non Clustered Column Store Index for Update / Delete Statements

Question

My Table tbl_FieldeData_ColStore Data Example:

/NonClustred Column Store Index on Table/

CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI ON tbl_FieldeData_ColStore ( PDID,FieldID )

/Below Select was using Non CLustered Column Store Index/

SELECT * FROM tbl_FieldeData_ColStore WHERE PDID = 46222 AND FieldID = 102582

Plan for Select Statement

https://www.brentozar.com/pastetheplan/?id=HJKsB0MT_

/ Not Using Above Created NonClustered ColumnStore Index, when i run below Update / Delete statement /

UPDATE tbl_FieldeData_ColStore SET InActive = 0 WHERE PDID = 46222 AND FieldID = 102582

DELETE tbl_FieldeData_ColStore WHERE PDID = 46222 AND FieldID = 102582

Plan for Update Statement:

https://www.brentozar.com/pastetheplan/?id=rkFADT-pu

Plan for Delete Statement:

https://www.brentozar.com/pastetheplan/?id=S1DCcpbpO

May I know Why This Happening With Non Clustered Column Store Index?

Is this way Non Clustered Column store Index Works?

Please Help me, Want to know How Non Clustered Column Store Index Works For Update / Delete Statements.

Answer 1

Based on the execution plans you provided, it appears that your table doesn't have a clustered index of any sorts, as noticeable by the RID Lookup operation. As a result, for the SELECT query, my guess is the SQL Engine is thinking using any available index to scan in conjunction with a RID Lookup would be the most performant operation for locating the data.

Conversely, for your data manipulation queries (the UPDATE and DELETE statements) the SQL Engine believes just a single Table Scan operation would be the better choice for the most efficient execution plan.

Why the SQL Engine made these choices is difficult to say based on the limited information we have to go off of, as it's based on your data, the statistics for that data, how your table is architected (e.g. what other indexes exist), and how the SQL Engine was programmed. Again, the only interesting thing I noticed is your table lacks a clustered index. Adding one (especially on the fields in your predicates, PDID and FieldID) might yield you different results.

Also, as mentioned in the comments, a columnstore index might not be the best choice for your use cases. Usually they're helpful for running OLAP type queries against large amounts of data, which doesn't seem to be your use case. You may want to consider completely switching from a nonclustered columnstore index to a regular clustered rowstore index.