8

I'm currently upgrading our Data warehouse from SQL 2012 to SQL 2016. I have both my old and new DW's running side by side in parallel.

My ETL process (A framework developed in SSIS by a 3rd party) has run successfully for more than 2 years on 2012 but is failing on 2016. So far the databases and ETL process are identical.

Both Servers are Virtual machines running on VMWare. Old Server is Win 2008 with 24Gb of RAM. SQL 2012 Std. Max mem set to 16Gb. New Server is Win 2012 with 64Gb of RAM. SQL 2016 dev. Max mem set to 50Gb. New DW is running v13.0.1601.5 RTM Developer Edition (64-bit).

While running my ETL process the load steps that use a SQL Merge into either a dimension or fact table fail with the following error.

Full text:

DESCRIPTION: SQL Server Assertion: File: , line=951 Failed Assertion = 'IS_OFF (BUF_MINLOGGED, m_buf->bstat) || pageModifyType != PageModifyType_Contents || GetPagePtr ()->IsTextPage ()'. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.

As recommended I have run DBCC and no errors were found. I have also restarted SQL. I then restarted the ETL process and got the same error.

My searches for this error show that is was known error in SQL 2008, 2012 & 2014 and fixed in subsequent hotfixes & cumulative updates. so I'm a bit surprised to see it reappear in 2016.

The links I've found say it affects SSIS when trying to do inserts if database is in Simple or Bulk Logged recovery model. (I'm running in Simple recovery model)

A suggested workaround is to change Db recovery model to FULL. I have tried this and it works, but it's not much of a solution for a Data Warehouse.

Has anybody else encountered this with 2016?

Can anyone suggest alternative workarounds?

Updates:

26/7/2016: I applied the Critical Update KB3164398 (v13.0.1708.0) and the problem still exists.

27/7/2016: I have applied Cumulative Update CU1 KB3164674 (v13.0.2149.0).

3/8/2016: Error occurred overnight on our smallest cube. CU1 did not fix the issue. Today I reported the bug on MS Connect And I have also logged a support call with Microsoft.

12/8/2016: MS-Support responded initially, but the responses was "We don't have a fix for that". The Support guy was going to discuss it with his colleagues and get back to me. 8 days later I haven't heard from him.

Although I don't have a 'fix' we did find a workaround that suited us. See my posted answer.

29/9/2016. I applied CU2 last week. On Thursay we accidentally ran an old version of the merge which failed again with the same error. So.. CU2 hasnt fixed it either.

23/1/2017: I Applied 2016 SP1 CU1 and I believe this has resolved the issue. Specifically KB3205964

2

Looking at the KB you have a couple of options / workarounds:

  1. Switch to FULL recovery model. You say "this isn't much of an option for a warehouse" but it's really just a matter of setting up the transaction log backups on a regular basis eg 15 minutes and then disposing of them. SSIS / Maintenance Plans have stock tasks for doing this. You will lose bulk-logged transactions, but I've never found these have made a great difference in runtimes, just log size. You can even backup log to nul which I won't describe here. If you're not sure what to do, ask you local DBA. Disk space and transaction log backup retention are easier problems to solve than fatal errors. When this problem eventually gets solved you can switch back.
  2. The KB mentions "multiple BULK INSERT statements in a single-distributed transaction". It's not clear from your question how your bulk inserts are set up. Are you using SSIS to run 'Execute SQL' tasks which use the MERGE command? What does 'multiple BULK INSERTs' mean here? Is there a way to convert your approach to single BULK INSERTs, one at a time for example? In SSIS you can set the 'MaxConcurrentExecutables' to 1 temporarily for example, see if that helps. Tie it in to a config variable so you can change it back later. Obviously it will slow things down but you prefer your ETL to finish rather than fail quickly. Doing things in parallel is a good pattern and a real strength of SSIS, but you can only go as fast as your slowest component; say you have 10 dimensions which take a minute and one fact which takes an hour, your ETL finishes in an hour running parallel or 1 hour 10 minutes running serially.
  3. MERGE is nice but does have a few issues. You could consider converting back to INSERT/UPDATE. Also you should be using HOLDLOCK with MERGE according to here. Do you use that hint? Does it make any difference to this issue if you do? We had an issue in an early SQL 2014 build where using MERGE with composable DML (OUTPUT clause) into columnstore caused this kind of assertion - I made them take the columnstore indexes off the dimensions, which they had added without telling me.
  4. What kind of transaction handling are you doing? Sometimes with ETL the position is repeatable by just re-running. Sometimes you need it to fail and rollback. How have you implemented this? Can be altered so it's not "single distributed transaction"?

