locked
SQL SERVER 2016 (SP2-CUI) BUG? MERGE and INSERT with COLUMNSTORE index creates crash dump - script supplied RRS feed

  • Question

  • Hi

    I have managed to create a script that (on my system) reliably generates the following message

    Location: pageref.cpp:955
    Expression: IS_OFF (BUF_MINLOGGED, m_buf->bstat) || pageModifyType != PageModifyType_Contents || GetPagePtr ()->IsTextPage ()
    SPID: 59
    Process ID: 5080
    Msg 3624, Level 20, State 1, Line 45
    A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a Hotfix from Technical Support.
    Msg 596, Level 21, State 1, Line 32
    Cannot continue the execution because the session is in the kill state.
    Msg 0, Level 20, State 0, Line 32
    A severe error occurred on the current command. The results, if any, should be discarded.

    select @@version results
    Microsoft SQL Server 2016 (SP2-CU1) (KB4135048) - 13.0.5149.0 (X64) May 19 2018 09:41:57 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: ) (Hypervisor)

    So I am pretty sure I am running a fully patched SQL 2016

    think this is closely related to item 3205976 but this should be fixed on the version I am running

    If there is anyone else out there running this, could you please try the following and let me know what happens!

    (You may need to tune (up) the number of values going into tables ONE and TWO)

    SELECT @@VERSION
    
    IF OBJECT_ID('dbo.ONE') IS NOT NULL
    BEGIN
        DROP TABLE dbo.ONE
    END
    
    IF OBJECT_ID('dbo.TWO') IS NOT NULL
    BEGIN
        DROP TABLE dbo.TWO
    END
    GO
    
    CREATE TABLE dbo.ONE (
    	id integer IDENTITY(1,1)
    	, wibble varchar(50)
    )
    
    CREATE TABLE dbo.TWO (
    	id integer
    	, wibble varchar(50)
    	, isactive bit
    )
    GO
    
    CREATE CLUSTERED COLUMNSTORE INDEX IX_CS_ONE ON ONE
    CREATE CLUSTERED COLUMNSTORE INDEX IX_CS_TWO ON TWO
    GO
    
    INSERT INTO ONE VALUES ('ONE')
    INSERT INTO TWO (ID, WIBBLE, isactive) VALUES (1, 'TWO', 1)
    GO
    
    INSERT INTO ONE (WIBBLE)
    SELECT 'TESTING'
    FROM SYS.OBJECTS AS O1
    CROSS JOIN SYS.OBJECTS AS O2
    
    INSERT INTO TWO (ID, WIBBLE, ISACTIVE)
    SELECT 2, 'TESTING', 1
    FROM SYS.OBJECTS AS O1
    CROSS JOIN SYS.OBJECTS AS O2
    
    
    INSERT INTO TWO (ID, WIBBLE, isactive)
    SELECT ID, WIBBLE, 1
    FROM (
    	MERGE TWO WITH (TABLOCK) AS TARGET
    	USING ONE AS SOURCE
    	ON TARGET.id = SOURCE.id
    	WHEN MATCHED
    	THEN UPDATE SET isactive = 0
    	OUTPUT
    				$action AS ACTION
    				, [SOURCE].*
    	) AS MERGE_OUTPUT
    ;
    
    
    -- it goes bang before here!
    SELECT * FROM TWO


    Tuesday, July 10, 2018 7:53 AM

Answers

  • I am able to reproduce this on SQL 2017 CU8, which I believe is the latest CU for SQL 2017.

    If this is a blocking issue, you should open a case with Microsoft.

    If you have a workaround, and only want Microsoft to know, you can file a bug on https://feedback.azure.com/forums/908035-sql-server


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Proposed as answer by Xi Jin Wednesday, July 11, 2018 6:07 AM
    • Marked as answer by uberb1oke Wednesday, July 11, 2018 9:53 AM
    Tuesday, July 10, 2018 9:36 PM
  • THANKS FOR THAT

    There is a work around which is to drop and recreate the columnstore index before and after the merge, not ideal but it'll have to do for now.

    • Proposed as answer by Xi Jin Wednesday, July 11, 2018 9:45 AM
    • Marked as answer by uberb1oke Wednesday, July 11, 2018 9:53 AM
    Wednesday, July 11, 2018 7:51 AM

All replies

  • I am able to reproduce this on SQL 2017 CU8, which I believe is the latest CU for SQL 2017.

    If this is a blocking issue, you should open a case with Microsoft.

    If you have a workaround, and only want Microsoft to know, you can file a bug on https://feedback.azure.com/forums/908035-sql-server


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Proposed as answer by Xi Jin Wednesday, July 11, 2018 6:07 AM
    • Marked as answer by uberb1oke Wednesday, July 11, 2018 9:53 AM
    Tuesday, July 10, 2018 9:36 PM
  • THANKS FOR THAT

    There is a work around which is to drop and recreate the columnstore index before and after the merge, not ideal but it'll have to do for now.

    • Proposed as answer by Xi Jin Wednesday, July 11, 2018 9:45 AM
    • Marked as answer by uberb1oke Wednesday, July 11, 2018 9:53 AM
    Wednesday, July 11, 2018 7:51 AM
  • Oh, and I logged this with Microsoft yesterday

    https://feedback.azure.com/forums/908035-sql-server/suggestions/34791778-sql-server-2016-sp2-cui-bug-merge-and-insert-wi

    If anyone wants to up vote it

    Wednesday, July 11, 2018 7:54 AM
  • Hi,

    I also made a test in my SQL Server 2016 SP2 CU1. When I use a small database which sys.objects returns 93 rows, I didn't encounter this issue. However with a big one which sys.objects returns 639 rows, I can repro your issue. Maybe there exists some records number limitations of Merge and Insert with COLUMNSTORE index. Just one thought.

    By the way, thanks for sharing the workaround and submitting a feedback. 

    And please kindly mark your workaround as answer. It'll benefit others with relevant issue. 

    Thanks,
    Xi Jin.


    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.


    • Edited by Xi Jin Wednesday, July 11, 2018 10:07 AM
    Wednesday, July 11, 2018 9:52 AM