Hello DBA friends,

I had the most bizarre experience on a SQL server 2016 (sp2). On the cumulative hotfixes after, the closest one from the error is this one : https://support.microsoft.com/en-us/help/4316858

It is still not quite the error that I got though.

With a client, I have implemented a clustered columstore index on a table with the default compression and one nonclustered b-tree index. All have worked very well. I noticed a performance improvement with an additional nonclustered filtered index on the table and SQL server does build the index very well. For a few hours, all was working very well and nothing to complain about and then suddenly, my transform process (it is on a fact table) started to fail with this error and this error would not go away:

Msg 407, Level 16, State 1, Procedure GetCustomerSf, Line 843 [Batch Start Line 6]

Internal error. The string routine in file d:\b\s3\sources\sql\ntdbms\common\shared\dstream.cpp, line 686 failed with HRESULT 0x8007007a.

Msg 8646, Level 21, State 1, Procedure GetCustomerSf, Line 843 [Batch Start Line 6]

Unable to find index entry in index ID 123, of table 1648750970, in database 'Dwh'. The indicated index is corrupt or there is a problem with the current update plan. Run DBCC CHECKDB or DBCC CHECKTABLE. If the problem persists, contact product support.

Msg 596, Level 21, State 1, Line 6

Cannot continue the execution because the session is in the kill state.

Msg 0, Level 20, State 0, Line 6

A severe error occurred on the current command. The results, if any, should be discarded.

I then checked the integrity of all the indexes and nothing was corrupted. I rebuild all the indexes and all went back to normal.... well , only for a few hours...

I was stunned. I checked hotfixes and nothing was on the Microsoft radar.

Then I decided to change my filtered index to a regular index and all went back correctly.

Conclusion: Microsoft needs to release a bug fix for such occasion. A filtered index on a clustered columstore table will make DML operations fail after a few hours until you rebuild the indexes.

Have you encountered this issue?