Good luck.

  • Hi @wBob thanks for your reply. To answer your questions. 1. We're talking about extracting 20-30Gb tables in a single step. moving over 100Gb of data. I'm concerned about blowing out the TX logs and also the storage. Maybe I could make the log backup a step in the ETL after each extract?. 2. Yes SSIS calls a SQL task to perform a merge. Each step is executed sequentially. At the time the merge is running it's the only task executing. 3. Our ETL tool is a framework supplied by a vendor. This is how it works. Not sure about hints. will check. 4. No TX handling. Straight SQL. – Sir Swears-a-lot Jul 27 '16 at 21:19
  • So if the successful runs don't continue, try a 100GB t-log with log backups after each table move, that should sort it. Obviously you have to test. Any other features we should know about on the target? Columnstore, partitioning, excessive indexing? – wBob Jul 27 '16 at 22:03
  • Currently no features that aren't already on our existing DW on 2012 std. The new DW/Datamarts are exact copy of originals, No columnstores or partitioning. Minimal (but effective) indexes. We're currently focusing on a platform move like for like.Ultimately our new prod DW will be 2016 Enterprise. But we're not adding Enterprise level features until we have the basics working. – Sir Swears-a-lot Jul 27 '16 at 22:28
  • Hi @wBob.We've sort of implemented your suggestion by breaking the insert out of the merge. But given the way i've asked the question i'm not sure what qualifies as a correct answer. but I think you've lead me to a valid and workable workaround. – Sir Swears-a-lot Aug 4 '16 at 5:18
2

I believe this has been resolved in 2016 SP1 CU1.

Specifically by KB3205964

1

This is fixed by applying cumulative update 1 (CU1) for MSSQL2016 see link https://support.microsoft.com/en-us/kb/3164674

  • Steve was working with me on this. we thought we had it solved but no cigar. so I've removed credit and downvoted. – Sir Swears-a-lot Aug 4 '16 at 5:11
1

I believe we've found another workaround. I'm posting my answer as I think it may be useful, and it's different enough from wBob's suggestion.

We've changed the insert part of the merge statement so that it inserts to a temp table rather than the original target.

Once the merge statement executes, we then insert from the #table into the target.

It's not ideal, but at least the merge still handles the complexity of the 'upsert' by marking rows that have been retired/expired.

We found this to be an acceptable trade-off compared with completely re-writing the merge as separate inserts and updates.

CREATE TABLE #Activity(
[ETL_ActivitySurrogateKey] [int] IDENTITY(1,1) NOT NULL,
[Field1] [varchar](255) NULL,
)

-- Insert statements for procedure here
INSERT INTO #Activity ( [Field1],2,3,etc )

SELECT [Field1],2,3,etc
FROM 
(
    MERGE [DDS_OZ_CC].[dimActivity] AS target 
    USING (
      SELECT [Field1],2,3,etc
      FROM [STAGE_OZ_CC].[Transform_Activity]
      ) as source
    ON
    (
      target.RowIsCurrent = source.RowIsCurrent
         AND target.[Field1] = source.[Field1]
    )
    WHEN MATCHED 
        AND (        
        EXISTS (SELECT target.Level5Id EXCEPT SELECT source.Level5Id)
    )
    THEN
      UPDATE SET 
        ETL_ValidToDateTime = source.ETL_ValidFromDateTime 
       ,ETL_RowIsCurrent = 0 
       ,ETL_LastTouchedBy = source.ETL_LastTouchedBy 
       ,ETL_RowChangeReason = 'SCD2 Retired' 

    WHEN NOT MATCHED THEN 
    INSERT 
    (    
     [Field1],2,3,etc
    )
    VALUES (      
      source.[Field1],2,3,etc
    )
       WHEN NOT MATCHED BY SOURCE AND target.ETL_RowIsCurrent = 1
       THEN UPDATE SET
       ETL_RowIsCurrent = 0 
       ,ETL_RowChangeReason = 'Fact Removed' 
       ,ETL_LastTouchedBy = 'Unknown'

  OUTPUT
    $action      
      ,source.[Field1],2,3,etc    

  ) AS MergeOutput
  (
    action  
    ,[Field1],2,3,etc   
  ) 

  WHERE ACTION = 'UPDATE' AND ETL_RowIsCurrent = 1

    INSERT INTO [DDS_OZ_CC].[dimActivity]
    ( [Field1],2,3,etc  )
    SELECT [Field1],2,3,etc
    FROM #Activity

    END
  • Ok thanks Peter. Just out of interest, did you try the HOLDLOCK hint? – wBob Aug 4 '16 at 6:09
  • No i didnt. After reading Aaron Bertrands article about it my understanding was that was really about concurrency issues with multiple users. In our case the ETL process is the only thing executing. – Sir Swears-a-lot Aug 4 '16 at 6:17
  • I think it was worth trying just to see if you get a different behaviour, plus it's probably best practice, just to say you're not concurrent now doesn't mean you might not be in the future. Anyway, well done on finding a solution and on to the next problem : ) – wBob Aug 4 '16 at 7:48
  • I would have liked to, but sadly we're under time constraints. Once we found a workable solution the boss made a call to move on. – Sir Swears-a-lot Aug 4 '16 at 8:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.