May 13, 2022 at 6:12 pm
Select * from tablea where comt_dim_id = 3947804 -- returns no rows
Select count(1) from tablea where comt_dim_id = 3947804 -- returns count = 32
Why? How? There are no current locks in the database. The column datatype = INT.
Selecting these 32 rows based on a different column value works and shows the comt_dim_id value of 3947804.
Select comt_dim_id, count(1) from tablea where comt_dim_id = 3947804 -- shows comt_dim_id of 3947804 and count of 32.
The count query that works shows Index Seek (nonclustered) on the PK index and then stream aggregate.
The select (*) query that does not work shows same index seek along with key lookup in the clustered columnstore index and then nested loops inner join.
Has anyone seen some kind of corruption cause this?
May 13, 2022 at 6:55 pm
Update: I ran columnstore reorganize and rebuild and the problem was resolved. Clearly it was corrupted clustered columnstore index. Scary stuff as it gave our customers incorrect results. A developer just happened to notice the problem during report development. The only thing unique about this columnstore is it gets significant number of updates each night as it contains historical accumulating fact data (it's a periodic accumulating fact) that gets pieces of it rebuilt. I'm wondering if this is a known issue that is resolved in later version of SqlServer given updatable clustered columnstores were brand new with 2016.
May 14, 2022 at 9:06 am
This was removed by the editor as SPAM
May 16, 2022 at 9:38 am
Great feedback !
Can you elaborate on the build number of SQL2016 you are using ? Which CU ?
Did you open a case at Microsoft?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 17, 2022 at 11:12 am
This was removed by the editor as SPAM
May 17, 2022 at 11:12 am
This was removed by the editor as SPAM
May 23, 2022 at 10:19 am
This was removed by the editor as SPAM
May 23, 2022 at 10:20 am
This was removed by the editor as SPAM
May 24, 2022 at 5:01 am
This was removed by the editor as SPAM
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply