none
Non clustered column store index major bug in SQL server 2016 SP1 Enterprise Edition RRS feed

  • Question

  • Our stage table holds 130 + columns with 1 billion 430 million rows with partition with service date. we created non clustered column store index, it took 36 hours for its completion. after creating the index the select count_big(col_name) from table shows 40 billion as row count, we tested this by sp_space used, sys.indexex table, right click and properties the table all shows the 1 billion 430 million rows. Before index creation also we count the table after 45 minutes execution it says the 1 billion 430 million. estimated number of rows in non clustered column store also shows 1 billion 430 million rows, but count shows very strange result. 

    Below are the execution plan details

    Column store index scan (Non clustered)
    scan columnstore index, entirely or only a range.

    Physical operation : columnstore index scan
    logical operation : index scan
    Estimated execution mode : Batch
    Storage : ColumnStore
    Estimated I/O Cost:0.05625
    Estimated Operator Cost : 8.74159(41%)
    Estimated SubtreeCost : 8.74159
    Estimated CPU Cost:8.68534
    Estimated Number Of Executions:1
    Estimated Number Of Rows: 1262930000
    Estimated Row Size: 27B
    Partitioned : True
    Ordered: False
    Node ID : 5

    In the GUI it is displayed like this

    select cost 0% <———Parallelism(Gather Streams) Cost 0% <——-Compute Scalar Cost : 0% <—— Hash Match Aggregate Cost(59%) <——- Columnstore index scan cost 55%.


    Tuesday, November 12, 2019 3:50 PM

All replies

  • Apply last CU and try again.

    Tuesday, November 12, 2019 5:09 PM
  • Hi Kannan,

    You can get the SP and CU download link of SQL server 2016 from here.

    By the way, we can submit SQL bug to the Microsoft feedback at this link https://feedback.azure.com/forums/908035-sql-server. This site will serve as a connecting point between you and Microsoft, and ultimately the large community for you and Microsoft to interact with.  Hope this could help you.

    Best regards,
    Cathy 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com



    Wednesday, November 13, 2019 2:15 AM
  • Dear All,

    Thanks for sharing the details, I will try to install the latest CU and run the count against the table.

    Regards,

     Kannan.C

    Wednesday, November 13, 2019 6:25 AM
  • Be careful! In SQL 2016 there was also a bug in the nonclustered columnstore index (on a rowstore table) that caused it to create deadlocks because it improperly locked partitions instead of rows … or something like that. This was in 2017, and it was already fixed in SQL 2017. I suppose they might have fixed it in 2016 in the later CU, but if it fixes your rowcount, if you then still see strange deadlocks, that might be why.

    Josh

    Wednesday, November 13, 2019 8:23 AM
  • Dear Josh,

    Thanks for the response. I am requesting our infra team to get the latest service packs and CU's for the production server upgrade. We have some process to apply the patches it may take sometime. I am very much disappointed about such behavior of SQL server in large volume table, in correct result may impact business as well, and our team will raise the support ticket, Microsoft should fix such errors otherwise we will not consider the database as a stable product. Those are not seen in oracle..

    Friday, November 15, 2019 10:41 AM