locked
Timeout when executing MERGE-statement in SQL Server 2016 in simple recovery model RRS feed

  • Question

  • After migrating from SQL Server 2014 to SQL Server 2016, we have been experiencing query timeouts using MERGE-statements on some datasets. 

    Tested versions:
    WORKING: SQL Server 2014 Enterprise (64-bit) (12.0.5532.0)
    TIMEOUTS: SQL Server 2016 Enterprise (64-bit) (13.0.4001.0) and SQL Server 2016 Enterprise (64-bit) (13.0.1601.5)

    Code to reproduce the timeout error: http://pastebin.com/DXZJzyJv

    • Moved by Eric__Zhang Thursday, December 29, 2016 7:11 AM move to a proper forum for better result
    Wednesday, December 28, 2016 1:12 PM

All replies

  • Hi Pathed1969,

    I have made a test on my SQL Server 2016 (Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64)   Enterprise Evaluation Edition (64-bit) on Windows 10 Enterprise 6.3 <X64> (Build 14393: )) and it happen error after 5 mins with following error message.

    Time-out occurred while waiting for buffer latch type 4 for page (1:464), database ID 13.

    But, when I run the code in SQL Server 2014 and SQL Server 2008 R2, the code runs normally and it could return result quickly.

    I have tested following code in SQL Server 2016 and it could run quickly.

    declare @tmp table (action varchar(20), [RK] int, [Date1] date, [Date2] date, [FK] int, [Char1] varchar(32), [DateTime1] datetime, [HashedBK] varbinary(64), [Bit] bit)
    
    MERGE INTO dbo.TargetTable AS target 
    USING (
    		SELECT [RK], [Date1], [Date2], [FK], [Char1], [DateTime1], [HashedBK] 
    		FROM TEST.dbo.SourceTable
    	 ) AS source ([RK], [Date1], [Date2], [FK], [Char1], [DateTime1], [HashedBK])
    ON (target.[HashedBK] = source.[HashedBK] and target.[Bit] = 1) 
    WHEN MATCHED AND EXISTS 
    (
    	select source.[Date1], source.[Date2], source.[FK] 
    	except 
    	select target.[Date1], target.[Date2], target.[FK]
    ) 
    THEN UPDATE SET [DateTime2] = dateadd(ss, -1, source.[DateTime1]), [Bit]=0 
    WHEN NOT MATCHED THEN 
    	INSERT ([RK],[Date1],[Date2],[FK],[Char1],[DateTime1],[HashedBK],[Bit]) 
    	VALUES (source.[RK], source.[Date1], source.[Date2], source.[FK], source.[Char1], source.[DateTime1], source.[HashedBK], 1)   
    OUTPUT $action, source.[RK], source.[Date1], source.[Date2], source.[FK], source.[Char1], source.[DateTime1], source.[HashedBK], 1 
    into @tmp;
    
    INSERT INTO dbo.TargetTable ([RK], [Date1], [Date2], [FK], [Char1], [DateTime1], [Bit], [HashedBK])   
    SELECT [RK], [Date1], [Date2], [FK], [Char1], [DateTime1], 1, [HashedBK] 
    FROM @tmp WHERE action = 'UPDATE';

    Best Regards,

    Albert Zhang


    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.

    Thursday, December 29, 2016 10:41 AM
  • Excellent work! A very good repro!

    Please submit a bug on http://connect.microsoft.com/SqlServer/Feedback
    unless you already have.

    Often when I see bugs on Connect, I realise that there is little chance that Microsoft will be able to address the issue, because there is too little information to reproduce the problem, and without this, they can at best chase ghosts.

    But when they get a repro like yours, they should be able to determine the cause for the error quickly. Then it may prove to be more or less difficult to fix, but that's another story.

    Oh, I should point out that if this is a blocking issue for you, and Albert's suggestion does not work for you, you need to open a case with Microsoft. Things submitted at Connect are fixed as time and priorities permit.

    Thursday, December 29, 2016 11:48 AM
  • Hi Albert,

    This is a good workaround. For us it's not an option to use full recovery model as it would be the easiest way.

    I'll change the tablevariable to a tempdb-table just because it would be easier to generate the code. Yes it gone take some extra time in processing and hit the tempdb more but i can then use a select into approach as:

    Select Cast('' as varchar(20)) as Action, [RK], [Date1], [Date2], [FK], [Char1], [DateTime1], 1, [HashedBK]  into #temp from TargetTable where 1=0

    Best Regards

    Johan Öhman

    Tuesday, January 3, 2017 3:07 PM
  • Thank's Erland,

    We tried to make it easier for Microsoft to solve the problem as it's a crucial part of our solution. We filed a bug to Microsoft already (https://connect.microsoft.com/SQLServer/feedback/details/3117548/timeout-when-executing-merge-statement-in-sql-server-2016-in-simple-recovery-model).

    Hope they make a quick fix :-)

    Best regards

    Johan öhman

    Tuesday, January 3, 2017 3:12 PM
  • >We filed a bug to Microsoft already

    I don't see any attachments there.

    David


    Microsoft Technology Center - Dallas

    My Blog

    Tuesday, January 3, 2017 3:16 PM
  • I worked through your issue and found the following:

    1. The merge with just the update works fine.
    2. The merge with just the insert works fine.
    3. The merge with both insert and update do not.
    4. The query is waiting on a page latch for which the session already has an X lock.
    5. Using OPTION (FAST 1) seems to resolve the issue after a simple index is added. Cardinality issue with Merge? This may also explain why deleting some rows allows it work work.

    Steps to work around:

    1. Don't use a MERGE use traditional TSQL instead.

    OR

    1. OPTION (FAST 1) at the end of your statement
    2. Add a clustered index on HASHBK for the SourceTable table

    CREATE CLUSTERED INDEX [ix_cl_hashbk] ON [dbo].[SourceTable] ([HashedBK] ASC)

    CREATE NONCLUSTERED INDEX [ix_ncl_date1] ON [dbo].[SourceTable] ([Date1] ASC, [Date2] ASC, [FK] ASC)


    INSERT INTO dbo.TargetTable ([RK], [Date1], [Date2], [FK], [Char1], [DateTime1], [Bit], [HashedBK])   
    	SELECT [RK], [Date1], [Date2], [FK], [Char1], [DateTime1], 1, [HashedBK] FROM 
    	(
       		MERGE INTO dbo.TargetTable AS target 
       		USING (SELECT [RK], [Date1], [Date2], [FK], [Char1], [DateTime1], [HashedBK] FROM dbo.SourceTable) AS source
                     	 ([RK], [Date1], [Date2], [FK], [Char1], [DateTime1], [HashedBK])
       		ON (target.[HashedBK] = source.[HashedBK] and target.[Bit] = 1) 
       		WHEN MATCHED
       		THEN UPDATE SET [DateTime2] = dateadd(ss, -1, source.[DateTime1]), [Bit]=0 
       		WHEN NOT MATCHED THEN 
           		INSERT ([RK],[Date1],[Date2],[FK],[Char1],[DateTime1],[HashedBK],[Bit]) 
           		VALUES (source.[RK], source.[Date1], source.[Date2], source.[FK], source.[Char1], source.[DateTime1], source.[HashedBK], 1)   
       		OUTPUT $action, source.[RK], source.[Date1], source.[Date2], source.[FK], source.[Char1], source.[DateTime1], source.[HashedBK], 1
    	) AS changes 
    	(action, [RK], [Date1], [Date2], [FK], [Char1], [DateTime1], [HashedBK], [Bit]) WHERE action = 'UPDATE'
    	OPTION (FAST 1)

    Doing these two simple things removes the SPOOL operators and Hash joins from the plan and allows it to complete in sub-second times. 

    Addressing the issue. Given that this happens in SIMPLE mode and does not for FULL, it certainly sounds like an issue you would want to file on connect; though, this seems more like a cardinality issue with the MERGE statement than anything else.

    *Note that adding a non-clustered index to the target table on hashbk with every column included will allow the fast 1 to not be used. This is not ideal though.

    I truly hope that helps!!


    I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)


    • Edited by Daniel Janik Tuesday, January 3, 2017 5:43 PM added 2nd index I overlooked in original post
    Tuesday, January 3, 2017 5:35 PM
  • As David said, you forgot to add any code in your Connect item. You may also want to link to this thread there.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, January 3, 2017 9:20 PM
  • I believe I am experiencing the same bug as you in SQL Server 2016.  Our production environment is running SQL Server 2012 and we recently setup a new development server running SQL Server 2016 and 4 out of 8 of our MERGE statements would consistently time-out and result in one of the following two errors after 5 minutes:

    Msg 845 - Time-out occurred while waiting for buffer latch type 4 for page (1:448), database ID 5.

    Msg 701 - There is insufficient system memory in resource pool 'default' to run this query.

    However, 4 of our MERGE statements did not time-out and I found the only difference between the ones completing and the ones timing out was the existence of a clustered primary key index in the destination table.  After dropping the indexes from the destination tables the MERGE statement ran fine.  The index was created automatically because the problematic tables all contained INT IDENTITY(1,1) columns.  I see that your target table also contains an INT IDENTITY(1,1) columns.  I'd be interested to know if dropping the PK clustered index enables your MERGE statement to complete.  I'm even more interested if Microsoft plans on addressing this bug in SQL Server 2016 as we will eventually want to upgrade our production environment without having to drop these indexes.

    Tuesday, April 18, 2017 10:52 PM