Msg 601... "Could not continue scan with NOLOCK due to data movement".BUT NOLOCK ! - BUG

Question

Strange situation... New physical severs, new install of SQL Server 2019 Enterprise version : Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ). Testing the performance by creating the first database like this :

CREATE DATABASE DB_BENCH
GO

DECLARE @SQL NVARCHAR(max) = N'';
SELECT @SQL = @SQL + N'ALTER DATABASE DB_BENCH MODIFY FILE (NAME = ''' + name + N''', SIZE = 10 GB, FILEGROWTH = 64 MB);' 
FROM DB_BENCH.sys.database_files;
SET @SQL = @SQL + N'ALTER DATABASE DB_BENCH SET RECOVERY SIMPLE;'
EXEC (@SQL);
GO

And the objects in the database like this :

USE DB_BENCH
GO

SET NOCOUNT ON;
GO

CREATE TABLE T_TIME_INTERVAL_TIV
(TIV_ID       INT NOT NULL IDENTITY PRIMARY KEY,
 TIV_GROUP    INT,
 TIV_DEBUT    DATETIME2(0),
 TIV_FIN      DATETIME2(0))
GO 

TRUNCATE TABLE T_TIME_INTERVAL_TIV;
GO

BULK INSERT T_TIME_INTERVAL_TIV
FROM "C:\DATA_SQL\intervals.txt"
WITH (KEEPIDENTITY , 
      FIELDTERMINATOR = '\t',
      ROWTERMINATOR = '\n');
GO

CREATE VIEW V
AS
SELECT TIV_GROUP AS id, TIV_DEBUT AS intime, TIV_FIN AS outtime
FROM   T_TIME_INTERVAL_TIV
GO

The intervals.txt datafile contains 1 million lines. You can have it at : https://1drv.ms/t/s!AqvZfiQYoNpBiCD65D4zaRbch5s-?e=UicEYu

The query that produce the bug :

WITH T1 As
(SELECT id, intime 
 FROM   @T
 UNION  ALL
 SELECT id, outtime FROM @T),
T2 As
(SELECT ROW_NUMBER() OVER(PARTITION BY id ORDER BY intime) NN, id, intime
 FROM   T1 T1_1),
T3 As
(SELECT T2_1.NN - ROW_NUMBER() OVER(PARTITION BY T2_1.id ORDER BY T2_1.intime,T2_2.intime) NN1,
        T2_1.id, T2_1.intime intime, T2_2.intime outtime
 FROM   T2 T2_1
        INNER JOIN T2 T2_2
              ON T2_1.id=T2_2.id
                 And T2_1.NN=T2_2.NN-1
 WHERE  EXISTS (SELECT *
                FROM   V S
                WHERE  S.id=T2_1.id
                  AND  (S.intime < T2_2.intime AND S.outtime>T2_1.intime))
    OR  T2_1.intime = T2_2.intime)
SELECT id, MIN(intime) intime, MAX(outtime) outtime
FROM   T3
GROUP  BY id, NN1
ORDER BY id, intime, outtime;

We tested this query on 2 different servers... with the same SQL Server installation. The result is always : Msg 601, Level 12, State 1, Line ... Could not continue scan with NOLOCK due to data movement.

With an installation of : SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 Developer Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor)

There is no problem...

We tested the databases with DBCC CHECKDB () WITH DATA_PURITY. No errors.

Can you reproduce with your different editions/patches (CU) and give me your results for which are alright or not ?

If some of you have already the bug I will add an entry in SQL Azure feedback...

Thanks

Answer 1

After many investigation, we found that it is really a SQL Server bug.

The bug disappear when we execute a :

UPDATE STATISTICS T_TIME_INTERVAL_TIV WITH FULLSCAN;

Or when whe "hint" the query with OPTION (MAXDOP 1)

Sometime a stack dump appear (not Always) showing this type of messages :

A time-out occurred while waiting for buffer latch -- type 4, bp 0000029BA883BDC0, page 9:407, stat 0x10b, database id: 2, allocation unit Id: 422212465393664/140737488683008, task 0x0000029B86723848 : 14, waittime 300 seconds, flags 0x1a, owning task 0x0000029B86713848. Not continuing to wait.

Which is the tempdb. A Stack Dump is systematically recorded on file.

We will call the MS hotline as soon as possible.

Thanks to all of you.