Clustered Columstore and Nonclustered filtered indexes
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?